sql解惑谜题3 麻醉师谜题

来源：互联网发布：解防沉迷软件编辑：程序博客网时间：2024/05/16 02:01

DROP TABLE PROCS;

CREATE TABLE PROCS AS

SELECT 10 AS proc_id, 'Baker' AS anest_name, '08:00' AS start_time, '11:00' AS end_time FROM dual UNION ALL

SELECT 20 AS proc_id, 'Baker' AS anest_name, '09:00' AS start_time, '13:00' AS end_time FROM dual UNION ALL

SELECT 30 AS proc_id, 'Dow' AS anest_name, '09:00' AS start_time, '15:30' AS end_time FROM dual UNION ALL

SELECT 40 AS proc_id, 'Dow' AS anest_name, '08:00' AS start_time, '13:30' AS end_time FROM dual UNION ALL

SELECT 50 AS proc_id, 'Dow' AS anest_name, '10:00' AS start_time, '11:30' AS end_time FROM dual UNION ALL

SELECT 60 AS proc_id, 'Dow' AS anest_name, '12:30' AS start_time, '13:30' AS end_time FROM dual UNION ALL

SELECT 70 AS proc_id, 'Dow' AS anest_name, '13:30' AS start_time, '14:30' AS end_time FROM dual UNION ALL

SELECT 80 AS proc_id, 'Dow' AS anest_name, '18:00' AS start_time, '19:00' AS end_time FROM dual;

我们首先生成各时间点

SELECT anest_name, start_time AS t FROM procs

UNION

SELECT anest_name, end_time AS t FROM procs;

ANEST_NAME T

---------- -----

Baker 08:00

Baker 09:00

Baker 11:00

Baker 13:00

Dow 08:00

Dow 09:00

Dow 10:00

Dow 11:30

Dow 12:30

Dow 13:30

Dow 14:30

Dow 15:30

Dow 18:00

Dow 19:00

14 rows selected

对于各时间点，分别取出对应的麻醉过程数据。

SELECT a.t, b.*, COUNT(*) over(PARTITION BY a.anest_name, a.t) AS ct

FROM (SELECT anest_name, start_time AS t FROM procs

UNION

SELECT anest_name, end_time AS t FROM procs) a

INNER JOIN procs b ON (b.anest_name = a.anest_name AND b.start_time <= a.t AND b.end_time > a.t)

ORDER BY 3, 1, 2;

T PROC_ID ANEST_NAME START_TIME END_TIME CT

----- ---------- ---------- ---------- -------- ----------

08:00 10 Baker 08:00 11:00 1

09:00 10 Baker 08:00 11:00 2

09:00 20 Baker 09:00 13:00 2

11:00 20 Baker 09:00 13:00 1

08:00 40 Dow 08:00 13:30 1

09:00 30 Dow 09:00 15:30 2

09:00 40 Dow 08:00 13:30 2

10:00 30 Dow 09:00 15:30 3

10:00 40 Dow 08:00 13:30 3

10:00 50 Dow 10:00 11:30 3

11:30 30 Dow 09:00 15:30 2

11:30 40 Dow 08:00 13:30 2

12:30 30 Dow 09:00 15:30 3

12:30 40 Dow 08:00 13:30 3

12:30 60 Dow 12:30 13:30 3

13:30 30 Dow 09:00 15:30 2

13:30 70 Dow 13:30 14:30 2

14:30 30 Dow 09:00 15:30 1

18:00 80 Dow 18:00 19:00 1

19 rows selected

然后分别取各麻醉过程最大重叠时间点即可

SELECT proc_id,anest_name, start_time,end_time, MAX(ct) AS ct

FROM

(

SELECT a.t, b.*, COUNT(*) over(PARTITION BY a.anest_name, a.t) AS ct

FROM (SELECT anest_name, start_time AS t

FROM procs

UNION

SELECT anest_name, end_time AS t

FROM procs) a

INNER JOIN procs b

ON (b.anest_name = a.anest_name AND b.start_time <= a.t AND

b.end_time > a.t)

)

GROUP BY proc_id,anest_name, start_time,end_time

ORDER BY 2,1;

PROC_ID ANEST_NAME START_TIME END_TIME CT

---------- ---------- ---------- -------- ----------

10 Baker 08:00 11:00 2

20 Baker 09:00 13:00 2

30 Dow 09:00 15:30 3

40 Dow 08:00 13:30 3

50 Dow 10:00 11:30 3

60 Dow 12:30 13:30 3

70 Dow 13:30 14:30 2

80 Dow 18:00 19:00 1

8 rows selected