求一个简单而难的SQL语句
写一个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
作者: cg20061563 发布时间: 2011-05-12
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')
作者: wwwwb 发布时间: 2011-05-12
SQL code
select order_id from tb where status='REFUND'
作者: rucypli 发布时间: 2011-05-12
引用 1 楼 wwwwb 的回复:
select * from tt a where a.status='REFUND' and not exists(select 1 from tt where
a.order_id=order_id and status<>'OPEN')
select * from tt a where a.status='REFUND' and not exists(select 1 from tt where
a.order_id=order_id and status<>'OPEN')
都是WWWWB的代码:
SQL code
select * from tt a where a.status='REFUND' and not exists(select 1 from tt where a.order_id=order_id and status='OPEN'
作者: lxq19851204 发布时间: 2011-05-12