求sql语句（sqlserver 2000）
原表数据：
bh xmgzbh gzmc ggbh gg dj cl
123 丽丽 a 主机 1 9.00-20 0.36 22
123 丽丽 a 主机 1 9.00-20 0.36 80
456 峰 a 主机 1 9.00-20 0.36 85
456 峰 a 主机 2 10.00-20 2.3 50
456 峰 a 主机 2 10.00-20 2.3 40
456 峰 b 付机 1 9.00-20 0.28 33
456 峰 b 付机 2 10.00-20 2.5 80
要查出的结果：
123 丽丽 a 主机 1 9.00-20 0.36 102
456 峰 a 主机 1 9.00-20 0.36 852 10.00-20 2.390
456 峰 b 付机 1 9.00-20 0.28 332 10.00-20 2.580
[解决办法]
create table #(bh int,xm varchar(10),gzbh varchar(5),gzmc varchar(10),ggbh int,gg varchar(15),dj varchar(10),cl int)
insert # select
123, '丽丽 ', 'a ', '主机 ',1, '9.00-20 ', '0.36 ',22 union all select
123, '丽丽 ', 'a ', '主机 ',1, '9.00-20 ', '0.36 ',80 union all select
456, '峰 ', 'a ', '主机 ',1, '9.00-20 ', '0.36 ',85 union all select
456 , '峰 ', 'a ', '主机 ',2, '10.00-20 ', '2.3 ',50 union all select
456 , '峰 ', 'a ', '主机 ',2, '10.00-20 ', '2.3 ',40 union all select
456 , '峰 ', 'b ', '付机 ',1, '9.00-20 ', '0.28 ',33 union all select
456 , '峰 ', 'b ', '付机 ',2, '10.00-20 ', '2.5 ',80
select a.*,isnull(b.ggbh, ' '),isnull(b.gg, ' '),isnull(b.dj, ' '),isnull(b.[sum], ' ') from
(select bh,xm,gzbh,gzmc,ggbh,gg,dj,sum(cl) as [sum] from # where ggbh=1 group by bh,xm,gzbh,gzmc,ggbh,gg,dj) a left join
(select bh,xm,gzbh,gzmc,ggbh,gg,dj,sum(cl) as [sum] from # where ggbh=2 group by bh,xm,gzbh,gzmc,ggbh,gg,dj) b on a.bh=b.bh and a.gzbh=b.gzbh
drop table #
[解决办法]
create table #(bh int,xm varchar(10),gzbh varchar(5),gzmc varchar(10),ggbh int,gg varchar(15),dj varchar(10),cl int)
insert # select
123, '丽丽 ', 'a ', '主机 ',1, '9.00-20 ', '0.36 ',22 union all select
123, '丽丽 ', 'a ', '主机 ',1, '9.00-20 ', '0.36 ',80 union all select
456, '峰 ', 'a ', '主机 ',1, '9.00-20 ', '0.36 ',85 union all select
456 , '峰 ', 'a ', '主机 ',2, '10.00-20 ', '2.3 ',50 union all select
456 , '峰 ', 'a ', '主机 ',2, '10.00-20 ', '2.3 ',40 union all select
456 , '峰 ', 'b ', '付机 ',1, '9.00-20 ', '0.28 ',33 union all select
456 , '峰 ', 'b ', '付机 ',2, '10.00-20 ', '2.5 ',80
select * from #
select bh,xm,gzbh,gzmc,ggbh,gg,dj,sum(cl) as cl into #temp from # where ggbh=1 group by bh,xm,gzbh,gzmc,ggbh,gg,dj
union all
select bh,xm,gzbh,gzmc,ggbh,gg,dj,sum(cl) from # where ggbh=2 group by bh,xm,gzbh,gzmc,ggbh,gg,dj
select a.*,isnull((select ggbh from #temp where bh=a.bh and xm=a.xm and gzbh=a.gzbh and gzmc=a.gzmc and ggbh=2), ' ') as ggbh,
isnull((select dj from #temp where bh=a.bh and xm=a.xm and gzbh=a.gzbh and gzmc=a.gzmc and ggbh=2), ' ') as dj,
isnull((select cl from #temp where bh=a.bh and xm=a.xm and gzbh=a.gzbh and gzmc=a.gzmc and ggbh=2), ' ') as cl
from #temp a where ggbh=1
drop table #,#temp
[解决办法]
使用动态汇总:
if object_id( 'tbTest ') is not null
drop table tbTest
GO
create table tbTest(bh int, xm varchar(10),gzbh varchar(10),gzmc varchar(10), ggbh int, gg varchar(10),dj decimal(5,2),cl int)
insert tbTest
select 123, '丽丽 ', 'a ', '主机 ', 1, '9.00-20 ', 0.36, 22 union all
select 123, '丽丽 ', 'a ', '主机 ', 1, '9.00-20 ', 0.36, 80 union all
select 456, '峰 ', 'a ', '主机 ', 1, '9.00-20 ', 0.36, 85 union all
select 456, '峰 ', 'a ', '主机 ', 2, '10.00-20 ', 2.3, 50 union all
select 456, '峰 ', 'a ', '主机 ', 2, '10.00-20 ', 2.3, 40 union all
select 456, '峰 ', 'b ', '付机 ', 1, '9.00-20 ', 0.28, 33 union all
select 456, '峰 ', 'b ', '付机 ', 2, '10.00-20 ', 2.5, 80
[解决办法]
hellowork(一两清风) 强!
