我有一个如下表:
Name Start Date End Date
Joe 20/04/2021 20/05/2021
John 01/05/2021 28/05/2021
我正在使用 SQL 表值函数来返回一个包含 2 列的表:月份和计数本月的总日期。
例子:
- 乔:4月10天,5月20天
- 约翰:5 月 28 天
最后我会返回一个新表
月 | 数数 |
---|---|
4 | 10 |
5 | 48 |
我尝试使用 datediff 和 datepart 按月分组,但不知道如何在分组后求和。有没有办法做到这一点?
此外,我想从日期和日期添加过滤器。
另一种方法:
解决方案:
输出:
在线演示:
db<>小提琴演示
https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=c2d6d92223f6516d78550a021cd5c3ce
该查询假定所有期间都属于同一年。当然也可以简化。从“2021-04-20”到“2021-04-30”(含)的期间长度为 11 天,而不是 10 天。
这仅影响
cte1
(不是根据表数据而是根据所需的期间日期生成日历),并且如果您想部分过滤某个月份(不是从一个月的第一天到最后一天)在cte2
. 由于ON
子句表达式,主查询将被自动过滤。TL;博士
我添加了前一年的一些样本数据(见小提琴),并包括跨过除夕的一个时期。我使用的方法假设存在日历表 - 下面讨论使用递归 CTE 执行此操作的方法。通过添加
WHERE
提供的条款(注释掉),可以限制日期范围和/或在给定月份是否有任何停留。结果(参见小提琴中的额外数据- 这个结果似乎是一致的):
更详细的讨论:
对于已接受的答案,有两种或三种替代方法可能会起作用。这是需要日历表的经典案例。有两种方法可以做到这一点 - 第一种是使用“经典” SQL - 使用函数 - 以及使用更现代的
CTE
(通用表表达式)方法,特别是递归 CTE(参见上面参考中的链接)。我遇到的第一个宝石是Jon Tavernier 的这篇关于如何在 SQL Server 中创建(物化)日历表的文章 - 我将它缩减到这个问题所需的最低限度,尽管这种表有很多潜在用途,你不妨考虑在 10-20 年期间(仅约 3.5K - 7K 条记录)和更多字段(例如每月的第一天、会计期结束...)进行此操作。
只是一小点-您的表定义似乎被简化了,但我建议您将其设置为更像这样-增加信息量,服务器使其更容易提出一个好的计划:
您可能还想确保给定
name
和它们start_date
之间没有重叠end_date
- 但我不知道如何在 SQL Server 中执行此操作,除非使用触发器 - 我的 UNIQUE 约束是通过 SQL 进行的尝试 - 但它们不会阻止嵌套间隔...这是一篇关于如何使用触发器进行操作的文章。
第一种方法(日历表 - Tavernier):
方法 1 的代码可在此处的小提琴中找到。
然后我检查:
结果(为简洁起见):
stay
接下来,我们根据 OP 的问题(我称之为 table )设置我们的测试数据:然后,我运行了这个查询:
结果:
月份名称未包含在规范中,并且可以很容易地删除 - 只是觉得它很高兴。
然后我
turned on Statistics
如下:并重新运行查询 - 结果如下所述。
第二种方法(通过 CTE 使用预定义日期的日历年表):
这种方法的代码可在 fiddle here上找到。
有时不允许顾问创建物理表,而我想要自然的方法是使用 CTE 方法,我偶然发现
Creating a date dimension or calendar table in SQL Server
了 Aaron Bertand(本教区)的下一个宝石 ( )。他以这些圣言开始他的作品:
他接着说:
因此,即使是第一种方法中的函数也可能是合适的——但是,
CTE
在 Tavernier 的上述文章之后,尽我所能确定,'s(通用表表达式 - 也请参见内部 RECURSIVE CTE 的链接)被引入 SQL Server。我改编了 Bertrand 的第二个 CTE 中的代码,如下所示:
结果(为简洁起见):
所以,就我们的日历表而言,我们是金色的......
然后,我将其放在方法 1 中的原始查询上,如下所示:
结果(相同):
所以,我做了与第一种方法相同的方法并打开了统计 - 下面讨论。
第三种方法(限制日期):
小提琴在这里可用。
在这里,为了“效率”,我将日历表中的日期限制
MIN()
为 start_time 和表MAX()
中end_time
的stay
,如下所示:结果(为简洁起见):
所以,现在我们有了最早
start_date
和最晚之间的每个日期end_date
。并使用这些日期作为我们的日历表,我们制定了查询:
结果(相同):
我在小提琴结束时有一些性能统计数据。当然,很难从无法控制的服务器上(相对)少量数据的性能分析中得出许多结论 - 我会敦促您使用自己的数据并在自己的负载下进行测试。 .