dworksheet工单表
worksheet_id serv_order_id worksheet_status
1 1 0
2 1 9
3 2 9
4 2 9
5 3 9
6 3 9
现在我要取出serv_order_id,提取原则为只有worksheet_status都是9的才能取,如上serv_order_id=1不满足条件。
只有2,3才满足条件。这个SQL怎么写。
worksheet_id serv_order_id worksheet_status
1 1 0
2 1 9
3 2 9
4 2 9
5 3 9
6 3 9
现在我要取出serv_order_id,提取原则为只有worksheet_status都是9的才能取,如上serv_order_id=1不满足条件。
只有2,3才满足条件。这个SQL怎么写。
worksheet_id varchar(40) not null,
serv_order_id varchar(40) ,
worksheet_status)insert into dworksheet
select 1 , 1, 0 from dual
union
select 2,1,9 from dual
union
select 3,2,9 from dual
union
select 4,2,9 from dual
union
select 5,3,9 from dual
union
select 6,3,9 from dual;
commit;
select serv_order_id,count(decode(worksheet_status,9,1,'')),count(*) from dworksheet d
group by serv_order_id
having count(decode(worksheet_status,9,1,''))=count(*)
;
FROM (
SELECT COUNT(serv_order_id) OVER (PARTITION BY serv_order_id) AS LAB1,
COUNT(serv_order_id) OVER (PARTITION BY serv_order_id,worksheet_status) AS LAB2,
worksheet_id, serv_order_id, worksheet_status
FROM dworksheet
)
WHERE worksheet_status = 9 AND LAB1 = LAB2
from (select serv_order_id, worksheet_status
from dworksheet
group by serv_order_id, worksheet_status
having count(*) = 1)
where worksheet_status = 9