sql - 允许基于预订 SQL 进行更新的场景

Question

场景为 DEMO001 系统由用户在 10-8 月至 11-8 月期间预订。

START_DATE     END DATE    SYSTEM
2016-08-10     2016-08-11  DEMO001
2016-09-05     2016-09-08  DEMO001
2016-08-08     2016-08-11  DEMO013
2016-08-16     2016-08-18  DEMO017

说如果我得到一个输入参数

1) start date as 2016-08-08  and end date as 2016-08-11 I can allow 
2) start date as 2016-08-11  and end date as 2016-09-08 I cannot allow
3) start date as 2016-08-10   and end date as 2016-08-15 I can allow
3) start date as 2016-08-10   and end date as 2016-09-06 I cannot allow

如果用户试图通过延长或提前开始或结束日期来更新此系统中的任何一个，如果在这些天之间没有其他人预订，我将不得不说 0 或 1 。

这是这个场景的扩展

Checking if the "system" falls between two dates in SQL

我尝试修改那里给出的建议，但无法正确获得它。请建议。

Answer 1

尝试:

WITH dates AS (

  -- input data (ranges)
  SELECT date '2016-08-08' as start_date,  date '2016-08-11' as end_date from dual union all
  SELECT date '2016-08-11',  date '2016-09-08' from dual union all
  SELECT date '2016-08-10',  date '2016-08-15' from dual union all
  SELECT date '2016-08-10',  date '2016-09-06' from dual
)

-- the query
SELECT d.start_date, d.end_date, 
       CASE WHEN count(*) > 1 
            THEN 'Disallow' ELSE 'Allow' 
            -- change the above line to => THEN 0 ELSE 1 <= if you prefer numbers
       END is_allowed
FROM dates d
LEFT JOIN table1 t1 -- table1 holds booking data, eg DEMO0001 etc.
ON (d.Start_date <= t1.end_date)  and  (d.end_date >= t1.start_date ) 
    AND t1.system = 'DEMO001'
GROUP BY d.start_date, d.end_date
ORDER BY 1