与大家探讨一个根据自然月分组统计的SQL语句

---测试数据---
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 行）