Question

SQL问题 分组求和

如下数据：
日期 姓名 金额
2008-1-12 sun 100
2008-1-12 张 100
2008-1-13 sun 100
2008-1-19 张 300
2008-2-12 sun 120
2008-2-19 张 300
2008-3-12 sun 130
得到的查询结果：
姓名 2008-01 2008-02 2008-03
sun 200 120 130
张 400 300
请写出查询语句，提示：分组、求和 每个月每个人得到的金额

Answer 1

--> --> (Roy)生成测试数据

if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([日期] Datetime,[姓名] nvarchar(3),[金额] int)
Insert #T
select '2008-1-12',N'sun',100 union all
select '2008-1-12',N'张',100 union all
select '2008-1-13',N'sun',100 union all
select '2008-1-19',N'张',300 union all
select '2008-2-12',N'sun',120 union all
select '2008-2-19',N'张',300 union all
select '2008-3-12',N'sun',130
Go
declare @S nvarchar(4000)
set @s='select [姓名]'
Select @s=@s+','+quotename(convert(varchar(7),[日期],120))+'=sum(case when convert(varchar(7),[日期],120)='+quotename(convert(varchar(7),[日期],120),'''')+' then [金额] else 0 end)'
from #T group by convert(varchar(7),[日期],120)

exec(@s+' from #T group by [姓名]')

--或
select [姓名],
[2008-01]=sum(case when convert(varchar(7),[日期],120)='2008-01' then [金额] else 0 end),
[2008-02]=sum(case when convert(varchar(7),[日期],120)='2008-02' then [金额] else 0 end),
[2008-03]=sum(case when convert(varchar(7),[日期],120)='2008-03' then [金额] else 0 end)
from #T group by [姓名]

姓名 2008-01 2008-02 2008-03
---- ----------- ----------- -----------
sun 200 120 130
张 400 300 0

(2 行受影响)

Answer 2

select 日期，姓名，sum (金额) from 表名
where 条件
group by 日期，姓名