问题描述
我想在 SELECT 语句中填写缺失的月份,所以我计划将我的表与另一个包含所有月份的表连接起来.如何以轻量级方式生成月份表?例如,
I want to fill in the missing months in a SELECT statement so I was planning on joining my table with another table that contains all months. How can I generate a table of months in a light-weight fashion? For instance,
CREATE TABLE #TEMP(Timewhen DATETIME, Value INT) INSERT INTO #TEMP VALUES('2012-02-04', 4) INSERT INTO #TEMP VALUES('2012-02-06', 4) INSERT INTO #TEMP VALUES('2012-02-10', 4) INSERT INTO #TEMP VALUES('2012-04-08', 4) INSERT INTO #TEMP VALUES('2012-04-12', 4) SELECT YEAR(Timewhen) EventYear, MONTH(Timewhen) EventMonth, SUM(Value) Total FROM #TEMP GROUP BY YEAR(Timewhen), MONTH(Timewhen) DROP TABLE #TEMP
给我:
EventYear EventMonth Total 2012 2 12 2012 4 8
但我需要:
EventYear EventMonth Total 2012 2 12 2012 3 0 2012 4 8
推荐答案
DECLARE @months INT, @firstmonth DATE; SELECT @months = DATEDIFF(MONTH, MIN(Timewhen), MAX(Timewhen)) + 1, @firstmonth = DATEADD(DAY, 1-DAY(MIN(Timewhen)), MIN(Timewhen)) FROM #temp; ;WITH m(rn) AS ( SELECT TOP (@months) rn = ROW_NUMBER() OVER (ORDER BY [object_id]) FROM sys.objects ORDER BY rn ), x(d) AS ( SELECT DATEADD(MONTH, rn-1, @firstmonth) FROM m ) SELECT YEAR(x.d), MONTH(x.d), Total = SUM(COALESCE(t.Value, 0)) FROM x LEFT OUTER JOIN #temp AS t ON t.Timewhen >= x.d AND t.Timewhen < DATEADD(MONTH, 1, x.d) GROUP BY YEAR(x.d), MONTH(x.d);
或者稍微不那么冗长的版本:
Or a slightly less verbose version:
DECLARE @months INT, @firstmonth DATE; SELECT @months = DATEDIFF(MONTH, MIN(Timewhen), MAX(Timewhen)) + 1, @firstmonth = DATEADD(DAY, 1-DAY(MIN(Timewhen)), MIN(Timewhen)) FROM #temp; ;WITH x(y, m, s, e) AS ( SELECT YEAR(dt), MONTH(dt), dt, DATEADD(MONTH, 1, dt) FROM ( SELECT dt = DATEADD(MONTH, rn-1, @firstmonth) FROM ( SELECT TOP (@months) rn = ROW_NUMBER() OVER (ORDER BY [object_id]) FROM sys.objects ORDER BY rn ) AS z ) AS y ) SELECT EventYear = x.y, EventMonth = x.m, Total = SUM(COALESCE(t.Value, 0)) FROM x LEFT OUTER JOIN #temp AS t ON t.Timewhen >= x.s AND t.Timewhen < x.e GROUP BY x.y, x.m;
使用日历表的替代解决方案:
使用 此处.
-- Script to create a calendar table DROP TABLE dbo.Numbers DROP TABLE dbo.Calendar GO -- Use this to determine the number in the next query DECLARE @NUMDAYS int SELECT @NUMDAYS = DATEDIFF(DAY, '20000101', '20291231') CREATE TABLE dbo.Numbers ( Number INT IDENTITY(1,1) PRIMARY KEY CLUSTERED ) WHILE COALESCE(SCOPE_IDENTITY(), 0) <= @NUMDAYS BEGIN INSERT dbo.Numbers DEFAULT VALUES END GO CREATE TABLE dbo.Calendar ( dt **ALLDATETIME NOT NULL PRIMARY KEY CLUSTERED, isWeekday BIT, isHoliday BIT, Y **ALLINT, FY **ALLINT, Q TINYINT, M TINYINT, D TINYINT, DW TINYINT, monthname VARCHAR(9), dayname VARCHAR(9), W TINYINT , HolidayDescription VARCHAR(32) ) GO INSERT Calendar(dt) SELECT DATEADD(DAY, Number, '20000101') FROM dbo.Numbers --WHERE Number <= @NUMDAYS ORDER BY Number GO --SELECT * FROM Calendar UPDATE dbo.Calendar SET isWeekday = CASE WHEN DATEPART(DW, dt) IN (1,7) THEN 0 ELSE 1 END, isHoliday = 0, Y = YEAR(dt), FY = YEAR(dt), /* -- if our fiscal year -- starts on May 1st: FY = CASE WHEN MONTH(dt) < 5 THEN YEAR(dt)-1 ELSE YEAR(dt) END, */ Q = CASE WHEN MONTH(dt) <= 3 THEN 1 WHEN MONTH(dt) <= 6 THEN 2 WHEN MONTH(dt) <= 9 THEN 3 ELSE 4 END, M = MONTH(dt), D = DAY(dt), DW = DATEPART(DW, dt), monthname = DATENAME(MONTH, dt), dayname = DATENAME(DW, dt), W = DATEPART(WK, dt) GO
创建Calendar表后,可以使用以下方法来实现:
After creating a Calendar table, one can use the following to achieve this:
CREATE TABLE #TEMP(Timewhen DATETIME, Value INT) INSERT INTO #TEMP VALUES('2012-02-04', 4) INSERT INTO #TEMP VALUES('2012-02-06', 4) INSERT INTO #TEMP VALUES('2012-02-10', 4) INSERT INTO #TEMP VALUES('2012-04-08', 4) INSERT INTO #TEMP VALUES('2012-04-12', 4) SELECT Y EventYear, M EventMonth, SUM(Value) Total FROM #TEMP RIGHT OUTER JOIN (SELECT DISTINCT Y,M FROM dbo.dateRange('20120204', '20120412')) X ON YEAR(Timewhen) = X.Y AND MONTH(Timewhen) = X.M GROUP BY Y,M DROP TABLE #TEMP