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