写一个SQL语句,找出下面表中符合条件的order_id
条件:order_id对应的status中REFUND,但是不能有OPEN
如果100对应的status有REFUND、OPEN,则100不属于,而111则符合。
id order_id status
1 100 OPEN
2 111 REFUND
3 100 REFUND
4 100 VOID
5 111 LIFT
6 110 OPEN
条件:order_id对应的status中REFUND,但是不能有OPEN
如果100对应的status有REFUND、OPEN,则100不属于,而111则符合。
id order_id status
1 100 OPEN
2 111 REFUND
3 100 REFUND
4 100 VOID
5 111 LIFT
6 110 OPEN
a.order_id=order_id and status<>'OPEN')
from tb
where status='REFUND'
select * from tt a where a.status='REFUND' and not exists(select 1 from tt where
a.order_id=order_id and status='OPEN'
from (select * from 下面表 where status='REFUND') a
left join (select * from 下面表 where status='OPEN') b on a.order_id=b.order_id
where b.order_id is null
select * from tt a where a.status='REFUND' and not exists(select 1 from tt where
a.order_id=order_id and status='OPEN')
a.order_id=order_id and status='OPEN')id order_id status
2 111 REFUND