根据自然月分组统计的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 行）

 

 

 

转载于:https://www.cnblogs.com/Sue_/articles/1663612.html