我想为下面的问题编写一个查询。
我想要重叠日期范围和相同开始&SQL Server 中不重叠日期范围的结束日期时间
示例:-My_table
NAME | 开始日期时间 | 结束日期时间|
---|---|---|
Aman | 2020年2月1日04:30 | 020年2月01日06:30 |
Ram | 2020年2月1日04:40 | 020年2月01日05:30 |
Ram | 2020年2月1日04:40 | 020年2月01日05:30 |
这是一个典型的间隙&岛屿问题,如果你环顾四周,你会发现很多答案。
下面是一个";老派;方法(性能最低,因为表被多次访问(,这更容易遵循(它假设enddatetime是包容性的:如果enddatetime=与startdatetime,则存在重叠(:
declare @t table(name varchar(20), startdatetime datetime, enddatetime datetime);
insert into @t(name, startdatetime, enddatetime)
values
('Aman', '20200102 08:30', '20200102 09:30'),
('Aman', '20200102 04:40', '20200102 05:30'),
('Aman', '20200102 04:55', '20200102 07:30'),
('Ram', '20200102 04:40', '20200102 05:30'),
('Ram', '20200102 04:40', '20200102 05:30'),
--
('x', '20200102 02:40', '20200102 03:30'),
('x', '20200102 03:30', '20200102 04:55'),
('x', '20200102 04:20', '20200102 05:35'),
('x', '20200102 05:35', '20200102 06:42'),
('x', '20200102 06:00', '20200102 08:15');
select s.name, s.startdatetime, e.enddatetime
from
(
--starttimes which do not overlap start an island
select a.name, a.startdatetime, row_number() over(partition by a.name order by a.startdatetime) as rownum
from @t as a
where not exists (select * from @t as b where b.name = a.name and b.startdatetime < a.startdatetime and b.enddatetime >= a.startdatetime)
group by a.name, a.startdatetime
) as s --start datetimes
join
(
--endtimes which do not overlap, end an island
select a.name, a.enddatetime, row_number() over(partition by a.name order by a.enddatetime) as rownum
from @t as a
where not exists (select * from @t as b where b.name = a.name and b.startdatetime <= a.enddatetime and b.enddatetime > a.enddatetime)
group by a.name, a.enddatetime
) as e --end datetimes
on s.name = e.name and s.rownum = e.rownum;
为了解决您的问题,我做了以下操作:
CREATE TABLE test
(
name VARCHAR (20),
start_date_time DATETIME,
end_date_time DATETIME
);
然后运行:
INSERT INTO TEST VALUES
('Aman', convert(datetime, '01-02-2020 08:30', 120), convert(datetime, '01-02-2020 09:30', 120)),
('Aman', convert(datetime, '01-02-2020 04:40', 120), convert(datetime, '01-02-2020 05:30', 120)),
('Aman', convert(datetime, '01-02-2020 04:55', 120), convert(datetime, '01-02-2020 07:30', 120)),
('Ram', convert(datetime, '01-02-2020 04:40', 120), convert(datetime, '01-02-2020 05:30', 120)),
('Ram', convert(datetime, '01-02-2020 04:40', 120), convert(datetime, '01-02-2020 05:30', 120));
然后我运行了查询:
SELECT
name,
MIN(start_date_time) AS "Min dt",
MAX(end_date_time) AS "Max dt"
FROM test
GROUP BY name;
结果:
name Min dt Max dt
Aman 2020-01-02 04:40:00.000 2020-01-02 09:30:00.000
Ram 2020-01-02 04:40:00.000 2020-01-02 05:30:00.000
看这里的小提琴。将来,你能用这个网站制作你自己的小提琴吗。这有助于消除那些试图帮助你的人的重复工作,也消除了出错的可能性!我做了一些额外的测试——看看小提琴!