by shigemk2

当面は技術的なことしか書かない

union

結果の結合。

SELECT P1.proc_id, P2.proc_id, P1.anest_name, P2.start_time, +1
  FROM Procs AS P1, Procs AS P2
 WHERE P1.anest_name = P2.anest_name
   AND NOT (P2.end_time <= P1.start_time
              OR P2.start_time >= P1.end_time)
UNION
SELECT P1.proc_id, P2.proc_id, P1.anest_name, P2.end_time,
       -1 AS event_type
  FROM Procs AS P1, Procs AS P2
 WHERE P1.anest_name = P2.anest_name
   AND NOT (P2.end_time <= P1.start_time
              OR P2.start_time >= P1.end_time);

基本はまあ、selectの結果をcreateしたりとか。

CREATE VIEW Events
(proc_id, comparison_proc, anest_name, event_time, event_type) AS
SELECT P1.proc_id, P2.proc_id, P1.anest_name, P2.start_time, +1
  FROM Procs AS P1, Procs AS P2
 WHERE P1.anest_name = P2.anest_name
   AND NOT (P2.end_time <= P1.start_time
              OR P2.start_time >= P1.end_time)
UNION
SELECT P1.proc_id, P2.proc_id, P1.anest_name, P2.end_time,
       -1 AS event_type
  FROM Procs AS P1, Procs AS P2
 WHERE P1.anest_name = P2.anest_name
   AND NOT (P2.end_time <= P1.start_time
              OR P2.start_time >= P1.end_time);