按月和年分组的 SQL 查询

Question

我有一张 table ，叫 history (日期为 DD-MM-YYYY):

====================
| Buy        | Qty |
====================
| 01-01-2012 |  1  |
| 01-01-2012 |  1  |
| 01-02-2012 |  1  |
| 01-03-2012 |  1  |
| 01-05-2012 |  1  |
| 01-07-2012 |  1  |
| 01-12-2012 |  1  |
====================

注意:在第 4、6、8、9、10、11 个月没有购买。

如果我运行:

SELECT MONTH(buy) AS day, YEAR(buy) as year, SUM(qty)
FROM history
GROUP BY MONTH(buy),YEAR(buy)

我得到这个结果:

======================
| Month | Year | Qty |
======================
| 01    | 2012 |  2  |
| 02    | 2012 |  1  |
| 03    | 2012 |  1  |
| 05    | 2012 |  1  |
| 07    | 2012 |  1  |
| 12    | 2012 |  1  |
======================

我也想显示第 4、6、8、9、10、11 个月，但有 Qty 0(零)，像这样:

======================
| Month | Year | Qty |
======================
| 01    | 2012 |  2  |
| 02    | 2012 |  1  |
| 03    | 2012 |  1  |
| 04    | 2012 |  0  |
| 05    | 2012 |  1  |
| 06    | 2012 |  0  |
| 07    | 2012 |  1  |
| 08    | 2012 |  0  |
| 09    | 2012 |  0  |
| 10    | 2012 |  0  |
| 11    | 2012 |  0  |
| 12    | 2012 |  1  |
======================

我该怎么做？

Answer 1

试试这个:

Declare @Sample table 
(Buy datetime ,Qty int)

Insert into @Sample values
( '01-01-2012' ,1),
('01-01-2012',1 ),
('01-02-2012',1 ),
('01-03-2012',1 ),
('01-05-2012',1 ),
('01-07-2012',1 ),
('01-12-2012',1 )

;with cte as 
(
  select top 12 row_number() over(order by t1.number) as N
  from   master..spt_values t1 
   cross join master..spt_values t2
 )
select t.N as month,
isnull(datepart(year,y.buy),'2012') as Year,
sum(isnull(y.qty,0)) as Quantity
from cte t
left join @Sample y
on month(convert(varchar(20),buy,103)) = t.N
group by y.buy,t.N

创建一个 Month 表来存储从 1 到 12 的值。你也可以使用 sys.all_objects 代替 master..spt_values

  select row_number() over (order by object_id) as months
  from sys.all_objects  

或使用递归 cte 生成月份表

;with cte(N) as 
(
Select 1 
union all
Select 1+N from cte where N<12
)
Select * from cte

然后使用 Left join 将月表中的值与您的表进行比较并使用 isnull函数来处理空值。