查詢以查找可用時隙

我有一張叫做約會的桌子和一張叫做輪班的桌子。在約會表中，有 shift_id，所以它看起來像這樣：

id | start_date | end_date | shift_id ( FK )

一個示例班次是 09:00 到 17:00，其中包含幾個約會：

09:30 - 10:10 | 11:30 - 11:50 | 13:00 - 14:00

我目前正在使用此查詢來獲取可用的時間段，但它有一個邊緣情況問題。它找不到從 9 到 9:30 的開始。

SELECT Available_from, Available_to
        FROM (
        SELECT @lasttime_to AS Available_from, start_date AS Available_to, @lasttime_to := end_date
        FROM (SELECT start_date, end_date
               FROM appointments
                WHERE shift_id = " . $shiftData->id . "
                AND end_date <= '" . $shiftData->end_date->toDateTimeString() . "'
                AND start_date >= '" . $shiftData->start_date->toDateTimeString() . "'
               UNION ALL
             SELECT '". $shiftData->start_date->toDateTimeString() ."', '". $shiftData->start_date->toDateTimeString() ."'
                   ORDER BY end_date 
        
             ) e
        JOIN (SELECT @lasttime_to := NULL) init) x
        WHERE Available_to > DATE_ADD(Available_from, INTERVAL " . $serviceDurations . " MINUTE)

這缺少什么？我怎樣才能讓開始出現在查詢中？

http://sqlfiddle.com/#!9/e5292d/2

uj5u.com熱心網友回復：

首先，解決您的原始 SQL（在小提琴中）。

對于第一行，@lasttime_to為 NULL 并導致問題。

需要對空情況（第一行）有條件地使用“班次開始時間”。

另LAG一種方法見。這確實是@variable黑客試圖取代的東西，之前LAG在 MySQL 中可用。

嘗試這個：

SELECT Available_from, Available_to
  FROM (
    SELECT COALESCE(@lasttime_to, '2022-03-15 10:00:00') AS Available_from, start_date AS Available_to, @lasttime_to := end_date
    FROM (SELECT start_date, end_date
             FROM appointments
             WHERE shift_id = 4600
             AND end_date <= '2022-03-15 17:00:00'
             AND start_date >= '2022-03-15 10:00:00'
             UNION ALL
               SELECT '2022-03-15 17:00:00', '2022-03-15 17:00:00'
               ORDER BY start_date 
           ) e
    JOIN (SELECT @lasttime_to := NULL) init) x
    WHERE Available_to > DATE_ADD(Available_from, INTERVAL 20 MINUTE)
;

結果：

這里也有一些帶有視窗功能的東西。我重新安排了邏輯以避免與您的班次開始/結束細節相關的多個魔術常數。然而，我并沒有消除所有的魔法。

我仍然更喜歡我洗掉的更動態的方法。

WITH shift (shift_start, shift_end) AS (
       SELECT '2022-03-15 10:00:00', '2022-03-15 17:00:00'
     )
   , e0 AS (
       SELECT shift_id, start_date, end_date
         FROM appointments
        WHERE shift_id = 4600
          AND end_date   <= (SELECT shift_end   FROM shift)
          AND start_date >= (SELECT shift_start FROM shift)
        UNION ALL
       SELECT 4600, shift_end, shift_end FROM shift
        ORDER BY start_date 
     )
   , e AS (
      SELECT e0.*
           , LAG(end_date) OVER (PARTITION BY shift_id ORDER BY start_date) AS lasttime_to
        FROM e0
     )
SELECT shift_id, Available_from, Available_to
  FROM (
       SELECT shift_id
            , CAST(COALESCE(lasttime_to, shift_start) AS datetime) AS Available_from
            , start_date AS Available_to
         FROM e
         CROSS JOIN shift
      ) x
 WHERE Available_to > DATE_ADD(Available_from, INTERVAL 20 MINUTE)
;

版本略有不同：

WITH e0 AS (
       SELECT a.shift_id, a.start_date, a.end_date
            , s.start_date AS shift_start
            , s.end_date   AS shift_end
         FROM appointments AS a
         JOIN shifts       AS s
           ON a.shift_id = 4600
          AND a.shift_id = s.id
          AND a.end_date   <= s.end_date
          AND a.start_date >= s.start_date
        UNION
       SELECT id, end_date, end_date
            , start_date  , end_date
         FROM shifts WHERE id = 4600
     )
   , e AS (
      SELECT e0.*
           , LAG(end_date) OVER (PARTITION BY shift_id ORDER BY start_date) AS lasttime_to
        FROM e0
     )
SELECT shift_id, Available_from, Available_to
  FROM (
       SELECT shift_id
            , CAST(COALESCE(lasttime_to, shift_start) AS datetime) AS Available_from
            , start_date AS Available_to
         FROM e
      ) x
 WHERE Available_to > DATE_ADD(Available_from, INTERVAL 20 MINUTE)
;

小提琴

標籤：mysql sql

上一篇：sqlQuery，其中時間戳是特定日期

下一篇：合并來自ORDERBY的列并用連字符保持分散，并且僅在每個組的末尾顯示一次用于ORDERBY的欄位