请教高手这样的Sql 能写出吗 谢谢?
a b
101-1
101-1-1 1
101-1-2 2
102-1
102-1-1 3
102-1-2 4
102-1-3 5
.......
要结果如下
a b
101-1 3
101-1-1 1
101-1-2 2
102-1 12
102-1-1 3
102-1-2 4
102-1-3 5
就是把 101-1和102-1 的子的汇总
这样子能写吗?谢谢
[解决办法]
declare @t table(a varchar(20),b int)
insert into @t select rtrim( '101-1 '),null
insert into @t select rtrim( '101-1-1 '),1
insert into @t select rtrim( '101-1-2 '),2
insert into @t select rtrim( '102-1 '),null
insert into @t select rtrim( '102-1-1 '),3
insert into @t select rtrim( '102-1-2 '),4
insert into @t select rtrim( '102-1-3 '),5
select
a.a,
isnull(a.b,(select sum(b) from @t where a like a.a+ '% ')) as b
from
@t a
/*
a b
-------------------- -----------
101-1 3
101-1-1 1
101-1-2 2
102-1 12
102-1-1 3
102-1-2 4
102-1-3 5
*/
[解决办法]
declare @tab table
(
a varchar(10),
b int
)
insert into @tab select '101-1 ', null
insert into @tab select '101-1-1 ', 1
insert into @tab select '101-1-2 ', 2
insert into @tab select '102-1 ', null
insert into @tab select '102-1-1 ', 3
insert into @tab select '102-1-2 ', 4
insert into @tab select '102-1-3 ', 5
select a,case when b is null then (select sum(b) from @tab where charindex(tab.a,a) > 0 and a <> tab.a) else b end as b
from @tab tab
--结果
101-13
101-1-11
101-1-22
102-112
102-1-13
102-1-24
102-1-35
[解决办法]
drop table tab
create table tab
(
a varchar(10),
b int
)
insert into tab select '101-1 ', null
insert into tab select '101-1-1 ', 1
insert into tab select '101-1-2 ', 2
insert into tab select '102-1 ', null
insert into tab select '102-1-1 ', 3
insert into tab select '102-1-2 ', 4
insert into tab select '102-1-3 ', 5
update tab set b= (select sum(tb.b ) from tab as tb
where left(tab.a,5)=left(tb.a,5))
where tab.b is null
select * from tab
[解决办法]
if object_id( 'pubs..tb ') is not null
drop table tb
go
create table tb
(
a varchar(20),
b int
)
insert into tb(a,b) values( '101-1 ',null)
insert into tb(a,b) values( '101-1-1 ', 1)
insert into tb(a,b) values( '101-1-2 ', 2)
insert into tb(a,b) values( '102-1 ',null)
insert into tb(a,b) values( '102-1-1 ', 3)
insert into tb(a,b) values( '102-1-2 ', 4)
insert into tb(a,b) values( '102-1-3 ', 5)
select * from
(
select left(a,5) as a , sum(b) as b from tb group by left(a,5)
union all
select * from tb where b is not null
) t
order by a
drop table tb
/* result
a b
-------------------- -----------
101-1 3
101-1-1 1
101-1-2 2
102-1 12
102-1-1 3
102-1-2 4
102-1-3 5
（所影响的行数为 7 行）
*/
