sql解惑-麻醉师问题

问题：Leonard C.Medal在许多年前提出了很多了这个技巧问题。 在医院的手术室中， 麻醉师为手术中的病人实行麻醉。 每个人麻醉过程的信息都记录在一个表中。
[list]

引用

procs
proc_id           anest_name        start_time        end_time
----------------------------------------------------------------------------
10                    'Baker'                 08:00               11:00
20                    'Baker'                 09:00                13:00
30                    'Dow'                   09:00                15:30
40                    'Dow'                   08:00                13:30
50                    'Dow'                  10:00                 11:30
60                    'Dow'                  12:30                 13:30
70                    'Dow'                  13:30                 14:30
80                    'Dow'                  18:00                 19:00

注意抹嘴是的某些时间是重叠的， 这不是错误。 麻醉师跟外科医生不同， 他可以在手术过程中从一个手术室走到另一个手术室，依次检查每一个病人， 调整药的计量， 留下实习医生和护士时刻监察病人的情况。

麻醉师的工资按照麻醉过程数量支付， 但是计算方法很复杂。 根据麻醉师同时负责的麻醉过程的最大数量， 为每个麻醉过程二支付给麻醉师的报酬是浮动的。 麻醉工程越多， 为每个过程支付的报酬就越少。

问题就成为对于每一个人进行中的麻醉过程， 确定每一个麻醉师同时进行麻醉过程的最大即时数目。
我们通过可以通过图形到处答案，以便更好的理解问题。
proc_id     max_inst_count
------------------------------------------------
10            2
20           2
30            3
40            3
50            3
60            3
70            2
80            1

--创建一个sequence proc_sequence
create sequence proc_sequence
increment by 10
start with 10
nomaxvalue
nocycle
noorder;
--创建一个procs表
 create table procs(
        proc_id integer not null primary key,
        anest_name  varchar2(40) not null,
        start_time date not null,
        end_time date not null
 );

向procs表插入数据

--     PROC_ID ANEST_NAME                               START_TIME  END_TIME
--------------------------------------- ---------------------------------------- ----------- -----------
--     10 'Baker'                                     08:00:00                       11:00:00
--     20 'Baker'                                     09:00:00                       13:00:00
--     30 'Dow'                                       09:00:00                       15:30:00
--     40 'Dow'                                       08:00:00                       13:30:00
--     50 'Dow'                                       10:00:00                       11:30:00
--     60 'Dow'                                       12:30:00                       13:30:00
--     70 'Dow'                                       13:30:00                       14:30:00
--     80 'Dow'                                       18:00:00                       19:30:00

insert into procs values(
proc_sequence.nextval,'Baker',to_date('2009-3-22 08:00','yyyy-mm-dd hh24:mi'),
to_date('2009-3-22 11:00','yyyy-mm-dd hh24:mi'));
insert into procs values(
proc_sequence.nextval,'Baker',to_date('2009-3-22 09:00','yyyy-mm-dd hh24:mi'),
to_date('2009-3-22 13:00','yyyy-mm-dd hh24:mi'));
insert into procs values(
proc_sequence.nextval,'Dow',to_date('2009-3-22 09:00','yyyy-mm-dd hh24:mi'),
to_date('2009-3-22 15:30','yyyy-mm-dd hh24:mi'));
insert into procs values(
proc_sequence.nextval,'Dow',to_date('2009-3-22 08:00','yyyy-mm-dd hh24:mi'),
to_date('2009-3-22 13:30','yyyy-mm-dd hh24:mi'));
insert into procs values(
proc_sequence.nextval,'Dow',to_date('2009-3-22 10:00','yyyy-mm-dd hh24:mi'),
to_date('2009-3-22 11:30','yyyy-mm-dd hh24:mi'));
insert into procs values(
proc_sequence.nextval,'Dow',to_date('2009-3-22 12:30','yyyy-mm-dd hh24:mi'),
to_date('2009-3-22 13:30','yyyy-mm-dd hh24:mi'));
insert into procs values(
proc_sequence.nextval,'Dow',to_date('2009-3-22 13:30','yyyy-mm-dd hh24:mi'),
to_date('2009-3-22 14:30','yyyy-mm-dd hh24:mi'));
insert into procs values(
proc_sequence.nextval,'Dow',to_date('2009-3-22 18:00','yyyy-mm-dd hh24:mi'),
to_date('2009-3-22 19:30','yyyy-mm-dd hh24:mi'));

--方法1
如果显示权限不足， 请登入sys账号，并以as sysdba方式登录 然后授权view给scott // grant create view to scott;

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 p1, procs p2      
where p1.anest_name=p2.anest_name                                                                                 
union
select p1.proc_id, p2.proc_id, p1.anest_name,p2.end_time,-1 from procs p1, procs p2
where p1.anest_name=p2.anest_name
and not (p2.end_time<=p1.start_time or p2.start_time>=p1.end_time);

说明：用数学知识：not ((p2.end_time<=p1.start_time or p2.start_time>=p1.end_time) )等价于 p2.end_time>p1.start_time and p2.start_time <p1.end_time,意思是p2
--的时间段在p1的时间段的真子集（不包括两端）
方法2

select p3.proc_id , max(ConcurrentProcs.tally)
from (select p1.anest_name  anest_name, p1.start_time start_time, count(*) tally
     from procs p1
     inner join  procs p2
     on p1.anest_name=p2.anest_name
     and p2.start_time<=p1.start_time
     and p2.end_time>p1.start_time
     group by p1.anest_name, p1.start_time
)ConcurrentProcs--(anest_name,start_time, tally)
  inner join
  procs p3
  on ConcurrentProcs.anest_name=p3.anest_name
  and p3.start_time<=ConcurrentProcs.start_time
  and p3.end_time> ConcurrentProcs.start_time
  group by p3.proc_id

结果      
                        PROC_ID MAX(CONCURRENTPROCS.TALLY)
--------------------------------------- --------------------------
                                     30                          3
                                     20                          2
                                     70                          2
                                     40                          3
                                     50                          3
                                     80                          1
                                     10                          2
                                     60                          3

解析上面这个复杂的查询
首先查看它的子查询， 分为几步
step 1

select p1.anest_name , to_char(p1.start_time,'hh24:mi:ss'), count(*)
     from procs p1
     inner join  procs p2
     on p1.anest_name=p2.anest_name
     and p2.start_time<=p1.start_time
     and p2.end_time>p1.start_time
     group by p1.anest_name, p1.start_time;

结果是
ANEST_NAME                               TO_CHAR(P1.START_TIME,'HH24:MI   COUNT(*)
---------------------------------------- ------------------------------ ----------
Baker                                    09:00:00                                2
Dow                                      13:30:00                                2
Dow                                      08:00:00                                1
Dow                                      12:30:00                                3
Dow                                      10:00:00                                3
Baker                                    08:00:00                                1
Dow                                      09:00:00                                2
Dow                                      18:00:00                                1
step 2
对step 1 的查询在进行分解

select p1.proc_id P1ID,p2.proc_id P2ID,p1.anest_name P1ANEST_NAME ,
  to_char(P2.start_time,'hh24:mi:ss') P2ST,
  to_char(p1.start_time,'hh24:mi:ss') P1ST,
  to_char(p2.end_time,'hh24:mi:ss') P2ET
     from procs p1
     inner join  procs p2
     on p1.anest_name=p2.anest_name
     and p2.start_time<=p1.start_time
     and p2.end_time>p1.start_time order by p1.proc_id

    
得到的查询结果
                                   P1ID                                    P2ID P1ANEST_NAME                             P2ST     P1ST     P2ET
--------------------------------------- --------------------------------------- ---------------------------------------- -------- -------- --------
                                     10                                      10 Baker                                    08:00:00 08:00:00 11:00:00
                                     20                                      10 Baker                                    08:00:00 09:00:00 11:00:00
                                     20                                      20 Baker                                    09:00:00 09:00:00 13:00:00
                                    30                                      30 Dow                                      09:00:00 09:00:00 15:30:00
                                     30                                      40 Dow                                      08:00:00 09:00:00 13:30:00
                                    40                                      40 Dow                                      08:00:00 08:00:00 13:30:00
                                     50                                      50 Dow                                      10:00:00 10:00:00 11:30:00
                                     50                                      40 Dow                                      08:00:00 10:00:00 13:30:00
                                     50                                      30 Dow                                      09:00:00 10:00:00 15:30:00
                                     60                                      30 Dow                                      09:00:00 12:30:00 15:30:00
                                     60                                      40 Dow                                      08:00:00 12:30:00 13:30:00
                                    60                                      60 Dow                                      12:30:00 12:30:00 13:30:00
                                     70                                      70 Dow                                      13:30:00 13:30:00 14:30:00                                     70                                      30 Dow                                      09:00:00 13:30:00 15:30:00
                                     80                                      80 Dow                                      18:00:00 18:00:00 19:30:00
得到一个结论： 就是在同意麻醉师的情况下， 找到一组其中p1的启动时间在p2在开始之后（包括开始时刻）、在p2结束之前，这样的到的结果p1就是和其他麻醉记录(包括自己)有交集的。然后在在一proc_id,anest_name 分组，就可以得到和其他记录有时间相交的记录（但是并不是相交记录的最大数目）
step 3

select p3.proc_id , max(ConcurrentProcs.tally)
from (select p1.anest_name  anest_name, p1.start_time start_time, count(*) tally
     from procs p1
     inner join  procs p2
     on p1.anest_name=p2.anest_name
     and p2.start_time<=p1.start_time
     and p2.end_time>p1.start_time
     group by p1.anest_name, p1.start_time
)ConcurrentProcs--(anest_name,start_time, tally)
  inner join
  procs p3
  on ConcurrentProcs.anest_name=p3.anest_name
  and p3.start_time<=ConcurrentProcs.start_time
  and p3.end_time> ConcurrentProcs.start_time
  group by p3.proc_id

结果    
                         PROC_ID MAX(CONCURRENTPROCS.TALLY)
--------------------------------------- --------------------------
                                     30                          3
                                     20                          2
                                     70                          2
                                     40                          3
                                     50                          3
                                     80                          1
                                     10                          2
                                     60                          3
结论： step 2中找到的集合p1的就是step 3的视图ConcurrentProcs，而p3.start_time<=ConcurrentProcs.start_time and p3.end_time> ConcurrentProcs.start_time这两个条件表示找到一个集合p3能够在p1发生和p1结束之前发生（p1和任何记录都有时间交集的麻醉记录），等价于和任何都有交集的集合有相交的集合的条件下，取相交数量的最大值， 就是确定每一个麻醉师同时进行麻醉过程的最大即时数目


方法3
想法是在所有的麻醉记录过程中（p1）进行循环；对于每一个麻醉过程p1， 查找其起始时间落在麻醉过程p1中的麻醉过程p2. 对于找到的每一个p2的起始时间，计算该时间正在进行的麻醉过程（p3）的数目。然后，去出麻醉过程p1的最大计数。

create view Vprocs(id1,id2,total)
as
select p1.proc_id,p2.proc_id,count(*) from procs p1, procs p2, procs p3
where p2.anest_name=p1.anest_name
and p3.anest_name=p1.anest_name
and p1.start_time<=p2.start_time
and p1.start_time<p2.end_time
and p3.start_time<p2.start_time
and p2.start_time<p3.end_time
group by p1.proc_id,p2.proc_id;

然后对每一过程取最大值：
select id1 proc_id, max(total) max_inst_count from Vprocs group by id1;
结果是
                                PROC_ID MAX_INST_COUNT
--------------------------------------- --------------
                                     30              2
                                     70              1
                                     20              1
                                     50              2
                                     40              2
                                     60              2
                                     10              1
共有七项记录

结果有问题： 应该选取八项：