根据自然月分组统计的SQL语
SQL code
代码
---测试数据---
if object_id('[Invoices]') is not null drop table [Invoices]
go
create table [Invoices]([id] int,[Account] varchar(2),[Amount] numeric(5,2),[InvoiceDate] datetime)
insert [Invoices]
select 1,'CT',100.00,'2008-01-01' union all
select 2,'US',80.00,'2008-01-13' union all
select 3,'CT',100.00,'2008-01-18' union all
select 4,'OP',5.00,'2008-01-20' union all
select 5,'XR',66.32,'2008-01-22' union all
select 6,'US',80.00,'2008-02-05' union all
select 7,'CT',110.00,'2008-02-08' union all
select 8,'CT',150.00,'2008-02-15' union all
select 9,'XR',18.91,'2008-03-12' union all
select 10,'OP',3.00,'2008-03-29' union all
select 11,'D',10.00,'2008-03-30'
---查询---
select
convert(char(7),InvoiceDate,120) [Month],
sum(case Account when 'CT' then Amount else 0 end) as [CT],
sum(case Account when 'US' then Amount else 0 end) as [US],
sum(case Account when 'XR' then Amount else 0 end) as [XR],
sum(case Account when 'D' then Amount else 0 end) as [D],
sum(case Account when 'OP' then Amount else 0 end) as [OP],
SUM(Amount) as [Total]
from Invoices
group by convert(char(7),InvoiceDate,120)
---结果---
Month CT US XR D OP Total
------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ----------------------------------------
2008-01 200.00 80.00 66.32 .00 5.00 351.32
2008-02 260.00 80.00 .00 .00 .00 340.00
2008-03 .00 .00 18.91 10.00 3.00 31.91
（所影响的行数为 3 行）
