//上午值班人数为1人或2人时，查出值班信息和个人信息select b.contact as mContact,b.email as mEmail,c.contact as m2Contact,c.email as m2Email,t.* from(selectsubstr(a.morning_people_ids,0,instr(a.morning_people_ids,',',1,1)-1) as amId,substr(a.morning_people_ids,instr(a.morning_people_ids,',',1,1)+1) as bmId,a.*from tb_duty a ) t,tb_duty_person_info b,tb_duty_person_info cwhere 1=1 andnvl2(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(selectsubstr(a.morning_people_ids,0,instr(a.morning_people_ids,',',1,1)-1) as mId,substr(a.morning_people_ids,instr(a.morning_people_ids,',',1,1)+1) as m2Id,substr(a.afternoon_people_ids,0,instr(a.afternoon_people_ids,',',1,1)-1) as aId,substr(a.afternoon_people_ids,instr(a.afternoon_people_ids,',',1,1)+1) as a2Id,substr(a.evening_people_ids,0,instr(a.evening_people_ids,',',1,1)-1) as eId,substr(a.evening_people_ids,instr(a.evening_people_ids,',',1,1)+1) as e2Id,substr(a.leader_ids,0,instr(a.leader_ids,',',1,1)-1) as lId,substr(a.leader_ids,instr(a.leader_ids,',',1,1)+1) as l2Id,a.*from tb_duty a ) t,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 l2where 1=1 andnvl2(t.mId,t.mId,t.m2Id)=m1.person_id and t.m2Id=m2.person_id andnvl2(t.aId,t.aId,t.a2Id)=a1.person_id and t.a2Id=a2.person_id andnvl2(t.eId,t.eId,t.e2Id)=e1.person_id and t.e2Id=e2.person_id andnvl2(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 ;