SQL 从预订表中动态生成空闲插槽? [英] SQL generate free slots on the fly from bookings table?

问题描述

我知道 php 部分有很多预订问题.当我告诉你我尝试了其中的大部分时，请相信我，至少那些看起来兼容的.那么让我解释一下.

I know there's a lot of booking questions on php section. Belive me when i tell you that I tried most of them, at least those which seems compatible. So let me explain.

我有这个约会表

 ID |    day     | start | end   | 
 ----------------------------------
  1 | 01-01-2018 | 09:00 | 10:00 | 
  2 | 01-01-2018 | 10:00 | 13:00 | 
  3 | 02-01-2018 | 12:00 | 15:00 | 
  4 | 02-01-2018 | 18:00 | 19:30 | 

我想知道，使用 sql 可以获得空的时间段吗?结果应该类似于:

I was wondering, it possibile with sql to get empty time slots? The result should like something like:

    day     | start | end
 ---------------------------
 01-01-2018 | 00:00 | 09:00
 01-01-2018 | 13:00 | 23:59
 02-01-2018 | 00:00 | 12:00
 02-01-2018 | 15:00 | 18:00
 02-01-2018 | 19:30 | 23:59

查询应包含 2 个日期:start_day + end_day

The query should contain 2 dates: start_day + end_day

我在这里准备了一个小提琴:https://www.db-fiddle.com/f/6dm8q8UtmDkkkjExYfMEbx/1

推荐答案

MSSQL 版本

WITH X AS 
(
SELECT ROW_NUMBER() OVER (ORDER BY Day, Start)sq, [Day], [Start], [End]
FROM (
SELECT [Day], [Start], [End]
FROM [appointments]
UNION
SELECT Day, '00:00', '00:00'
FROM [appointments]
UNION
SELECT Day, '23:59', '23:59'
FROM [appointments]
) T1
)
SELECT A.Day, A.[End] AS Start, b.[Start] AS End
FROM x A
JOIN x B
ON A.sq = B.sq -1
AND A.[Day] = B.[Day]
AND A.[End] <> b.[Start]

Mysql 5.7 版本

Mysql 5.7 version

SET @RowNumber = 0;

CREATE TABLE cte

SELECT (@RowNumber := @RowNumber+1) AS Rownumber, Day, Start, End
  FROM (
SELECT Day, Start, End
FROM booking
UNION
SELECT Day, '00:00', '00:00'
FROM booking
UNION
SELECT Day, '23:59', '23:59'
FROM booking
) T1
  ORDER BY day ASC, Start ASC

      ;

SELECT A.Day, A.End AS Start, B.Start AS End
FROM cte A
JOIN cte B
ON A.Rownumber = B.Rownumber -1
AND A.Day = B.Day
AND A.End <> B.Start
ORDER BY A.Day asc, A.End asc

将添加一个小提琴来演示https://www.db-fiddle.com/f/6dm8q8UtmDkkkjExYfMEbx/2

Will add a fiddle to demonstrate https://www.db-fiddle.com/f/6dm8q8UtmDkkkjExYfMEbx/2

Mysql 5.7 包括没有预订的天数

Mysql 5.7 including days without bookings

SET @RowNumber = 0;

CREATE TABLE cte

SELECT (@RowNumber := @RowNumber+1) AS Rownumber, Day, Start, End
  FROM (
SELECT Day, Start, End
FROM booking
UNION
SELECT Day, '00:00', '00:00'
FROM booking
UNION
SELECT Day, '23:59', '23:59'
FROM booking
) T1
  ORDER BY day ASC, Start ASC

      ;

SELECT DAY, Start, End 
FROM(
SELECT A.Day, A.End AS Start, B.Start AS End
FROM cte A
JOIN cte B
ON A.Rownumber = B.Rownumber -1
AND A.Day = B.Day
AND A.End <> B.Start
UNION
SELECT DATE_ADD(A.Day, INTERVAL 1 DAY) AS Day, B.Start AS Start, A.End AS End
FROM cte A
JOIN cte B
ON A.Rownumber = B.Rownumber -1
AND A.Day <> B.Day
)Result
ORDER BY Day ASC, Start ASC

https://www.db-fiddle.com/f/6dm8q8UtmDkkkjExYfMEbx/3

这篇关于SQL 从预订表中动态生成空闲插槽?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！