TSQL loop months in sequence(TSQL 循环月按顺序)
问题描述
我有一个问题,但我对此感到无法理解.
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 循环月按顺序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!
本文标题为:TSQL 循环月按顺序
基础教程推荐
- 无法在 ubuntu 中启动 mysql 服务器 2021-01-01
- 使用pyodbc“不安全"的Python多处理和数据库访问? 2022-01-01
- SQL Server 中单行 MERGE/upsert 的语法 2021-01-01
- SQL Server:只有 GROUP BY 中的最后一个条目 2021-01-01
- Sql Server 字符串到日期的转换 2021-01-01
- SQL Server 2016更改对象所有者 2022-01-01
- 在 VB.NET 中更新 SQL Server DateTime 列 2021-01-01
- 将数据从 MS SQL 迁移到 PostgreSQL? 2022-01-01
- 如何在 SQL Server 的嵌套过程中处理事务? 2021-01-01
- ERROR 2006 (HY000): MySQL 服务器已经消失 2021-01-01