SQL Server-检索两列之间的月份日期列表

最新推荐文章于 2024-08-08 08:00:00 发布

原创最新推荐文章于 2024-08-08 08:00:00 发布 · 394 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #sql #mysql

文章描述了如何使用SQL查询，特别是通过交叉联接和DATEADD函数，在给定的开始和结束日期范围内生成详细的日期列表，示例中涉及`tmp`表和两个CASE-NAME组合：ABC和DEF。

在日期范围之间生成日期）使用交叉联接将tmp表与日期生成器代码组合

declare @tmp as table ( [Case] int, [Name] varchar(20), [StartDate] date, [EndDate] date)

insert into @tmp
values(1, 'ABC', '2021-01-15', '2021-03-15')
,(2, 'DEF' ,'2021-03-15', '2021-05-15')

SELECT
tmp.[Case],
tmp.[Name],
DATEADD(DAY, Nbr - 1, tmp.StartDate) Detail_Date
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY c.object_id ) AS Nbr
FROM sys.columns c
) nbrs
cross join
(
select [Name],[Case],[StartDate],[EndDate] from @tmp
)tmp
WHERE Nbr - 1 <= DATEDIFF(DAY, tmp.StartDate, tmp.EndDate)
order by
tmp.[Case],
tmp.[Name],
Detail_Date

-----------------------------

Case Name Detail_Date
1 ABC 2021-01-15
1 ABC 2021-01-16
1 ABC 2021-01-17
1 ABC 2021-01-18
1 ABC 2021-01-19
1 ABC 2021-01-20
1 ABC 2021-01-21
1 ABC 2021-01-22
1 ABC 2021-01-23
1 ABC 2021-01-24
1 ABC 2021-01-25
1 ABC 2021-01-26
1 ABC 2021-01-27
1 ABC 2021-01-28
1 ABC 2021-01-29
1 ABC 2021-01-30
1 ABC 2021-01-31
1 ABC 2021-02-01
1 ABC 2021-02-02
1 ABC 2021-02-03
1 ABC 2021-02-04
1 ABC 2021-02-05
1 ABC 2021-02-06
1 ABC 2021-02-07
1 ABC 2021-02-08
1 ABC 2021-02-09
1 ABC 2021-02-10
1 ABC 2021-02-11
1 ABC 2021-02-12
1 ABC 2021-02-13
1 ABC 2021-02-14
1 ABC 2021-02-15
1 ABC 2021-02-16
1 ABC 2021-02-17
1 ABC 2021-02-18
1 ABC 2021-02-19
1 ABC 2021-02-20
1 ABC 2021-02-21
1 ABC 2021-02-22
1 ABC 2021-02-23
1 ABC 2021-02-24
1 ABC 2021-02-25
1 ABC 2021-02-26
1 ABC 2021-02-27
1 ABC 2021-02-28
1 ABC 2021-03-01
1 ABC 2021-03-02
1 ABC 2021-03-03
1 ABC 2021-03-04
1 ABC 2021-03-05
1 ABC 2021-03-06
1 ABC 2021-03-07
1 ABC 2021-03-08
1 ABC 2021-03-09
1 ABC 2021-03-10
1 ABC 2021-03-11
1 ABC 2021-03-12
1 ABC 2021-03-13
1 ABC 2021-03-14
1 ABC 2021-03-15
2 DEF 2021-03-15
2 DEF 2021-03-16
2 DEF 2021-03-17
2 DEF 2021-03-18
2 DEF 2021-03-19
2 DEF 2021-03-20
2 DEF 2021-03-21
2 DEF 2021-03-22
2 DEF 2021-03-23
2 DEF 2021-03-24
2 DEF 2021-03-25
2 DEF 2021-03-26
2 DEF 2021-03-27
2 DEF 2021-03-28
2 DEF 2021-03-29
2 DEF 2021-03-30
2 DEF 2021-03-31
2 DEF 2021-04-01
2 DEF 2021-04-02
2 DEF 2021-04-03
2 DEF 2021-04-04
2 DEF 2021-04-05
2 DEF 2021-04-06
2 DEF 2021-04-07
2 DEF 2021-04-08
2 DEF 2021-04-09
2 DEF 2021-04-10
2 DEF 2021-04-11
2 DEF 2021-04-12
2 DEF 2021-04-13
2 DEF 2021-04-14
2 DEF 2021-04-15
2 DEF 2021-04-16
2 DEF 2021-04-17
2 DEF 2021-04-18
2 DEF 2021-04-19
2 DEF 2021-04-20
2 DEF 2021-04-21
2 DEF 2021-04-22
2 DEF 2021-04-23
2 DEF 2021-04-24
2 DEF 2021-04-25
2 DEF 2021-04-26
2 DEF 2021-04-27
2 DEF 2021-04-28
2 DEF 2021-04-29
2 DEF 2021-04-30
2 DEF 2021-05-01
2 DEF 2021-05-02
2 DEF 2021-05-03
2 DEF 2021-05-04
2 DEF 2021-05-05
2 DEF 2021-05-06
2 DEF 2021-05-07
2 DEF 2021-05-08
2 DEF 2021-05-09
2 DEF 2021-05-10
2 DEF 2021-05-11
2 DEF 2021-05-12
2 DEF 2021-05-13
2 DEF 2021-05-14
2 DEF 2021-05-15