問題描述

我有一個問題，但我對此感到無法理解.

I have an query that I'm feeling out-of-my depth with.

我需要遍歷兩個日期之間的月份，并為每個月返回一個數據子集，并為沒有數據的月份返回一個空白行.

I need to loop through months between two dates and return a subset of data for each month with a blank row for months with no data.

例如:

TransactionID    |    Date          |    Value
1                |    01/01/2015    |    ￡10
2                |    16/01/2015    |    ￡15
3                |    21/01/2015    |    ￡5
4                |    15/03/2015    |    ￡20
5                |    12/03/2015    |    ￡15
6                |    23/04/2015    |    ￡10

需要返回:

Month            |    Amount
January          |    ￡30
February         |    ￡0
March            |    ￡35
April            |    ￡10

我的查詢將依賴于指定日期范圍，以便我可以設置查詢的第一個和最后一個日期.

My query will rely on specifying a date range so I can set the first and last date of the query.

我覺得我可能想多了，但已經到了那個階段，你開始覺得自己束手無策.

I feel like I maybe over thinking this, but have gotten to that stage where you start to feel like you tying yourself in knots.

推薦答案

關鍵是可以訪問一個整數列表來表示該范圍內的月份.如果您沒有 Numbers Table，則 spt_values 將在一點.

The key is having access to a list of integers to represent the months in the range. If you don't have a Numbers Table, then spt_values will do in a pinch.

SqlFiddle 演示

SELECT
  [Year]   = YEAR(DATEADD(month,[i],@range_start))
 ,[Month]  = DATENAME(month,DATEADD(month,[i],@range_start))
 ,[Amount] = ISNULL(SUM([Value]),0)
FROM (
  SELECT TOP (DATEDIFF(month,@range_start,@range_end)+1)
    ROW_NUMBER() OVER(ORDER BY (SELECT 1))-1 [i]
  FROM master.dbo.spt_values
) t1
LEFT JOIN #MyTable t2
  ON (t1.[i] = DATEDIFF(month,@range_start,t2.[Date]) )
GROUP BY [i]
ORDER BY [i]

這篇關于TSQL 循環月按順序的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！