sql解惑 25 里程碑问题 答案

table servicesschedule( shop_id char(3) not null, order_nbr char(10) not null, sch_seq number not null check(sch_seq in (1,2,3)), service_type char(2) not null, sch_date date, primary key(shop_id,order_nbr,sch_seq)); drop table sch_seq; create table sch_seq_desc( sch_seq number not null, sch_desc varchar2(20) ); insert into sch_seq_desc values(1,‘processed‘); insert into sch_seq_desc values(2,‘completed‘); insert into sch_seq_desc values(3,‘confirmed‘); insert into servicesschedule values(‘002‘,‘4155526710‘,1,‘01‘,date‘1994-07-16‘); insert into servicesschedule values(‘002‘,‘4155526710‘,2,‘01‘,date‘1994-07-30‘); insert into servicesschedule values(‘002‘,‘4155526710‘,3,‘01‘,date‘1994-10-01‘); insert into servicesschedule values(‘002‘,‘4155526711‘,1,‘01‘,date‘1994-07-16‘); insert into servicesschedule values(‘002‘,‘4155526711‘,2,‘01‘,date‘1994-07-30‘); insert into servicesschedule values(‘002‘,‘4155526711‘,3,‘01‘,null); select * from sch_seq_desc; select * from servicesschedule; select a.order_nbr, min(case when sch_desc = ‘processed‘ then sch_date end) as processed, min(case when sch_desc = ‘completed‘ then sch_date end) as completed, min(case when sch_desc = ‘confirmed‘ then sch_date end) as confirmed from servicesschedule a,sch_seq_desc b where a.sch_seq=b.sch_seq and service_type= ‘01‘ group by order_nbr ; ============================================================================================== ============================================================================================== SQL> select * from servicesschedule; SHOP_I ORDER_NBR SCH_SEQ SERV SCH_DATE ------ -------------------- ---------- ---- ------------ 002 4155526710 1 01 16-JUL-94 002 4155526710 2 01 30-JUL-94 002 4155526710 3 01 01-OCT-94 002 4155526711 1 01 16-JUL-94 002 4155526711 2 01 30-JUL-94 002 4155526711 3 01 6 rows selected. select * from sch_seq_desc; SCH_SEQ SCH_DESC ---------- ---------------------------------------- 1 processed 2 completed 3 confirmed select a.order_nbr, min(case when sch_desc = ‘processed‘ then sch_date end) as processed, min(case when sch_desc = ‘completed‘ then sch_date end) as completed, min(case when sch_desc = ‘confirmed‘ then sch_date end) as confirmed from servicesschedule a,sch_seq_desc b where a.sch_seq=b.sch_seq and service_type= ‘01‘ group by order_nbr ; ORDER_NBR PROCESSED COMPLETED CONFIRMED -------------------- ------------ ------------ ------------ 4155526710 16-JUL-94 30-JUL-94 01-OCT-94 4155526711 16-JUL-94 30-JUL-94

 

sql解惑 25 里程碑问题 答案

标签：values   and   cte   creat   weight   com   --   div   cas