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

努力偷懒 2005-09-05 06:01:09
有表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的大家明白我的意思没?
先谢谢哦!
...全文
230 6 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
MorningTea 2005-09-05
  • 打赏
  • 举报
回复
--用楼上几位高手的语句来拼接会语句更加间接一点哦
--修改
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)
*/
MorningTea 2005-09-05
  • 打赏
  • 举报
回复
--这个问题,我都是用动态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
*/
col_wolf0724 2005-09-05
  • 打赏
  • 举报
回复
本人最近创了几个群,如有爱好者请进来聊聊
VC 15228918
软件设计师 1582507
网络高手 15471413
数据库 155322792
phantomMan 2005-09-05
  • 打赏
  • 举报
回复
是楼主的结果由问题
zlp321002 2005-09-05
  • 打赏
  • 举报
回复
--难道楼主数据有问题,还是我的算法有问题吗??
--测试环境
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 2005-09-05
  • 打赏
  • 举报
回复
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

34,838

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧