假设两个表:
test1
A B C
1 a >1000
1 b <10
2 a >20
3 c <1200
test2
D E
1 1010
2 15
3 1000
求一查询语句,结果如下:
A B C E
1 a >1000 1010
3 c <1200 1000
test1
A B C
1 a >1000
1 b <10
2 a >20
3 c <1200
test2
D E
1 1010
2 15
3 1000
求一查询语句,结果如下:
A B C E
1 a >1000 1010
3 c <1200 1000
能满足了要求,谁还有好的方法没
(
select 1 a,'a' b,'>1000' c from dual
union all
select 1,'b','<10' from dual
union all
select 2,'a','>20' from dual
union all
select 3,'c','<1200' from dual
),
b as
(select 1 d,1010 e from dual
union all
select 2 d,15 e from dual
union all
select 3 ,1000 from dual
)
select a,b,c,e from(
select a,b,c,e,
case when substr(c,1,1)='>' and e>substr(c,2) then e
when substr(c,1,1)='<' and e<substr(c,2) then e end w
from a ,b
where a.a=b.d )
where w is not null