表tba中有字段 depart realmoney realtime怎么通过今天的日期得到每个depart在1-12中realmoney的总和比方有数据
人事 100 2010-1-3 8:36:00
人事 500 2009-12-3 8:36:00
人事 200 2010-2-3 8:36:00
人事 300 2010-2-3 8:36:00
人事 50 2010-3-3 8:36:00
营业 100 2010-1-3 8:36:00
营业 600 2010-7-3 8:36:00那么要得到
depart 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月
人事 100 500 50 0 0 0 0 0 0 0 0 0
营业 100 0 0 0 0 0 600 0 0 0 0 0select语句得怎么写呢?
人事 100 2010-1-3 8:36:00
人事 500 2009-12-3 8:36:00
人事 200 2010-2-3 8:36:00
人事 300 2010-2-3 8:36:00
人事 50 2010-3-3 8:36:00
营业 100 2010-1-3 8:36:00
营业 600 2010-7-3 8:36:00那么要得到
depart 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月
人事 100 500 50 0 0 0 0 0 0 0 0 0
营业 100 0 0 0 0 0 600 0 0 0 0 0select语句得怎么写呢?
[1月]=sum(case when datepart(mm,realtime)=1 then realmoney else 0 end),
[2月]=sum(case when datepart(mm,realtime)=2 then realmoney else 0 end),
[3月]=sum(case when datepart(mm,realtime)=3 then realmoney else 0 end),
...
[12月]=sum(case when datepart(mm,realtime)=12 then realmoney else 0 end)
from tba
where datediff(yy,realtime,getdate())=0
group by depart
SUM(CASE WHEN DATEPART(MM,realtime)=1 THEN realmoney ELSE 0 END) AS '1月',
SUM(CASE WHEN DATEPART(MM,realtime)=2 THEN realmoney ELSE 0 END) AS '2月',
SUM(CASE WHEN DATEPART(MM,realtime)=3 THEN realmoney ELSE 0 END) AS '3月',
SUM(CASE WHEN DATEPART(MM,realtime)=4 THEN realmoney ELSE 0 END) AS '4月',
SUM(CASE WHEN DATEPART(MM,realtime)=5 THEN realmoney ELSE 0 END) AS '5月',
SUM(CASE WHEN DATEPART(MM,realtime)=6 THEN realmoney ELSE 0 END) AS '6月',
SUM(CASE WHEN DATEPART(MM,realtime)=7 THEN realmoney ELSE 0 END) AS '7月',
SUM(CASE WHEN DATEPART(MM,realtime)=8 THEN realmoney ELSE 0 END) AS '8月',
SUM(CASE WHEN DATEPART(MM,realtime)=9 THEN realmoney ELSE 0 END) AS '9月',
SUM(CASE WHEN DATEPART(MM,realtime)=10 THEN realmoney ELSE 0 END) AS '10月',
SUM(CASE WHEN DATEPART(MM,realtime)=11 THEN realmoney ELSE 0 END) AS '11月',
SUM(CASE WHEN DATEPART(MM,realtime)=12 THEN realmoney ELSE 0 END) AS '12月'
FROM TB
WHERE DATEPART(YY,realtime)=YEAR(GETDATE())
GROUP BY depart
if object_id('[tba]') is not null drop table [tba]
create table [tba]([depart] varchar(4),[realmoney] int,[realtime] datetime)
go
insert [tba]
select '人事',100,'2010-1-3 8:36:00' union all
select '人事',500,'2009-12-3 8:36:00' union all
select '人事',200,'2010-2-3 8:36:00' union all
select '人事',300,'2010-2-3 8:36:00' union all
select '人事',50,'2010-3-3 8:36:00' union all
select '营业',100,'2010-1-3 8:36:00' union all
select '营业',600,'2010-7-3 8:36:00'declare @sql varchar(8000)select @sql = isnull(@sql+',
','')+'sum(case month(realtime) when '+ltrim(number)+' then realmoney else 0 end) as ['+ltrim(number)+'月]'
from master..spt_values
where type = 'P' and number between 1 and 12select @sql = 'select depart,'+@sql+' from tba group by depart'exec(@sql)------------------------------
人事 100 500 50 0 0 0 0 0 0 0 0 500
营业 100 0 0 0 0 0 600 0 0 0 0 0
,isnull([1],0) as '1月'
,isnull([2],0) as '2月'
,isnull([3],0) as '3月'
,isnull([4],0) as '4月'
,isnull([5],0) as '5月'
,isnull([6],0) as '6月'
,isnull([7],0) as '7月'
,isnull([8],0) as '8月'
,isnull([9],0) as '9月'
,isnull([10],0) as '10月'
,isnull([11],0) as '11月'
,isnull([12],0) as '12月'
from (select depart,realmoney,datepart(month,realtime) as mon from #test) a
PIVOT
(
sum(realmoney)
for mon in([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) as pvt
if object_id('tb') is not null drop table tb
create table tb(depart varchar(10),realmoney int,realtime datetime)
insert into tb
select '人事',100,'2010-1-3 8:36:00' union all
select '人事',500,'2009-12-3 8:36:00' union all
select '人事',200,'2010-2-3 8:36:00'union all
select '人事',300,'2010-2-3 8:36:00' union all
select '人事',50,'2010-3-3 8:36:00' union all
select '营业',100,'2010-1-3 8:36:00' union all
select '营业',600,'2010-7-3 8:36:00'
--语句
select depart,sum(case when substring(convert(char(6),realtime,112),5,2)='01' then realmoney end) as 一月,
sum(case when substring(convert(char(6),realtime,112),5,2)='02' then realmoney end) as 二月,
sum(case when substring(convert(char(6),realtime,112),5,2)='03' then realmoney end) as 三月,
sum(case when substring(convert(char(6),realtime,112),5,2)='04' then realmoney end) as 四月,
sum(case when substring(convert(char(6),realtime,112),5,2)='05' then realmoney end) as 五月,
sum(case when substring(convert(char(6),realtime,112),5,2)='06' then realmoney end) as 六月,
sum(case when substring(convert(char(6),realtime,112),5,2)='07' then realmoney end) as 七月,
sum(case when substring(convert(char(6),realtime,112),5,2)='08' then realmoney end) as 八月,
sum(case when substring(convert(char(6),realtime,112),5,2)='09' then realmoney end) as 九月,
sum(case when substring(convert(char(6),realtime,112),5,2)='10' then realmoney end) as 十月,
sum(case when substring(convert(char(6),realtime,112),5,2)='11' then realmoney end) as 十一月,
sum(case when substring(convert(char(6),realtime,112),5,2)='12' then realmoney end) as 十二月,
sum(realmoney) 全年
from tb
where substring(convert(char(6),realtime,112),1,4)=substring(convert(char(6),getdate(),112),1,4)
group by depart
declare @tb table (depart nvarchar(10),
realmoney int,
realtime datetime)
insert into @tb select '人事',100,'2010-1-3 8:36:00'
union all select '人事',500,'2009-12-3 8:36:00'
union all select '人事',200,'2010-2-3 8:36:00'
union all select '人事',300,'2010-2-3 8:36:00'
union all select '人事',50,'2010-3-3 8:36:00'
union all select '营业',100,'2010-1-3 8:36:00'
union all select '营业',600,'2010-7-3 8:36:00'
--select depart,month(realtime) realmoney from @tb
select depart,isnull([1],0) as [1],
isnull([2],0) as [2],
isnull([3],0) as [3],
isnull([4],0) as [4],
isnull([5],0) as [5],
isnull([6],0) as [6],
isnull([7],0) as [7],
isnull([8],0) as [8],
isnull([9],0) as [9],
isnull([10],0) as [10],
isnull([11],0) as [11],
isnull([12],0) as [12] from (select depart,month(realtime) as realtime ,realmoney from @tb) a pivot (max(realmoney) for realtime in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) b
/*
(7 行受影响)
depart 1 2 3 4 5 6 7 8 9 10 11 12
---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
人事 100 300 50 0 0 0 0 0 0 0 0 500
营业 100 0 0 0 0 0 600 0 0 0 0 0(2 行受影响)*/