求高难的sql语句

[felix3118]

求条sql语句：

tabel1(l1, l2)
记录：
　　　　1　　　　　　　a
2 b
tabel2(l1, w2, w3)
1 1 1
1 2 1
1 3 1
2 1 1
2 2 0
2 3 0
现要求table1里的l1
要求：
　　　　　如果有w3=1,该条记录对应的w2+1对应的w3为0，则l1被select出来

以上结果应为 2
l1 w2 w3
2 1 1
2 2 0
合适条件.


分全给出来。

[air_line]

select

[air_line]

select * from table2 where w3=0 and w2 in (select w2+1 from table2 where w3=1)

[OracleRoob]

--你要求的SQL语句
select *
from tabel2 as T
where w3=1 and exists (select * from tabel2 where l1=T.l1 and w2=T.w2+1 and w3=0)
union
select *
from tabel2 as T
where w3=0 and exists (select * from tabel2 where l1=T.l1 and w2=T.w2-1 and w3=1)

[OracleRoob]

--创建测试环境
create table tabel2 (l1 int, w2 int, w3 int)

--追加测试数据
insert into tabel2 select 1, 1, 1
insert into tabel2 select 1, 2, 1
insert into tabel2 select 1, 3, 1
insert into tabel2 select 2, 1, 1
insert into tabel2 select 2, 2, 0
insert into tabel2 select 2, 3, 0


--你要求的SQL语句
select *
from tabel2 as T
where w3=1 and exists (select * from tabel2 where l1=T.l1 and w2=T.w2+1 and w3=0)
union
select *
from tabel2 as T
where w3=0 and exists (select * from tabel2 where l1=T.l1 and w2=T.w2-1 and w3=1)

--删除测试表
drop table tabel2