合并时间按段sql

2013/12/31 20:42
阅读数 518

假设有一张考勤单,经常需要将连续或重叠的时间段合并。在做简单查询时这可能成为问题,所以要当心,这个问题并不容易领会或理解:

创建示例表与数据:

CREATE TABLE Timesheets (
   task_id CHAR(10) NOT NULL PRIMARY KEY, 
   start_date DATE NOT NULL, 
   end_date DATE NOT NULL, 
   CHECK(start_date <= end_date)
);
NSERT INTO Timesheets 
VALUES (1,  '1997-01-01', '1997-01-03'), 
       (2,  '1997-01-02', '1997-01-04'), 
       (3,  '1997-01-04', '1997-01-05'), 
       (4,  '1997-01-06', '1997-01-09'), 
       (5,  '1997-01-09', '1997-01-09'), 
       (6,  '1997-01-09', '1997-01-09'), 
       (7,  '1997-01-12', '1997-01-15'), 
       (8,  '1997-01-13', '1997-01-14'), 
       (9,  '1997-01-14', '1997-01-14'), 
       (10, '1997-01-17', '1997-01-17');

方案一:(总耗时  : 0.004 sec)

SELECT 
  T1.start_date,
  MAX(T2.end_date) 
FROM
  Timesheets AS T1,
  Timesheets AS T2 
WHERE T1.start_date <= T2.end_date 
  AND NOT EXISTS 
  (SELECT 
    * 
  FROM
    Timesheets AS T3,
    Timesheets AS T4 
  WHERE T3.end_date < T4.start_date 
    AND T3.start_date >= T1.start_date 
    AND T3.end_date <= T2.end_date 
    AND T4.start_date >= T1.start_date 
    AND T4.end_date <= T2.end_date 
    AND NOT EXISTS 
    (SELECT 
      * 
    FROM
      Timesheets AS T5 
    WHERE T5.start_date BETWEEN T3.start_date 
      AND T3.end_date 
      AND T5.end_date BETWEEN T4.start_date 
      AND T4.end_date)) 
GROUP BY T1.start_date 
HAVING T1.start_date = MIN(t2.start_date) ;

方案二:(总耗时 : 0.002 sec)

SELECT 
  X.start_date,
  MIN(Y.end_date) AS end_date 
FROM
  (SELECT 
    T1.start_date 
  FROM
    Timesheets AS T1 
    LEFT OUTER JOIN Timesheets AS T2 
      ON T1.start_date > T2.start_date 
      AND T1.start_date <= T2.end_date 
  GROUP BY T1.start_date 
  HAVING COUNT(T2.start_date) = 0) AS X
  INNER JOIN 
    (SELECT 
      T3.end_date 
    FROM
      Timesheets AS T3 
      LEFT OUTER JOIN Timesheets AS T4 
        ON T3.end_date >= T4.start_date 
        AND T3.end_date < T4.end_date 
    GROUP BY T3.end_date 
    HAVING COUNT(T4.start_date) = 0) AS Y
    ON X.start_date <= Y.end_date 
GROUP BY X.start_date ;

方案三:(总耗时 : 0.002 sec)

SELECT 
  X.start_date,
  MIN(X.end_date) AS end_date 
FROM
  (SELECT 
    T1.start_date,
    T2.end_date 
  FROM
    Timesheets AS T1,
    Timesheets AS T2,
    Timesheets AS T3 
  WHERE T1.end_date <= T2.end_date 
  GROUP BY T1.start_date,
    T2.end_date 
  HAVING MAX(
      CASE
        WHEN (
          T1.start_date > T3.start_date 
          AND T1.start_date <= T3.end_date
        ) 
        OR (
          T2.end_date >= T3.start_date 
          AND T2.end_date < T3.end_date
        ) 
        THEN 1 
        ELSE 0 
      END
    ) = 0) AS X
GROUP BY X.start_date ;

结果:

start_date    end_date 

======================== 
1997-01-01   1997-01-05 
1997-01-06   1997-01-09 
1997-01-12   1997-01-15 
1997-01-17   1997-01-17






展开阅读全文
加载中
点击引领话题📣 发布并加入讨论🔥
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部