TSQL 循环月按顺序

2021-09-10 00:00:00 tsql sql-server

我有一个问题，但我对此感到无法理解.

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]

相关文章