Sql单列不同数据统计问题
[id] [AgtentId] [Money] [Type] [Date]
1 1 10.00 1 2012-12-27
2 2 10.00 2 2012-12-27
3 1 10.00 1 2012-12-20
4 1 10.00 1 2012-12-20
id是主键
出来的结果希望：
以 AgtentId 和 Date 分组，统计出每个 AgtentId 的当天 Money 总和按 Type 统计显示不同的名称。
[AgtentId] [类型1] [类型2] [Date]
1 10.00 0 2012-12-27
2 0 10.00 2012-12-27
1 20.00 0 2012-12-20
[解决办法]
SELECT AgtentId,[type 1]=SUM(CASE WHEN type = 1 THEN money ELSE 0 END),
[type 2]=SUM(CASE WHEN type = 2 THEN money ELSE 0 END) ,
date
FROM dbo.TB
GROUP BY AgtentId,date
ORDER BY date
/*
AgtentIdtype 1type 2date
120.000.002012-12-20 00:00:00.000
110.000.002012-12-27 00:00:00.000
20.0010.002012-12-27 00:00:00.000*/
with tb(a,b,c,d,e) as(
select 1,1,10.00,1,'2012-12-27' union all
select 2,2,10.00,2,'2012-12-27' union all
select 3,1,10.00,1,'2012-12-20' union all
select 4,1,10.00,1,'2012-12-20')
select b,SUM(case when b=1 then c else 0 end),SUM(case when b=2 then c else 0 end),
e from tb group by b,e order by e desc
