sql解惑-麻醉师问题

于 2009-03-24 18:03:36 发布
阅读量139 收藏
点赞数
分类专栏： sql语法 文章标签： SQL
[b]问题：Leonard C.Medal在许多年前提出了很多了这个技巧问题。 在医院的手术室中， 麻醉师为手术中的病人实行麻醉。 每个人麻醉过程的信息都记录在一个表中。[/b]
[list]
[quote][i]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[/i][/quote]
[b]注意抹嘴是的某些时间是重叠的， 这不是错误。 麻醉师跟外科医生不同， 他可以在手术过程中从一个手术室走到另一个手术室，依次检查每一个病人， 调整药的计量， 留下实习医生和护士时刻监察病人的情况。[/b]

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

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

--创建一个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表插入数据
[i]
-- 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
[/i] 
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'));

[b]--方法1[/b]
如果显示权限不足， 请登入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的时间段的真子集（不包括两端）
[b]方法2[/b] 
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


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

解析上面这个复杂的查询
首先查看它的子查询， 分为几步
[b]step 1[/b] 
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;

[b]结果是[/b]
[i]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[/i]
[b]step 2 [/b]
对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


[b]得到的查询结果[/b][i]
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[/i]
[b]得到一个结论： 就是在同意麻醉师的情况下， 找到一组其中p1的启动时间在p2在开始之后（包括开始时刻）、在p2结束之前，这样的到的结果p1就是和其他麻醉记录(包括自己)有交集的。然后在在一proc_id,anest_name 分组，就可以得到和其他记录有时间相交的记录（但是并不是相交记录的最大数目）[/b]
[b]step 3[/b] 
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

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

[b]方法3[/b]
想法是在所有的麻醉记录过程中（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;

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

[b]结果有问题： 应该选取八项：[/b]
liaobinxu0918
关注
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值