表A
id value date type
1 10 2008-08-06 1
2 12 2008-12-01 1
3 13 2008-12-03 1
4 14 2008-12-01 2
5 14 2008-12-09 2结果type value(年) value(12月)
1 35 25
2 28 28
id value date type
1 10 2008-08-06 1
2 12 2008-12-01 1
3 13 2008-12-03 1
4 14 2008-12-01 2
5 14 2008-12-09 2结果type value(年) value(12月)
1 35 25
2 28 28
value=sum(case when datepart(mm,[date])=12 then value else 0 end) from tb group by type
go
create table [tb]([id] int,[value] int,[date] datetime,[type] int)
insert [tb]
select 1,10,'2008-08-06',1 union all
select 2,12,'2008-12-01',1 union all
select 3,13,'2008-12-03',1 union all
select 4,14,'2008-12-01',2 union all
select 5,14,'2008-12-09',2select * from [tb]select type,[value(年)]=sum(value),[value(12月)]=sum(case month(date) when 12 then value else 0 end)
from tb
group by type
--测试结果:
/*
type value(年) value(12月)
----------- ----------- -----------
1 35 25
2 28 28(2 行受影响)
*/
[value(12月)]=sum(case when datediff(month,date,'2008-12-01')=0 then value else 0 end)
from 表A
group by type
from
(select sum(value) as value(年) , type
from table
group by type
) AS A,
(select sum(value) as value(12月),type
from table
where datepart(month,convert(dateTime, date,101))=12
group by type
) AS B
where A.type=B.type