sql-server – 如何创建一系列连接稀疏数据的月份？

我认为这是一个非常常见的问题,但我不知道这个过程是什么,所以我将用一个例子来描述它.这个概念是我想将稀疏数据集加入到完整的系列中,例如一周中的几天,一年中的几个月或任何有序集(例如,排名).稀疏数据中的空位置将在完整系列旁边显示为NULL.

假设我在SQL Server中运行以下查询以查找月度销售额.

SELECT
    YEAR([timestamp]),MONTH([timestamp]),COUNT(*)
FROM table1
WHERE YEAR([timestamp]) = YEAR(GETDATE())
GROUP BY
    YEAR([timestamp]),MONTH([timestamp])
ORDER BY
    YEAR([timestamp]) DESC,MONTH([timestamp]) DESC;

但是,如果销售仅发生在今年5月和8月,那么返回结果将如下所示：

2010    August    1234
2010    May       5678

我希望我的返回结果集看起来像这样：

2010    January
2010    February
2010    March
2010    April
2010    May        1234
2010    June
2010    July
2010    August     5678
2010    September
2010    October
2010    November
2010    December

我知道这样做的唯一方法是：

SELECT
    YEAR(GETDATE()),month_index.month_name,sales_data.sales
FROM (
    SELECT 'January' as month_name,1 as month_number
    UNION
    SELECT 'February',2
    UNION
    SELECT 'March',3
    UNION
    SELECT 'April',4
    UNION
    SELECT 'May',5
    UNION
    SELECT 'June',6
    UNION
    SELECT 'July',7
    UNION
    SELECT 'August',8
    UNION
    SELECT 'September',9
    UNION
    SELECT 'October',10
    UNION
    SELECT 'November',11
    UNION
    SELECT 'December',12
) as month_index
LEFT JOIN (
    SELECT
        YEAR([timestamp]) AS year_name,MONTH([timestamp]) AS month_name,COUNT(*) AS sales
    FROM table1
    WHERE YEAR([timestamp]) = GETDATE()
    GROUP BY
        YEAR([timestamp]),MONTH([timestamp])
) AS sales_data
ON month_index.month_name = sales_data.month_name
ORDER BY
    month_index.month_number DESC;

有没有更好的方法来创建完整的日期和字母数字系列来连接数据？这叫什么？

谢谢！

解决方法

DECLARE @StartDate datetime,@EndDate datetime
SELECT @StartDate=DATEADD(month,-6,DATEADD(month,DATEDIFF(month,GETDATE()),0) ),@EndDate=GETDATE()

;with AllDates AS
(
    SELECT @StartDate AS DateOf
    UNION ALL
    SELECT DateAdd(month,1,DateOf)
        FROM AllDates
    WHERE DateOf<@EndDate
)
SELECT * FROM AllDates

输出：

DateOf
-----------------------
2009-12-01 00:00:00.000
2010-01-01 00:00:00.000
2010-02-01 00:00:00.000
2010-03-01 00:00:00.000
2010-04-01 00:00:00.000
2010-05-01 00:00:00.000
2010-06-01 00:00:00.000
2010-07-01 00:00:00.000

(8 row(s) affected)

（编辑：上海站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!