Sql单列不同数据统计问题
0
[已解决问题]
解决于 2012-12-27 16:39
[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
最佳答案
0
select * from test go select agtentid,isnull([1],0) [type1],isnull([2],0) [type2],[date] from( select agtentid,date,type,sum(money) m from test group by agtentid,date,type ) as S pivot ( max(m) for [type] in ([1],[2]) ) pvt =========================================== 1 10.00 1 2012-12-27 00:00:00.000 2 10.00 2 2012-12-27 00:00:00.000 1 10.00 1 2012-12-20 00:00:00.000 1 10.00 1 2012-12-20 00:00:00.000 =========================================== 1 20.00 0.00 2012-12-20 00:00:00.000 1 10.00 0.00 2012-12-27 00:00:00.000 2 0.00 10.00 2012-12-27 00:00:00.000
奖励园豆：5
其他回答(2)
0
按 Type 统计显示不同的名称。????
select adi,data,sum(money)
group by aid,date
0
type是确定个数的吗？
如果是
select agentid,date,sum(type1) as type1,sum(type2) as type2 from ( select agentid,date, case when type=1 then money else 0 end as type1, case when type=2 then money else 0 end as type2 from tablename ) a group by agentid,date
