如何汇总我这个表！谢谢！

[努力偷懒]

有表A(kind只有2种)
EM_ID TC_M DO_M Kind
1 5 10 1
2 3 10 1
3 2 10 2
2 12 20 2
3 4 30 1
1 7 15 2

结果表：
EM_ID TC_SUM_M1 DO_SUM_M1 TC_SUM_M2 DO_SUM_M2
1 5 10 7 15
2 3 10 12 20
3 2 10 4 30
这样一个结果
TC_SUM_M1就是kind=1的,TC_SUM_M2就是Kind=2的大家明白我的意思没？
先谢谢哦！

[phantomMan]

create table a
(
EM_ID int,
TC_M int,
DO_M int,
Kind int
)

insert into a values(1,5,10,1)
insert into a values(2,3,10,1)
insert into a values(3,2,10,2)
insert into a values(2,12,20,2)
insert into a values(3,4,30,1)
insert into a values(1,7,15,2)

select EM_ID,
(select t2.TC_M from A t2 where kind=1 and t1.EM_ID=t2.EM_ID) as TC_SUM_M1,
(select DO_M from A t2 where kind=2 and t1.EM_ID=t2.EM_ID) as DO_SUM_M2,
(select TC_M from A t2 where kind=1 and t1.EM_ID=t2.EM_ID) as TC_SUM_M1,
(select DO_M from A t2 where kind=2 and t1.EM_ID=t2.EM_ID) as DO_SUM_M2
from A t1
group by EM_ID

• 打赏
• 举报

回复 赞

[zlp321002]

--难道楼主数据有问题,还是我的算法有问题吗??
--测试环境
declare @表A table(EM_ID int,TC_M int,DO_M int,Kind int)
insert into @表A select 1,5,10,1
union all select 2,3,10,1
union all select 3,2,10,2
union all select 2,12,20,2
union all select 3,4,30,1
union all select 1,7,15,2
--查询
select EM_ID=EM_ID,
TC_SUM_M1=sum(case when Kind=1 then TC_M else 0 end),
DO_SUM_M1=sum(case when Kind=1 then DO_M else 0 end),
TC_SUM_M2=sum(case when Kind=2 then TC_M else 0 end),
DO_SUM_M2=sum(case when Kind=2 then DO_M else 0 end)
from @表A
group by EM_ID
--结果
EM_ID TC_SUM_M1 DO_SUM_M1 TC_SUM_M2 DO_SUM_M2
----------- ----------- ----------- ----------- -----------
1 5 10 7 15
2 3 10 12 20
3 4 30 2 10

（所影响的行数为 3 行）

• 打赏
• 举报

回复 赞

[phantomMan]

是楼主的结果由问题

• 打赏
• 举报

回复 赞

[col_wolf0724]

本人最近创了几个群，如有爱好者请进来聊聊
VC 15228918
软件设计师 1582507
网络高手 15471413
数据库 155322792

• 打赏
• 举报

回复 赞

[MorningTea]

--这个问题，我都是用动态sql，应付多个kind的问题！
create table #A (EM_ID int,TC_M int,DO_M int,Kind int)
insert into #A select 1,5,10,1
union all select 2,3,10,1
union all select 3,2,10,2
union all select 2,12,20,2
union all select 3,4,30,1
union all select 1,7,15,2

declare @str as varchar(6000) --用来存取动态sql
set @str=''
select @str = @str +',TC_SUM_M' + convert(varchar(2), A1.Kind) + '=(select TC_M from #A A2 where A2.EM_ID = A1.EM_ID
and A2.kind = ''' + convert(varchar(2), A1.Kind) + ''')' + ',DO_SUM_M' + convert(varchar(2), A1.Kind) + '=(select DO_M from #A A2 where A2.EM_ID = A1.EM_ID
and A2.kind = ''' + convert(varchar(2), A1.Kind) + ''')'
from #A A1
group by A1.kind

set @str = stuff(@str,1,1,'')
set @str = 'select EM_ID,' + @str + ' from #A A1 group by A1.EM_ID'
exec(@str)

/*
EM_ID TC_SUM_M1 DO_SUM_M1 TC_SUM_M2 DO_SUM_M2
----------- ----------- ----------- ----------- -----------
1 5 10 7 15
2 3 10 12 20
3 4 30 2 10
*/

• 打赏
• 举报

回复 赞

[MorningTea]

--用楼上几位高手的语句来拼接会语句更加间接一点哦
--修改
declare @str as varchar(6000) --用来存取动态sql
set @str=''
select @str = @str +',TC_SUM_M' + convert(varchar(2), A1.Kind) +
'=sum(case when kind =' + convert(varchar(2), A1.Kind) +
' then TC_M else 0 end),DO_SUM_M'+ convert(varchar(2), A1.Kind) + '=sum(case when kind =' +
convert(varchar(2), A1.Kind) +
' then DO_M else 0 end)'
from #A A1
group by A1.kind

set @str = stuff(@str,1,1,'')
set @str = 'select EM_ID,' + @str + ' from #A A1 group by A1.EM_ID'
--print @str
exec(@str)

/*
EM_ID TC_SUM_M1 DO_SUM_M1 TC_SUM_M2 DO_SUM_M2
----------- ----------- ----------- ----------- -----------
1 5 10 7 15
2 3 10 12 20
3 4 30 2 10

(3 row(s) affected)
*/

• 打赏
• 举报

回复 赞