已知：表:papernum
nid factno deptno padate mou
1 1 2 2007-07 56
2 1 3 2007-07 26
3 1 2 2008-01 10
4 2 1 2007-06 25
5 3 1 2007-06 12
6 2 2 2008-06 25
7 3 3 2008-05 26
8 1 3 2007-06 30
...
表：fact
fno factname
1 cs1
2 cs2
3 cs3
...
表： dept
did deptname
1 AS1
2 AS2
3 AS3
...
现在在页面上要显示：
fa/pa 一月 二月 三月 四月 五月 六月 七月 八月 九月 十月 十一月 十二月 tota
cs1/2008 10 10
cs2/2008 25 25
cs3/2008 26 26
cs1/2007 30 82 112
cs2/2007 25 25
cs3/2007 12 12
total2 10 26 92 82 210
可用多个sql语句。即只要在页面上显示如上数据
现在在页面上要显示：
fa/pa 一月 二月 三月 四月 五月 六月 七月 八月 九月 十月 十一月 十二月 tota
cs1/2008 10 10
cs2/2008 25 25
cs3/2008 26 26
cs1/2007 30 82 112
cs2/2007 25 25
cs3/2007 12 12
total 10 26 92 82 210
行列互转
if not object_id('papernum') is null
drop table papernum
Go
Create table papernum([nid] int,[factno] int,[deptno] int,[padate] nvarchar(7),[mou] int)
Insert papernum
select 1,1,2,N'2007-07',56 union all
select 2,1,3,N'2007-07',26 union all
select 3,1,2,N'2008-01',10 union all
select 4,2,1,N'2007-06',25 union all
select 5,3,1,N'2007-06',12 union all
select 6,2,2,N'2008-06',25 union all
select 7,3,3,N'2008-05',26 union all
select 8,1,3,N'2007-06',30
Go
if not object_id('fact') is null
drop table fact
Go
Create table fact([fno] int,[factname] nvarchar(3))
Insert fact
select 1,N'cs1' union all
select 2,N'cs2' union all
select 3,N'cs3'
Go
--dept沒有用到
if not object_id('dept') is null
drop table dept
Go
Create table dept([did] int,[deptname] nvarchar(3))
Insert dept
select 1,N'AS1' union all
select 2,N'AS2' union all
select 3,N'AS3'
Go
declare @s nvarchar(4000),@i int
select @s='',@i=12
while @i>0
select @s=','+quotename(case @i when 1 then '一月' when 2 then '二月' when 3 then '三月' when 4 then '四月' when 5 then '五月' when 6 then '六月'
when 7 then '七月' when 8 then '八月' when 9 then '九月' when 10 then '十月' when 11 then '十一月' when 12 then '十二月' end)
+'=sum(case when right([padate],2)='+rtrim(@i)+' then [mou] else 0 end)'+@s,@i=@i-1
exec('select b.[factname]+''/''+left(a.[padate],4) as [fa/pa]'+@s+',[total]=(select sum([mou]) from papernum where left([padate],4)=left(a.[padate],4) and [factno]=a.[factno])
from papernum a join fact b on a.[factno]=b.[fno] group by b.[factname],left(a.[padate],4),a.[factno] order by left(a.[padate],4)desc')
fa/pa 一月 二月 三月 四月 五月 六月 七月 八月 九月 十月 十一月 十二月 total
-------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
cs1/2008 10 0 0 0 0 0 0 0 0 0 0 0 10
cs2/2008 0 0 0 0 0 25 0 0 0 0 0 0 25
cs3/2008 0 0 0 0 26 0 0 0 0 0 0 0 26
cs1/2007 0 0 0 0 0 30 82 0 0 0 0 0 112
cs2/2007 0 0 0 0 0 25 0 0 0 0 0 0 25
cs3/2007 0 0 0 0 0 12 0 0 0 0 0 0 12(6 個資料列受到影響)
if not object_id('papernum') is null
drop table papernum
Go
Create table papernum([nid] int,[factno] int,[deptno] int,[padate] nvarchar(7),[mou] int)
Insert papernum
select 1,1,2,N'2007-07',56 union all
select 2,1,3,N'2007-07',26 union all
select 3,1,2,N'2008-01',10 union all
select 4,2,1,N'2007-06',25 union all
select 5,3,1,N'2007-06',12 union all
select 6,2,2,N'2008-06',25 union all
select 7,3,3,N'2008-05',26 union all
select 8,1,3,N'2007-06',30
Go
if not object_id('fact') is null
drop table fact
Go
Create table fact([fno] int,[factname] nvarchar(3))
Insert fact
select 1,N'cs1' union all
select 2,N'cs2' union all
select 3,N'cs3'
Go
--dept沒有用到
if not object_id('dept') is null
drop table dept
Go
Create table dept([did] int,[deptname] nvarchar(3))
Insert dept
select 1,N'AS1' union all
select 2,N'AS2' union all
select 3,N'AS3'
Go
declare @s nvarchar(4000),@s2 nvarchar(4000),@i int
select @s='',@s2='',@i=12
while @i>0
select @s=','+quotename(case @i when 1 then '一月' when 2 then '二月' when 3 then '三月' when 4 then '四月' when 5 then '五月' when 6 then '六月'
when 7 then '七月' when 8 then '八月' when 9 then '九月' when 10 then '十月' when 11 then '十一月' when 12 then '十二月' end)
+'=sum(case when right([padate],2)='+rtrim(@i)+' then [mou] else 0 end)'+@s,
@s2=','+quotename(case @i when 1 then '一月' when 2 then '二月' when 3 then '三月' when 4 then '四月' when 5 then '五月' when 6 then '六月'
when 7 then '七月' when 8 then '八月' when 9 then '九月' when 10 then '十月' when 11 then '十一月' when 12 then '十二月' end)
+'=sum(case when right([padate],2)='+rtrim(@i)+' then [mou] else 0 end)'+@s2,
@i=@i-1
exec('select b.[factname]+''/''+left(a.[padate],4) as [fa/pa]'+@s+',[total]=(select sum([mou]) from papernum where left([padate],4)=left(a.[padate],4) and [factno]=a.[factno])
from papernum a join fact b on a.[factno]=b.[fno] group by b.[factname],left(a.[padate],4),a.[factno]
union all
select [fa/pa]=''Total'''+@s2+',sum([mou]) from papernum order by [fa/pa]')
fa/pa 一月 二月 三月 四月 五月 六月 七月 八月 九月 十月 十一月 十二月 total
-------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
cs1/2007 0 0 0 0 0 30 82 0 0 0 0 0 112
cs1/2008 10 0 0 0 0 0 0 0 0 0 0 0 10
cs2/2007 0 0 0 0 0 25 0 0 0 0 0 0 25
cs2/2008 0 0 0 0 0 25 0 0 0 0 0 0 25
cs3/2007 0 0 0 0 0 12 0 0 0 0 0 0 12
cs3/2008 0 0 0 0 26 0 0 0 0 0 0 0 26
Total 10 0 0 0 26 92 82 0 0 0 0 0 210(7 個資料列受到影響)
insert into tb select 1,'2008-01-01',10
insert into tb select 2,'2008-01-01',10
insert into tb select 3,'2008-02-01',20
insert into tb select 4,'2008-02-01',20
insert into tb select 5,'2008-03-01',30
insert into tb select 6,'2008-03-01',30
insert into tb select 7,'2008-04-01',40
insert into tb select 8,'2008-04-01',40
insert into tb select 9,'2008-05-01',50
insert into tb select 10,'2008-05-01',50
insert into tb select 11,'2008-06-01',60
insert into tb select 12,'2008-06-01',60
insert into tb select 13,'2008-07-01',70
insert into tb select 14,'2008-07-01',70
insert into tb select 15,'2008-08-01',80
insert into tb select 16,'2008-08-01',80
insert into tb select 17,'2008-09-01',90
insert into tb select 18,'2008-09-01',90
insert into tb select 19,'2008-10-01',100
insert into tb select 20,'2008-10-01',100
insert into tb select 21,'2008-11-01',110
insert into tb select 22,'2008-11-01',110
insert into tb select 23,'2008-12-01',120
insert into tb select 24,'2008-12-01',120--静态查询
select
sum(case when datepart(yy,date)='2008' and datepart(mm,date)='01' then sale else 0 end) as '1月',
sum(case when datepart(yy,date)='2008' and datepart(mm,date)='02' then sale else 0 end) as '2月',
sum(case when datepart(yy,date)='2008' and datepart(mm,date)='03' then sale else 0 end) as '3月',
sum(case when datepart(yy,date)='2008' and datepart(mm,date)='04' then sale else 0 end) as '4月',sum(case when datepart(yy,date)='2008' and datepart(mm,date)='05' then sale else 0 end) as '5月',
sum(case when datepart(yy,date)='2008' and datepart(mm,date)='06' then sale else 0 end) as '6月',
sum(case when datepart(yy,date)='2008' and datepart(mm,date)='07' then sale else 0 end) as '7月',
sum(case when datepart(yy,date)='2008' and datepart(mm,date)='08' then sale else 0 end) as '8月',sum(case when datepart(yy,date)='2008' and datepart(mm,date)='09' then sale else 0 end) as '9月',
sum(case when datepart(yy,date)='2008' and datepart(mm,date)='10' then sale else 0 end) as '10月',
sum(case when datepart(yy,date)='2008' and datepart(mm,date)='11' then sale else 0 end) as '11月',
sum(case when datepart(yy,date)='2008' and datepart(mm,date)='12' then sale else 0 end) as '12月'
from tb
--插入2009年数据
insert into tb select 25,'2009-01-01',10
--动态查询
declare @sql varchar(max)
select @sql=isnull(@sql+',','')+'sum(case when datepart(yy,date)='''+ltrim([year])+''' and datepart(mm,date)='''+ltrim([month])+''' then sale else 0 end) as ['+ltrim([year])+'年'+ltrim([month])+'月]'
from (select distinct datepart(yy,date) as [year],datepart(mm,date) as [month] from tb)a
exec('select '+@sql+' from tb')
select b.factname+'/'+a.pa "fa/pa", a.*
from
(select factno,substring(padate,1,4) pa,
sum(case when substring(padate,6,2) = '01' then mou else 0 end) '一月',
sum(case when substring(padate,6,2) = '02' then mou else 0 end) '二月',
sum(case when substring(padate,6,2) = '03' then mou else 0 end) '三月',
sum(case when substring(padate,6,2) = '04' then mou else 0 end) '四月',
sum(case when substring(padate,6,2) = '05' then mou else 0 end) '五月',
sum(case when substring(padate,6,2) = '06' then mou else 0 end) '六月',
sum(case when substring(padate,6,2) = '07' then mou else 0 end) '七月',
sum(case when substring(padate,6,2) = '08' then mou else 0 end) '八月',
sum(case when substring(padate,6,2) = '09' then mou else 0 end) '九月',
sum(case when substring(padate,6,2) = '10' then mou else 0 end) '十月',
sum(case when substring(padate,6,2) = '11' then mou else 0 end) '十一月',
sum(case when substring(padate,6,2) = '12' then mou else 0 end) '十二月',
sum(mou) total
from papernum
group by factno,substring(padate,1,4)) a, fact b
where a.factno=b.fno
select b.factname+'/'+a.pa "fa/pa", a.*
from
(select factno,substring(padate,1,4) pa,
sum(case when substring(padate,6,2) = '01' then mou else 0 end) '一月',
sum(case when substring(padate,6,2) = '02' then mou else 0 end) '二月',
sum(case when substring(padate,6,2) = '03' then mou else 0 end) '三月',
sum(case when substring(padate,6,2) = '04' then mou else 0 end) '四月',
sum(case when substring(padate,6,2) = '05' then mou else 0 end) '五月',
sum(case when substring(padate,6,2) = '06' then mou else 0 end) '六月',
sum(case when substring(padate,6,2) = '07' then mou else 0 end) '七月',
sum(case when substring(padate,6,2) = '08' then mou else 0 end) '八月',
sum(case when substring(padate,6,2) = '09' then mou else 0 end) '九月',
sum(case when substring(padate,6,2) = '10' then mou else 0 end) '十月',
sum(case when substring(padate,6,2) = '11' then mou else 0 end) '十一月',
sum(case when substring(padate,6,2) = '12' then mou else 0 end) '十二月',
sum(mou) total
from papernum
group by factno,substring(padate,1,4)) a, fact b
where a.factno=b.fno
union all
select 'total', null,null,
sum(case when substring(padate,6,2) = '01' then mou else 0 end) '一月',
sum(case when substring(padate,6,2) = '02' then mou else 0 end) '二月',
sum(case when substring(padate,6,2) = '03' then mou else 0 end) '三月',
sum(case when substring(padate,6,2) = '04' then mou else 0 end) '四月',
sum(case when substring(padate,6,2) = '05' then mou else 0 end) '五月',
sum(case when substring(padate,6,2) = '06' then mou else 0 end) '六月',
sum(case when substring(padate,6,2) = '07' then mou else 0 end) '七月',
sum(case when substring(padate,6,2) = '08' then mou else 0 end) '八月',
sum(case when substring(padate,6,2) = '09' then mou else 0 end) '九月',
sum(case when substring(padate,6,2) = '10' then mou else 0 end) '十月',
sum(case when substring(padate,6,2) = '11' then mou else 0 end) '十一月',
sum(case when substring(padate,6,2) = '12' then mou else 0 end) '十二月',
sum(mou) total
from papernum
select b.factoryname/left(a.padate,4) as fa/pa,
'一月'=sum(case when right(a.padate,2)='01' then isnull(a.mou,0) else 0 end),
'二月'=sum(case when right(a.padate,2)='02' then isnull(a.mou,0) else 0 end),
.....
'total'=(select sum(mou) from papernum c where c.factno=a.factno and left(c.padate,4)=left(a.padate,4))
from papernum a, factname b where a.factno=b.fno group by b.factoryname,left(padate,4)
union all
select 'total' as fa/pa,
'一月'=sum(case when right(padate,2)='01' then isnull(mou,0) else 0 end),
'二月'=sum(case when right(padate,2)='02' then isnull(mou,0) else 0 end),
.....
'total'=sum(mou)
from papernum
事例如下：
select year(getdate()),month(getdate()),day(getdate())
谢谢所有回帖的朋友们，我在看看