统计的问题~~~写不出来，帮帮忙 。不知道能不能实现啊 。。。哎~~~
表：
deptID datatime money
1 2006-03-10 100
2 2006-03-10 200
3 2006-03-10 300
4 2006-03-10 400
1 2007-02-10 1000
2 2007-02-10 2000
3 2007-02-10 3000
4 2007-02-10 4000
结果： 1 2 3 4 合计
月份 2006 2007 2006 2007 2006 2007 2006 2007 2006 2007
02 0 1000 0 2000 0 3000 0 4000 0 10000
03 100 0 200 0 300 0 400 0 1000 0
不知道能不能实现啊 。。。哎~~~
[解决办法]
declare @a table(deptID int,datatime char(10),money int)
insert into @a select 1 , '2006-03-10 ' , 100
union all select 2 , '2006-03-10 ' , 200
union all select 3 , '2006-03-10 ' , 300
union all select 4 , '2006-03-10 ' , 400
union all select 1 , '2007-02-10 ' , 1000
union all select 2 , '2007-02-10 ' , 2000
union all select 3 , '2007-02-10 ' , 3000
union all select 4 , '2007-02-10 ' , 4000
select distinct substring(datatime,6,2)as yuefen,
sum(case when deptid=1 and left(datatime,4)= '2006 'then money else 0 end)a,
sum(case when deptid=1 and left(datatime,4)= '2007 'then money else 0 end)b,
sum(case when deptid=2 and left(datatime,4)= '2006 'then money else 0 end)c,
sum(case when deptid=2 and left(datatime,4)= '2007 'then money else 0 end)d,
sum(case when deptid=3 and left(datatime,4)= '2006 'then money else 0 end)e,
sum(case when deptid=3 and left(datatime,4)= '2007 'then money else 0 end)f,
sum(case when deptid=4 and left(datatime,4)= '2006 'then money else 0 end)g,
sum(case when deptid=4 and left(datatime,4)= '2007 'then money else 0 end)h,
sum(case when left(datatime,4)= '2006 'then money else 0 end)i,
sum(case when left(datatime,4)= '2007 'then money else 0 end)j
into #a from @a group by substring(datatime,6,2)
select * from(select 结果=yuefen,[1]=cast(a as varchar)+ ' '+cast(b as varchar),[2]=cast(c as varchar)+ ' '+cast(d as varchar),
[3]=cast(e as varchar)+ ' '+cast(f as varchar),
[4]=cast(g as varchar)+ ' '+cast(h as varchar),
合计=cast(i as varchar)+ ' '+cast(j as varchar) from #a
union all
select 结果= '月份 ',[1]= '2006 '+ ' '+ '2007 ',[2]= '2006 '+ ' '+ '2007 ',[3]= '2006 '+ ' '+ '2007 ',[4]= '2006 '+ ' '+ '2007 ',
合计= '2006 '+ ' '+ '2007 ' )a order by (case when 结果= '月份 ' then '0 ' else 结果 end)
result:
结果 1 2 3 4 合计
---- ------------------------- ------------------------- ------------------------- ------------------------- -------------------------
月份 2006 2007 2006 2007 2006 2007 2006 2007 2006 2007
02 0 1000 0 2000 0 3000 0 4000 0 10000
03 100 0 200 0 300 0 400 0 1000 0
（所影响的行数为 3 行）
这个比较死板,呵呵
[解决办法]
---看看这种结果，结果和你要求的不一样
Declare @sql Varchar(1000)
Set @sql= ' '
Select @sql=@sql+ ',Max(Case When dyear= ' ' '+rtrim(dyear)+ ' ' ' And dmonth= ' ' '+rtrim(dmonth)+ ' ' ' And
deptID= ' ' '+rtrim(deptid)+ ' ' ' Then [money] Else 0 End) As [ '+right(dyear,2)+ '0 '+rtrim(deptID)+ '] ' From
(Select deptID,datepart(yy,datatime) As dyear,datepart(mm,datatime) As dmonth,[money] From 表) As A
Group By dyear,dmonth,deptID
Print @sql
Exec( 'Select dyear As 年份,dmonth As 月份 '+@sql+ ',Sum(money) As 合计 From (
Select deptID,datepart(yy,datatime) As dyear,datepart(mm,datatime) As dmonth,[money] From 表) As A
Group By dyear,dmonth Order By dyear,dmonth ')
[解决办法]
都不错啊！
[解决办法]
create table tb(deptID int,datatime datetime,money int)
insert into tb values(1, '2006-03-10 ',100)
insert into tb values(2, '2006-03-10 ',200)
insert into tb values(3, '2006-03-10 ',300)
insert into tb values(4, '2006-03-10 ',400)
insert into tb values(1, '2007-02-10 ',1000)
insert into tb values(2, '2007-02-10 ',2000)
insert into tb values(3, '2007-02-10 ',3000)
insert into tb values(4, '2007-02-10 ',4000)
go
--如果部门ID，年月固定，使用如下静态SQL
select month(datatime) 月份,
sum(case when deptid = 1 and year(datatime) = 2006 then money else 0 end) '1_2006 ',
sum(case when deptid = 1 and year(datatime) = 2007 then money else 0 end) '1_2007 ',
sum(case when deptid = 2 and year(datatime) = 2006 then money else 0 end) '2_2006 ',
sum(case when deptid = 2 and year(datatime) = 2007 then money else 0 end) '2_2007 ',
sum(case when deptid = 3 and year(datatime) = 2006 then money else 0 end) '3_2006 ',
sum(case when deptid = 3 and year(datatime) = 2007 then money else 0 end) '3_2007 ',
sum(case when deptid = 4 and year(datatime) = 2006 then money else 0 end) '4_2006 ',
sum(case when deptid = 4 and year(datatime) = 2007 then money else 0 end) '4_2007 ',
sum(case when year(datatime) = 2006 then money else 0 end) '合计_2006 ',
sum(case when year(datatime) = 2007 then money else 0 end) '合计_2007 '
from tb
group by month(datatime)
/*
月份 1_2006 1_2007 2_2006 2_2007 3_2006 3_2007 4_2006 4_2007 合计_2006 合计_2007
---- ------ ------ ------ ------ ------ ------ ------ ------ --------- -----------
2 0 1000 0 2000 0 3000 0 4000 0 10000
3 100 0 200 0 300 0 400 0 1000 0
（所影响的行数为 2 行）
*/
--如果部门ID，月不固定但年必须固定（2006，2007），使用如下动态SQL
declare @sql varchar(8000)
set @sql = 'select month(datatime) 月份 '
select @sql = @sql + ' , sum(case when deptID = ' + cast(deptID as varchar) + ' and year(datatime) = 2006 then money else 0 end) [ ' + cast(deptID as varchar) + '_2006] '
+ ' , sum(case when deptID = ' + cast(deptID as varchar) + ' and year(datatime) = 2007 then money else 0 end) [ ' + cast(deptID as varchar) + '_2007] '
from (select distinct deptID from tb) as a
set @sql = @sql
+ ' , sum(case when year(datatime) = 2006 then money else 0 end) ' + '合计_2006 '
+ ' , sum(case when year(datatime) = 2006 then money else 0 end) ' + '合计_2007 '
+ ' from tb group by month(datatime) '
exec(@sql)
/*
月份 1_2006 1_2007 2_2006 2_2007 3_2006 3_2007 4_2006 4_2007 合计_2006 合计_2007
---- ------ ------ ------ ------ ------ ------ ------ ------ --------- -----------
2 0 1000 0 2000 0 3000 0 4000 0 10000
3 100 0 200 0 300 0 400 0 1000 0
（所影响的行数为 2 行）
*/
drop table tb
[解决办法]
用动态语句吧
*--行列互换的通用存储过程 ： 将指定的表,按指定的字段进行行列互换
--邹建 2004.04--
--使用示例
--测试数据
create table 表(类别 varchar(10),男性 decimal(20,1),女性 decimal(20,1))
insert 表 select '小说 ',38.0,59.2
union all select '散文 ',18.9,30.6
union all select '哲学 ',16.2,10.2
--要求转换结果
/*
性别 小说 散文 哲学
---- ----- ----- -----
男性 38.0 18.9 16.2
女性 59.2 30.6 10.2
*/
--调用存储过程
exec p_zj '表 ', '类别 ', '性别 '
--删除测试
drop table 表
*/
create proc p_zj
@tbname sysname, --要处理的表名
@fdname sysname, --做为转换的列名
@new_fdname sysname= ' ' --为转换后的列指定列名
as
declare @s1 varchar(8000),@s2 varchar(8000)
,@s3 varchar(8000),@s4 varchar(8000),@s5 varchar(8000)
,@i varchar(10)
select @s1= ' ',@s2= ' ',@s3= ' ',@s4= ' ',@s5= ' ',@i= '0 '
select @s1=@s1+ ',@ '+@i+ ' varchar(8000) '
,@s2=@s2+ ',@ '+@i+ '= ' ' '+case isnull(@new_fdname, ' ') when ' ' then ' '
else @new_fdname+ '= ' end+ ' ' ' ' ' '+name+ ' ' ' ' ' ' ' '
-- ,@s2=@s2+ ',@ '+@i+ '= ' '性别= ' ' ' ' '+name+ ' ' ' ' ' ' ' '
,@s3=@s3+ '
select @ '+@i+ '=@ '+@i+ '+ ' ',[ ' '+[ '+@fdname+ ']+ ' ']= ' '+cast([ '+name+ '] as varchar) from [ '+@tbname+ '] '
,@s4=@s4+ ',@ '+@i+ '= ' 'select ' '+@ '+@i
,@s5=@s5+ '+ ' ' union all ' '+@ '+@i
,@i=cast(@i as int)+1
from syscolumns
where object_id(@tbname)=id and name <> @fdname
select @s1=substring(@s1,2,8000)
,@s2=substring(@s2,2,8000)
,@s4=substring(@s4,2,8000)
,@s5=substring(@s5,16,8000)
exec( 'declare '+@s1+ '
select '+@s2+@s3+ '
select '+@s4+ '
exec( '+@s5+ ') ')
go
[解决办法]
create table tb(deptID int,datatime datetime,money int)
insert into tb values(1, '2006-03-10 ',100)
insert into tb values(2, '2006-03-10 ',200)
insert into tb values(3, '2006-03-10 ',300)
insert into tb values(4, '2006-03-10 ',400)
insert into tb values(1, '2007-02-10 ',1000)
insert into tb values(2, '2007-02-10 ',2000)
insert into tb values(3, '2007-02-10 ',3000)
insert into tb values(4, '2007-02-10 ',4000)
go
--如果部门ID，年月固定，使用如下静态SQL
select month(datatime) 月份,
sum(case when deptid = 1 and year(datatime) = 2006 then money else 0 end) '1_2006 ',
sum(case when deptid = 1 and year(datatime) = 2007 then money else 0 end) '1_2007 ',
sum(case when deptid = 2 and year(datatime) = 2006 then money else 0 end) '2_2006 ',
sum(case when deptid = 2 and year(datatime) = 2007 then money else 0 end) '2_2007 ',
sum(case when deptid = 3 and year(datatime) = 2006 then money else 0 end) '3_2006 ',
sum(case when deptid = 3 and year(datatime) = 2007 then money else 0 end) '3_2007 ',
sum(case when deptid = 4 and year(datatime) = 2006 then money else 0 end) '4_2006 ',
sum(case when deptid = 4 and year(datatime) = 2007 then money else 0 end) '4_2007 ',
sum(case when year(datatime) = 2006 then money else 0 end) '合计_2006 ',
sum(case when year(datatime) = 2007 then money else 0 end) '合计_2007 '
from tb
group by month(datatime)
/*
月份 1_2006 1_2007 2_2006 2_2007 3_2006 3_2007 4_2006 4_2007 合计_2006 合计_2007
---- ------ ------ ------ ------ ------ ------ ------ ------ --------- -----------
2 0 1000 0 2000 0 3000 0 4000 0 10000
3 100 0 200 0 300 0 400 0 1000 0
（所影响的行数为 2 行）
*/
--如果部门ID，年月都不固定，使用如下动态SQL
declare @sql varchar(8000)
set @sql = 'select convert(varchar(7),datatime,120) 月份 '
select @sql = @sql + ' , sum(case when deptID = ' + cast(deptID as varchar) + ' then money else 0 end) [ ' + cast(deptID as varchar) + '] '
from (select distinct deptID from tb) as a
set @sql = @sql
+ ' , sum(money ) ' + '合计 '
+ ' from tb group by convert(varchar(7),datatime,120) '
exec(@sql)
/*
月份 1 2 3 4 合计
------- ----------- ----------- ----------- ----------- -----------
2006-03 100 200 300 400 1000
2007-02 1000 2000 3000 4000 10000
*/
drop table tb
