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)

（编辑：湖南网）

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