sql - 对于范围内的每个日期,获取日期范围内的记录
问题描述
假设我有一张桌子Orders
,每个订单都有CreatedDate
一DeliveredDate
列。
如何为范围内的每个日期选择一个计数,该计数包括日期介于CreatedDate
和之间的所有记录DeliveredDate
?假设获得 2021-10-04 到 2021-10-09 范围内的结果
如果Orders
表格看起来像这样
ID CreatedOn DeliveredOn
1 2021-10-04 2021-10-04
2 2021-10-06 2021-10-07
3 2021-10-06 2021-10-08
4 2021-10-07 2021-10-08
5 2021-10-08 2021-10-09
结果应该是这样的
Date ActiveCount
2021-10-04 1
2021-10-05 0
2021-10-06 2
2021-10-07 3
2021-10-08 3
2021-10-09 1
因此,订单 1 仅适用于 2021-10-04 的计数,但订单 3 适用于 2021-10-06、2021-10-07 和 2021-10-08 的计数,因为那是交付日期。
我对 SQL 不是很好,而且我在思考如何做到这一点时遇到了麻烦。如果有人能指出我正确的方向,我将不胜感激!
解决方案
假设您将根据输入参数值在过程中生成日期表,您只需要 aLEFT JOIN
和 a COUNT
with GROUP BY
:
DECLARE @Dates TABLE ([Date] DATE);
DECLARE @Orders TABLE (ID INT, CreatedOn DATE, DeliveredOn DATE);
INSERT @Dates ([Date])
VALUES ('2021-10-04'),('2021-10-05'),('2021-10-06'),
('2021-10-07'),('2021-10-08'),('2021-10-09');
INSERT @Orders (ID, CreatedOn, DeliveredOn)
VALUES (1, '2021-10-04', '2021-10-04'),
(2, '2021-10-06', '2021-10-07'),
(3, '2021-10-06', '2021-10-08'),
(4, '2021-10-07', '2021-10-08'),
(5, '2021-10-08', '2021-10-09');
SELECT d.[Date], COUNT(ID) AS ActiveCount
FROM @Dates d
LEFT JOIN @Orders o ON o.CreatedOn <= d.[Date] AND o.DeliveredOn >= d.[Date]
GROUP BY d.[Date];
表格的顺序在这里很重要。您想查看该范围内的所有日期,因此您从该表开始。这LEFT JOIN
将返回该表中的所有记录,并从 Orders 表中引入 JOIN 条件匹配的记录。您将获得每场比赛的单独记录,因此请使用COUNT
withGROUP BY
来获取每天的总计数。