oracle中单字段存在1个或2个关联id时的查询sql

//上午值班人数为1人或2人时，查出值班信息和个人信息
select 
b.contact as mContact,b.email as mEmail,c.contact as m2Contact,c.email as m2Email,t.* from(
select
substr(a.morning_people_ids,instr(a.morning_people_ids,',1,1)-1) as amId,substr(a.morning_people_ids,1)+1) as bmId,a.*
from tb_duty a 
) t,tb_duty_person_info b,tb_duty_person_info c
where 1=1 and
nvl2(t.amId,t.amId,t.bmId)=b.person_id 
and t.bmId=c.person_id 
and t.dscd='340100' and t.unit_id=4 
and t.day0='2015-08-06'

//整个8月的
select 
m1.person_name as mName,m1.contact as mContact,m1.email as mEmail,m2.person_name as m2Name,m2.contact as m2Contact,m2.email as m2Email,a1.person_name as aName,a1.contact as aContact,a1.email as aEmail,a2.person_name as a2Name,a2.contact as a2Contact,a2.email as a2Email,e1.person_name as eName,e1.contact as eContact,e1.email as eEmail,e2.person_name as e2Name,e2.contact as e2Contact,e2.email as e2Email,l1.person_name as lName,l1.contact as lContact,l1.email as lEmail,l2.person_name as l2Name,l2.contact as l2Contact,l2.email as l2Email,t.week_day,t.flag,t.day0
 from(
select
substr(a.morning_people_ids,1)-1) as mId,1)+1) as m2Id,substr(a.afternoon_people_ids,instr(a.afternoon_people_ids,1)-1) as aId,1)+1) as a2Id,substr(a.evening_people_ids,instr(a.evening_people_ids,1)-1) as eId,1)+1) as e2Id,substr(a.leader_ids,instr(a.leader_ids,1)-1) as lId,1)+1) as l2Id,tb_duty_person_info m1,tb_duty_person_info m2,tb_duty_person_info a1,tb_duty_person_info a2,tb_duty_person_info e1,tb_duty_person_info e2,tb_duty_person_info l1,tb_duty_person_info l2
where 1=1 
and
nvl2(t.mId,t.mId,t.m2Id)=m1.person_id 
and t.m2Id=m2.person_id 
and
nvl2(t.aId,t.aId,t.a2Id)=a1.person_id 
and t.a2Id=a2.person_id 
and
nvl2(t.eId,t.eId,t.e2Id)=e1.person_id 
and t.e2Id=e2.person_id 
and
nvl2(t.lId,t.lId,t.l2Id)=l1.person_id 
and t.l2Id=l2.person_id 
and t.dscd='340100' and t.unit_id=4 
and to_char(to_date(t.day0,'yyyy-mm-dd'),'yyyy-mm')='2015-08'

//查询tb_duty_person_info第11-20条记录（根据person_id排序）：
   //0.031秒
   select a.* from (
   select rownum rn,b.* from tb_duty_person_info b  where rownum<21 order by b.person_id
   ) a 
   where a.rn>10;
   //0.047秒
      select a.* from (
   select rownum rn,b.* from tb_duty_person_info b order by b.person_id
   ) a 
   where a.rn>10 and rn<21 ;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!