时间戳

首页 > 解决方案 > 使用日期 startdatetime 和 end datetime 的 SQL 查询组

sql - 使用日期 startdatetime 和 end datetime 的 SQL 查询组

问题描述

我有下表Jobs：

|Id  | StartDateTime       | EndDateTime
+----+---------------------+----------------------
|1   | 2020-10-20 23:00:00 | 2020-10-21 05:00:00
|2   | 2020-10-21 10:00:00 | 2020-10-21 11:00:00

请注意，作业 ID 1 跨越 10 月 20 日至 21 日。

我正在使用以下查询

SELECT DAY(StartDateTime), COUNT(id)
FROM Job
GROUP BY DAY(StartDateTime)

得到以下输出。但我面临的问题是第 21 天不包括作业 ID 1。由于作业跨越两天，我想在第 20 天和第 21 天都包含它。

Day | TotalJobs
----+----------
20  | 1
21  | 1

我正在努力获得以下预期输出：

Day | TotalJobs
----+----------
20  | 1
21  | 2

标签： sqlsql-servertsql

解决方案

一种方法是生成您想要的天数，然后计算重叠：

with days as (
      select convert(date, min(j.startdatetime)) as startd,
             convert(date, max(j.enddatetime)) as endd
      from jobs j
      union all
      select dateadd(day, 1, startd), endd
      from days
      where startd < endd
     )
select days.startd, count(j.id)
from days left join
     jobs j
     on j.startdatetime < dateadd(day, 1, startd) and
        j.enddatetime >= startd
group by days.startd;

这是一个 db<>fiddle。

推荐阅读