两张表之间的日期差 [英] Date difference between two tables
问题描述
我有两个带有开始和结束字段的表T1和T2。
我想要的是：T2中不在T1中的部分。
I have two tables T1 and T2 with start and end fields.
What I want is : the parts of T2 that are not in T1.
T1 : [----][----] [-----]
T2 : [---------------] [------------]
R : [-] [--] [--] [---]
R这是结果。
R here is the result.
T1：2015-05-14 07:00:00 2015-05-14 14:00:00
2015-05-14 14 ：00：00 2015-05-14 19:00:00
2015-05-16 12:30:00 2015-05-16 13:30:00
T2：2015-05-14 05:00:00 2015-05-14 23:00:00
2015-05-16 12:00 ：00 2015-05-16 14:00:00
R：2015-05-14 05:00:00 2015-05 -14 07:00:00
2015-05-14 19:00:00 2015-05-14 23:00:00
2015-05-16 12:00:00 2015-05-16 12:30:00
2015-05-16 13:30:00 2015-05-16 14:00:00
我使用SQL Server（2012及更高版本），并且我的字段类型为DateTime2。
我的主要问题是我的工程图中的第一种情况=>当两个或多个间隔被一个覆盖时。
非常感谢您的时间。
I use SQL Server (2012 and more) and the type of my fields are DateTime2.
My main issue here is the first case in my drawing => When you have 2 or more intervals covered by one.
Thanks a lot for your time.
推荐答案
因此，对于那些仅凭图纸不了解问题的人，我将在此处添加带有OP问题的图纸。提供的数据。
So for the guys that didn't understand the problem only by the drawing I will add a drawing here with the OP's problem with the data provided.
这是一种您只有在已经处理过的情况下才能理解的问题。
This is a kind of problem that you only understand if you already dealt with.
dt2 dt3|dt4 dt5 dt8 dt9
T1 : [-----]|[-----] [---------]
dt1 dt6 dt7 dt10
T2 : [--------------------------] [--------------------------]
R1s R1e R2s R2e R3s R3e R4s R4e
R : [----] [-------] [--------] [-------]
标签表示：
dt1 - Date 1
dt2 - Date 2
....
dt10 - Date 10
-------
R1s - Result date start 1
R1e - Result date end 1
R2s - Result date start 2
R2e - Result date end 2
...
它们是日期和时间段。请注意，日期3和日期4之间的 | 只是为了表明此后没有间隔。
They are periods of date and time. Note that the | between Date 3 and 4 is just to show that there is no interval between then.
我的解决方案
对于这种类型的问题，您要做的第一件事就是了解所有期间开始 >和结束作为一个，因此我用它创建了 VIEW ，因为在最终选择中我将不止一次使用它（如果您愿意，您可以不需要创建视图，只需将查询用作子查询即可）
For this type of problem, the very first thing you have to do is to know all your periods starts and ends as one so I created a VIEW with it as I will use it more than once on the final select (if you wish, you don't need to create the view just use the query as subquery)
create or replace view vw_times as
select dtstart as dtperiod from t1 UNION
select dtend from t1 UNION
select dtstart from t2 UNION
select dtend from t2;
此视图仅在一个字段中给出所有日期（开始和结束） dtperiod
This view will give me all dates (starts and ends) in just one field dtperiod
我还需要另一个视图来统一所有开始和从所以 T1 和 T2 结束
I will also need another view to to UNION all starts and ends from both T1 and T2 so
create or replace view vw_times2 as
select dtstart, dtend from t1 UNION
select dtstart, dtend from t2;
因此，最终查询将为：
SELECT t.dtstart, t.dtend
FROM (
SELECT t1.dtperiod as dtstart,
(SELECT min(dtperiod) second
FROM vw_times x where x.dtperiod > t1.dtperiod) as dtend
FROM vw_times t1
LEFT JOIN (SELECT (dtperiod - interval 1 second) dtperiod
FROM vw_times) t2
ON (t1.dtperiod = t2.dtperiod)
WHERE t2.dtperiod is null
) t LEFT JOIN vw_times2 t2 ON ( t.dtstart = t2.dtstart
AND t.dtend = t2.dtend)
WHERE t2.dtstart IS NULL
AND DAY(t.dtstart) = DAY(t.dtend)
ORDER BY t.dtstart;
请记住，我使用的是我在此查询中创建的视图，这样可以提高可读性。
Remember that I'm using the views I created on this query so it can be more readable.
此查询值得一提。由于您只希望得到每天缺少的时间段，因此我添加了此过滤器 AND DAY（t.dtstart）= DAY（t.dtend），因此查询赢得了不会给您几天之间的时间间隔。在您的样本集中，它将是 2015-05-14 23:00:00 2015-05-16 12:00:00 和 2015-05 -16 14:00:00 NULL 上一个是NULL ，这是因为我的查询期间进行了调整。
There's on thing worth to mention on this query. Since you only want as result the missing periods for each day, I've added this filter AND DAY(t.dtstart) = DAY(t.dtend) so the query won't give you missing periods between days. In your sample set it would be 2015-05-14 23:00:00 2015-05-16 12:00:00 and 2015-05-16 14:00:00 NULL last one because of the period tweak on my query.
以下是SQLFiddle上的有效解决方案：< a href = http://sqlfiddle.com/#!9/6a8a9/1 rel = nofollow> http://sqlfiddle.com/#!9/6a8a9/1
Here is the working solution on SQLFiddle: http://sqlfiddle.com/#!9/6a8a9/1
请注意，我使用MySql创建了它（sqlfiddle）（因为它在sqlserver中不稳定）。由于它仅使用普通sql，因此在SQLServer上的工作方式相同（唯一的区别是提取日期。在答案上，我添加了sqlserver版本）。
Note that I created it (sqlfiddle) using MySql (because it is unstable with sqlserver). Since it only use plain sql it will work as the same on SQLServer (the only difference is day extraction. Here on the answer I added the sqlserver version).
这篇关于两张表之间的日期差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
