Mysql 查询以查找可用时隙

首先，解决你的原始SQL(在小提琴中).

对于第一行，@lasttime_to为空，会导致问题.

对于空情况(第一行)，需要有条件地使用"移位开始时间".

另一种方法见LAG.这正是@variable黑客试图取代的，在MySQL中提供LAG之前.

试试这个:

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)
;

The fiddle