数据表或试图:
Idate saler money
2008-01 张三 100
2008-02 张三 50
。
。
2008-12 张三 340
2009-01 张三 240
2009-02 张三 300
,
,
2009-12 张三 200
----------------------------
需要结果为:
saler 2008 2009 2008同月累计 2009同月累计 2008同月金额 2009同月金额
张三 08年总额 09年总额 2008年1月金额 2009年1月金额 2008年1月金额 2009年1月金额
张三 08年总额 09年总额 08年1月+08年2月 09年1月+09年2月 08年2月金额 09年2月金额
张三 08年总额 09年总额 08年1月+2月+3月 09年1月+2月+3月 08年3月金额 09年3月金额
,,
。。
张三 08年总额 09年总额 08年1月+2月+。+12月 09年1月+2月+。+12月 08年12月金额 09年12月金额
这个结果可以用sql直接汇总出来吗
Idate saler money
2008-01 张三 100
2008-02 张三 50
。
。
2008-12 张三 340
2009-01 张三 240
2009-02 张三 300
,
,
2009-12 张三 200
----------------------------
需要结果为:
saler 2008 2009 2008同月累计 2009同月累计 2008同月金额 2009同月金额
张三 08年总额 09年总额 2008年1月金额 2009年1月金额 2008年1月金额 2009年1月金额
张三 08年总额 09年总额 08年1月+08年2月 09年1月+09年2月 08年2月金额 09年2月金额
张三 08年总额 09年总额 08年1月+2月+3月 09年1月+2月+3月 08年3月金额 09年3月金额
,,
。。
张三 08年总额 09年总额 08年1月+2月+。+12月 09年1月+2月+。+12月 08年12月金额 09年12月金额
这个结果可以用sql直接汇总出来吗
sum(money) as 2008,
saler,
substring(idate,6,2) as idate
money
into #2008
from table1
where idate between '2008-01' and '2008-12'select
sum(money) as 2009,
saler,
substring(idate,6,2) as idate
money
into #2009
from table1
where idate between '2009-01' and '2009-12'
declare @i varchar(30)
declare @j varchar(30)
declare @sum2008 varchar(30)
declare @sum2009 varchar(30)
set @i=1
set @j=12
set @sum2008=0
set @sum2009=0
while @i<@j
beginset @sum2008=@sum2008+select money from #2008 where idate=@i
set @sum2009=@sum2009+select money from #2009 where idate=@i
select
b.saler,
b.2008,
c.2009,
@sum2008 as 2008同月累计,
@sum2009 as 2009同月累计,
b.money as 2008同月金额,
c.money as 2009同月金额
from #2008 b,#2009 c
where b.saler=c.saler
and b.Idate=@i
and c.Idate=@i
set @i=@i+1
end
sum(money) as 2008,
saler,
substring(idate,6,2) as idate
money
into #2008
from table1
where idate between '2008-01' and '2008-12' select
sum(money) as 2009,
saler,
substring(idate,6,2) as idate
money
into #2009
from table1
where idate between '2009-01' and '2009-12'
两处的逗号丢了,呵呵,
drop table #testcreate table #test (
Idate varchar( 06 ),
saler varchar( 20 ),
money int )
insert into #test
select '200801' , '张三', 100 union
select '200802' , '张三', 100 union
select '200803' , '张三', 100 union
select '200804' , '张三', 100 union
select '200805' , '张三', 100 union
select '200806' , '张三', 100 union
select '200807' , '张三', 100 union
select '200808' , '张三', 100 union
select '200809' , '张三', 100 union
select '200810' , '张三', 100 union
select '200811' , '张三', 100 union
select '200812' , '张三', 100 union
select '200901' , '张三', 100 union
select '200902' , '张三', 100 union
select '200903' , '张三', 100 union
select '200904' , '张三', 100 union
select '200905' , '张三', 100 union
select '200906' , '张三', 100 union
select '200907' , '张三', 100 union
select '200908' , '张三', 100 union
select '200909' , '张三', 100 union
select '200910' , '张三', 100 union
select '200911' , '张三', 100 union
select '200912' , '张三', 100 --select * from #testSELECT BB.*,
AA.*
FROM (
select saler as saler ,
sum( case when substring( idate , 1 ,4 ) = '2008'
then money
else 0 end ) '2008',
sum( case when substring( idate , 1 ,4 ) = '2009'
then money
else 0 end ) '2009'
from #test
group by saler ) BB , (
select sum( case when substring( idate , 1 , 4 ) = '2008' then money
else 0 end ) '2008同月累计' ,
sum( case when substring( idate , 1 , 4 ) = '2009' then money
else 0 end ) '2009同月累计' ,
sum( case when idate = '200801' then money
else 0 end ) '2008同月金额' ,
sum( case when idate = '200901' then money
else 0 end ) '2009同月金额'
from #test
where substring( idate , 5 , 2 ) <= '01'
union
select sum( case when substring( idate , 1 , 4 ) = '2008' then money
else 0 end ) '2008同月累计' ,
sum( case when substring( idate , 1 , 4 ) = '2009' then money
else 0 end ) '2009同月累计' ,
sum( case when idate = '200802' then money
else 0 end ) '2008同月金额' ,
sum( case when idate = '200902' then money
else 0 end ) '2009同月金额'
from #test
where substring( idate , 5 , 2 ) <= '02' union
select sum( case when substring( idate , 1 , 4 ) = '2008' then money
else 0 end ) '2008同月累计' ,
sum( case when substring( idate , 1 , 4 ) = '2009' then money
else 0 end ) '2009同月累计' ,
sum( case when idate = '200803' then money
else 0 end ) '2008同月金额' ,
sum( case when idate = '200903' then money
else 0 end ) '2009同月金额'
from #test
where substring( idate , 5 , 2 ) <= '03' union
select sum( case when substring( idate , 1 , 4 ) = '2008' then money
else 0 end ) '2008同月累计' ,
sum( case when substring( idate , 1 , 4 ) = '2009' then money
else 0 end ) '2009同月累计' ,
sum( case when idate = '200804' then money
else 0 end ) '2008同月金额' ,
sum( case when idate = '200904' then money
else 0 end ) '2009同月金额'
from #test
where substring( idate , 5 , 2 ) <= '04' union
select sum( case when substring( idate , 1 , 4 ) = '2008' then money
else 0 end ) '2008同月累计' ,
sum( case when substring( idate , 1 , 4 ) = '2009' then money
else 0 end ) '2009同月累计' ,
sum( case when idate = '200805' then money
else 0 end ) '2008同月金额' ,
sum( case when idate = '200905' then money
else 0 end ) '2009同月金额'
from #test
where substring( idate , 5 , 2 ) <= '05'
union
select sum( case when substring( idate , 1 , 4 ) = '2008' then money
else 0 end ) '2008同月累计' ,
sum( case when substring( idate , 1 , 4 ) = '2009' then money
else 0 end ) '2009同月累计' ,
sum( case when idate = '200806' then money
else 0 end ) '2008同月金额' ,
sum( case when idate = '200906' then money
else 0 end ) '2009同月金额'
from #test
where substring( idate , 5 , 2 ) <= '06'
union
select sum( case when substring( idate , 1 , 4 ) = '2008' then money
else 0 end ) '2008同月累计' ,
sum( case when substring( idate , 1 , 4 ) = '2009' then money
else 0 end ) '2009同月累计' ,
sum( case when idate = '200807' then money
else 0 end ) '2008同月金额' ,
sum( case when idate = '200907' then money
else 0 end ) '2009同月金额'
from #test
where substring( idate , 5 , 2 ) <= '07'
union
select sum( case when substring( idate , 1 , 4 ) = '2008' then money
else 0 end ) '2008同月累计' ,
sum( case when substring( idate , 1 , 4 ) = '2009' then money
else 0 end ) '2009同月累计' ,
sum( case when idate = '200808' then money
else 0 end ) '2008同月金额' ,
sum( case when idate = '200908' then money
else 0 end ) '2009同月金额'
from #test
where substring( idate , 5 , 2 ) <= '08'
union
select sum( case when substring( idate , 1 , 4 ) = '2008' then money
else 0 end ) '2008同月累计' ,
sum( case when substring( idate , 1 , 4 ) = '2009' then money
else 0 end ) '2009同月累计' ,
sum( case when idate = '200809' then money
else 0 end ) '2008同月金额' ,
sum( case when idate = '200909' then money
else 0 end ) '2009同月金额'
from #test
where substring( idate , 5 , 2 ) <= '09'
union
select sum( case when substring( idate , 1 , 4 ) = '2008' then money
else 0 end ) '2008同月累计' ,
sum( case when substring( idate , 1 , 4 ) = '2009' then money
else 0 end ) '2009同月累计' ,
sum( case when idate = '200810' then money
else 0 end ) '2008同月金额' ,
sum( case when idate = '200910' then money
else 0 end ) '2009同月金额'
from #test
where substring( idate , 5 , 2 ) <= '10'
union
select sum( case when substring( idate , 1 , 4 ) = '2008' then money
else 0 end ) '2008同月累计' ,
sum( case when substring( idate , 1 , 4 ) = '2009' then money
else 0 end ) '2009同月累计' ,
sum( case when idate = '200811' then money
else 0 end ) '2008同月金额' ,
sum( case when idate = '200911' then money
else 0 end ) '2009同月金额'
from #test
where substring( idate , 5 , 2 ) <= '11'
union
select sum( case when substring( idate , 1 , 4 ) = '2008' then money
else 0 end ) '2008同月累计' ,
sum( case when substring( idate , 1 , 4 ) = '2009' then money
else 0 end ) '2009同月累计' ,
sum( case when idate = '200812' then money
else 0 end ) '2008同月金额' ,
sum( case when idate = '200912' then money
else 0 end ) '2009同月金额'
from #test
where substring( idate , 5 , 2 ) <= '12' ) AA
Idate saler money
2008-01 张三 100
2008-02 张三 50
。
。
2008-12 张三 340
2009-01 张三 240
2009-02 张三 300
,
,
2009-12 张三 200
----------------------------
需要结果为:
saler 2008 2009 2008同月累计 2009同月累计 2008同月金额 2009同月金额
张三 08年总额 09年总额 2008年1月金额 2009年1月金额 2008年1月金额 2009年1月金额
张三 08年总额 09年总额 08年1月+08年2月 09年1月+09年2月 08年2月金额 09年2月金额
张三 08年总额 09年总额 08年1月+2月+3月 09年1月+2月+3月 08年3月金额 09年3月金额
,,
。。
张三 08年总额 09年总额 08年1月+2月+。+12月 09年1月+2月+。+12月 08年12月金额 09年12月金额
这个结果可以用sql直接汇总出来吗
--- test
drop table #testcreate table #test (
Idate varchar( 06 ),
saler varchar( 20 ),
money int )
insert into #test
select '200801' , '张三', 100 union
select '200802' , '张三', 100 union
select '200803' , '张三', 100 union
select '200804' , '张三', 100 union
select '200805' , '张三', 100 union
select '200806' , '张三', 100 union
select '200807' , '张三', 100 union
select '200808' , '张三', 100 union
select '200809' , '张三', 100 union
select '200810' , '张三', 100 union
select '200811' , '张三', 100 union
select '200812' , '张三', 100 union
select '200901' , '张三', 100 union
select '200902' , '张三', 100 union
select '200903' , '张三', 100 union
select '200904' , '张三', 100 union
select '200905' , '张三', 100 union
select '200906' , '张三', 100 union
select '200907' , '张三', 100 union
select '200908' , '张三', 100 union
select '200909' , '张三', 100 union
select '200910' , '张三', 100 union
select '200911' , '张三', 100 union
select '200912' , '张三', 100 --select * from #testSELECT BB.*,
AA.*
FROM (
select saler as saler ,
sum( case when substring( idate , 1 ,4 ) = '2008'
then money
else 0 end ) '2008',
sum( case when substring( idate , 1 ,4 ) = '2009'
then money
else 0 end ) '2009'
from #test
group by saler ) BB , (
select sum( case when substring( idate , 1 , 4 ) = '2008' then money
else 0 end ) '2008同月累计' ,
sum( case when substring( idate , 1 , 4 ) = '2009' then money
else 0 end ) '2009同月累计' ,
sum( case when idate = '200801' then money
else 0 end ) '2008同月金额' ,
sum( case when idate = '200901' then money
else 0 end ) '2009同月金额'
from #test
where substring( idate , 5 , 2 ) <= '01'
union
select sum( case when substring( idate , 1 , 4 ) = '2008' then money
else 0 end ) '2008同月累计' ,
sum( case when substring( idate , 1 , 4 ) = '2009' then money
else 0 end ) '2009同月累计' ,
sum( case when idate = '200802' then money
else 0 end ) '2008同月金额' ,
sum( case when idate = '200902' then money
else 0 end ) '2009同月金额'
from #test
where substring( idate , 5 , 2 ) <= '02' union
select sum( case when substring( idate , 1 , 4 ) = '2008' then money
else 0 end ) '2008同月累计' ,
sum( case when substring( idate , 1 , 4 ) = '2009' then money
else 0 end ) '2009同月累计' ,
sum( case when idate = '200803' then money
else 0 end ) '2008同月金额' ,
sum( case when idate = '200903' then money
else 0 end ) '2009同月金额'
from #test
where substring( idate , 5 , 2 ) <= '03' union
select sum( case when substring( idate , 1 , 4 ) = '2008' then money
else 0 end ) '2008同月累计' ,
sum( case when substring( idate , 1 , 4 ) = '2009' then money
else 0 end ) '2009同月累计' ,
sum( case when idate = '200804' then money
else 0 end ) '2008同月金额' ,
sum( case when idate = '200904' then money
else 0 end ) '2009同月金额'
from #test
where substring( idate , 5 , 2 ) <= '04' union
select sum( case when substring( idate , 1 , 4 ) = '2008' then money
else 0 end ) '2008同月累计' ,
sum( case when substring( idate , 1 , 4 ) = '2009' then money
else 0 end ) '2009同月累计' ,
sum( case when idate = '200805' then money
else 0 end ) '2008同月金额' ,
sum( case when idate = '200905' then money
else 0 end ) '2009同月金额'
from #test
where substring( idate , 5 , 2 ) <= '05'
union
select sum( case when substring( idate , 1 , 4 ) = '2008' then money
else 0 end ) '2008同月累计' ,
sum( case when substring( idate , 1 , 4 ) = '2009' then money
else 0 end ) '2009同月累计' ,
sum( case when idate = '200806' then money
else 0 end ) '2008同月金额' ,
sum( case when idate = '200906' then money
else 0 end ) '2009同月金额'
from #test
where substring( idate , 5 , 2 ) <= '06'
union
select sum( case when substring( idate , 1 , 4 ) = '2008' then money
else 0 end ) '2008同月累计' ,
sum( case when substring( idate , 1 , 4 ) = '2009' then money
else 0 end ) '2009同月累计' ,
sum( case when idate = '200807' then money
else 0 end ) '2008同月金额' ,
sum( case when idate = '200907' then money
else 0 end ) '2009同月金额'
from #test
where substring( idate , 5 , 2 ) <= '07'
union
select sum( case when substring( idate , 1 , 4 ) = '2008' then money
else 0 end ) '2008同月累计' ,
sum( case when substring( idate , 1 , 4 ) = '2009' then money
else 0 end ) '2009同月累计' ,
sum( case when idate = '200808' then money
else 0 end ) '2008同月金额' ,
sum( case when idate = '200908' then money
else 0 end ) '2009同月金额'
from #test
where substring( idate , 5 , 2 ) <= '08'
union
select sum( case when substring( idate , 1 , 4 ) = '2008' then money
else 0 end ) '2008同月累计' ,
sum( case when substring( idate , 1 , 4 ) = '2009' then money
else 0 end ) '2009同月累计' ,
sum( case when idate = '200809' then money
else 0 end ) '2008同月金额' ,
sum( case when idate = '200909' then money
else 0 end ) '2009同月金额'
from #test
where substring( idate , 5 , 2 ) <= '09'
union
select sum( case when substring( idate , 1 , 4 ) = '2008' then money
else 0 end ) '2008同月累计' ,
sum( case when substring( idate , 1 , 4 ) = '2009' then money
else 0 end ) '2009同月累计' ,
sum( case when idate = '200810' then money
else 0 end ) '2008同月金额' ,
sum( case when idate = '200910' then money
else 0 end ) '2009同月金额'
from #test
where substring( idate , 5 , 2 ) <= '10'
union
select sum( case when substring( idate , 1 , 4 ) = '2008' then money
else 0 end ) '2008同月累计' ,
sum( case when substring( idate , 1 , 4 ) = '2009' then money
else 0 end ) '2009同月累计' ,
sum( case when idate = '200811' then money
else 0 end ) '2008同月金额' ,
sum( case when idate = '200911' then money
else 0 end ) '2009同月金额'
from #test
where substring( idate , 5 , 2 ) <= '11'
union
select sum( case when substring( idate , 1 , 4 ) = '2008' then money
else 0 end ) '2008同月累计' ,
sum( case when substring( idate , 1 , 4 ) = '2009' then money
else 0 end ) '2009同月累计' ,
sum( case when idate = '200812' then money
else 0 end ) '2008同月金额' ,
sum( case when idate = '200912' then money
else 0 end ) '2009同月金额'
from #test
where substring( idate , 5 , 2 ) <= '12' ) AA
/*
问题:如果上述两表互相换一下:即表结构和数据为:
姓名 语文 数学 物理
张三 74 83 93
李四 74 84 94
想变成(得到如下结果):
姓名 课程 分数
---- ---- ----
李四 语文 74
李四 数学 84
李四 物理 94
张三 语文 74
张三 数学 83
张三 物理 93
--------------
*/create table tb(姓名 varchar(10) , 语文 int , 数学 int , 物理 int)
insert into tb values('张三',74,83,93)
insert into tb values('李四',74,84,94)
go--SQL SERVER 2000 静态SQL。
select * from
(
select 姓名 , 课程 = '语文' , 分数 = 语文 from tb
union all
select 姓名 , 课程 = '数学' , 分数 = 数学 from tb
union all
select 姓名 , 课程 = '物理' , 分数 = 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 end--SQL SERVER 2000 动态SQL。
--调用系统表动态生态。
declare @sql varchar(8000)
select @sql = isnull(@sql + ' union all ' , '' ) + ' select 姓名 , [课程] = ' + quotename(Name , '''') + ' , [分数] = ' + quotename(Name) + ' from tb'
from syscolumns
where name! = N'姓名' and ID = object_id('tb') --表名tb,不包含列名为姓名的其它列
order by colid asc
exec(@sql + ' order by 姓名 ')--SQL SERVER 2005 动态SQL。
select 姓名 , 课程 , 分数 from tb unpivot (分数 for 课程 in([语文] , [数学] , [物理])) t--SQL SERVER 2005 动态SQL,同SQL SERVER 2000 动态SQL。--------------------