sql - SQL在给定开始日期和年数的情况下获取每一年的开始日期和结束日期
问题描述
我在 SQL 表中有以下数据:
+------------------------------------+
| ID YEARS START_DATE |
+------------------------------------+
| ----------- ----------- ---------- |
| 1 5 2020-12-01 |
| 2 8 2020-12-01 |
+------------------------------------+
尝试创建一个 SQL 来扩展上述数据,并根据上表中的 YEARS 和 START_DATE 为我提供每年的开始和结束日期。下面的示例输出:
+-----------------------------------------------+
| ID YEAR DATE_START DATE_END |
+-----------------------------------------------+
| ----------- ----------- ---------- ---------- |
| 1 1 2020-12-01 2021-11-30 |
| 1 2 2021-12-01 2022-11-30 |
| 1 3 2022-12-01 2023-11-30 |
| 1 4 2023-12-01 2024-11-30 |
| 1 5 2024-12-01 2025-11-30 |
| 2 1 2020-12-01 2021-11-30 |
| 2 2 2021-12-01 2022-11-30 |
| 2 3 2022-12-01 2023-11-30 |
| 2 4 2023-12-01 2024-11-30 |
| 2 5 2024-12-01 2025-11-30 |
| 2 6 2025-12-01 2026-11-30 |
| 2 7 2026-12-01 2027-11-30 |
| 2 8 2027-12-01 2028-11-30 |
+-----------------------------------------------+
解决方案
我会为此使用内联计数,因为它们比递归 CTE 解决方案快得多。假设您的值较低Years
:
WITH YourTable AS(
SELECT *
FROM (VALUES(1,5,CONVERT(date,'20201201')),
(2,8,CONVERT(date,'20201201')))V(ID,Years, StartDate))
SELECT ID,
V.I + 1 AS [Year],
DATEADD(YEAR, V.I, YT.StartDate) AS StartDate,
DATEADD(DAY, -1, DATEADD(YEAR, V.I+1, YT.StartDate)) AS EndDate
FROM YourTable YT
JOIN (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))V(I) ON YT.Years > V.I;
如果您有超过 10 年的时间,您可以使用创建一个计数表,或者在 CTE 中创建一个大的内联表。这将开始为:
WITH N AS(
SELECT N
FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 AS I --remove the -1 if you don't want to start from 0
FROM N N1, N N2) --100 rows, add more Ns for more rows
...
当然,我怀疑你有 1000 年的数据。
推荐阅读
- sql - EF Core 为嵌套数据生成过多查询
- rx-java - 创建的 Observable 的 onNext 不起作用
- java - 405 方法不允许,尽管在使用 RepositoryRestConfiguration.disableDefaultExposure() 时公开了所有方法
- php - Laravel 5.6:Auth::check() 在成功 Auth::login 后直接失败
- regex - 在 python 中,如何将正则表达式匹配类型(sre.SRE_Match)转换为 FLOATS?
- php - 使用数组作为函数参数的最佳实践是什么?
- java - Thymeleaf 中嵌套列表的表单绑定
- azure - 如何在数据工厂 V2 链接服务中连接现有网关
- r - 是否有任何类似的方法来对 Shiny 中的 excel 中的多列进行条件格式化
- javascript - 下拉菜单不适用于移动浏览器