select id,style,sum(money) as money
into #temp
from aa
group by id,style
select id,
(case when style='01' then sum(isnull(money,0)) end ) as s01,
(case when style='02' then sum(isnull(money,0)) end ) as s02,
(case when style='03' then sum(isnull(money,0)) end ) as s03,
(case when style='04' then sum(isnull(money,0)) end ) as s04,
(case when style='05' then sum(isnull(money,0)) end ) as s05,
(case when style='06' then sum(isnull(money,0)) end ) as s06,
(case when style='07' then sum(isnull(money,0)) end ) as s07,
(case when style='08' then sum(isnull(money,0)) end ) as s08
into #temp1
from #temp
group by id,style
select id,sum(isnull(s01,0)) as s01,sum(isnull(s02,0)) as s02,sum(isnull(s03,0)) as s03,sum(isnull(s04,0)) as s04,
sum(isnull(s05,0)) as s05,sum(isnull(s06,0)) as s06,sum(isnull(s07,0)) as s07,sum(isnull(s08,0)) as s08
into #temp2
from #temp1
group by id
select * from #temp2
在sql server 7.0中调试通过,记得给分哦!!
into #temp
from aa
group by id,style
select id,
(case when style='01' then sum(isnull(money,0)) end ) as s01,
(case when style='02' then sum(isnull(money,0)) end ) as s02,
(case when style='03' then sum(isnull(money,0)) end ) as s03,
(case when style='04' then sum(isnull(money,0)) end ) as s04,
(case when style='05' then sum(isnull(money,0)) end ) as s05,
(case when style='06' then sum(isnull(money,0)) end ) as s06,
(case when style='07' then sum(isnull(money,0)) end ) as s07,
(case when style='08' then sum(isnull(money,0)) end ) as s08
into #temp1
from #temp
group by id,style
select id,sum(isnull(s01,0)) as s01,sum(isnull(s02,0)) as s02,sum(isnull(s03,0)) as s03,sum(isnull(s04,0)) as s04,
sum(isnull(s05,0)) as s05,sum(isnull(s06,0)) as s06,sum(isnull(s07,0)) as s07,sum(isnull(s08,0)) as s08
into #temp2
from #temp1
group by id
select * from #temp2
在sql server 7.0中调试通过,记得给分哦!!
into #temp
from RZBF
group by id,style
select id,
(case when style='01' then sum(isnull(money,0)) end ) as s01,
(case when style='02' then sum(isnull(money,0)) end ) as s02,
(case when style='03' then sum(isnull(money,0)) end ) as s03,
(case when style='04' then sum(isnull(money,0)) end ) as s04,
(case when style='05' then sum(isnull(money,0)) end ) as s05,
(case when style='06' then sum(isnull(money,0)) end ) as s06,
(case when style='07' then sum(isnull(money,0)) end ) as s07,
(case when style='08' then sum(isnull(money,0)) end ) as s08
into #temp1
from #temp
group by id,style
select id,sum(isnull(s01,0)) as s01,sum(isnull(s02,0)) as s02,sum(isnull(s03,0)) as s03,sum(isnull(s04,0)) as s04,
sum(isnull(s05,0)) as s05,sum(isnull(s06,0)) as s06,sum(isnull(s07,0)) as s07,sum(isnull(s08,0)) as s08
into #temp2
from #temp1
group by id
select * from #temp2
#temp2 为最后的汇总表
在sql server 7.0中调试通过,记得给分哦!!
(select id,case when style = '01' then sum(Money) else 0 end ,
case when style = '02' then sum(Money) else 0 end ,
case when style = '03' then sum(Money) else 0 end
from test1
group by id,style) tb1
group by tb1.id
from RZBF,
(select id,sum(money) s1 from RZBF where style='01' group by id) a1,
(select id,sum(money) s2 from RZBF where style='02' group by id) a2,
(select id,sum(money) s3 from RZBF where style='03' group by id) a3,
(select id,sum(money) s3 from RZBF where style='04' group by id) a4,
(select id,sum(money) s3 from RZBF where style='05' group by id) a5,
(select id,sum(money) s3 from RZBF where style='06' group by id) a6,
(select id,sum(money) s3 from RZBF where style='07' group by id) a7,
(select id,sum(money) s3 from RZBF where style='08' group by id) a8
where RZBF.id=a1.id
and a1.id=a2.id
and a2.id=a3.id
and a3.id=a4.id
and a4.id=a5.id
and a5.id=a6.id
and a6.id=a7.id
and a7.id=a8.id
在sql server和oracle上跳是通过.