根据条件关联查询(条件关联)
在个别业务中,可能需要根据关联表与主表中的关联关系进行连接才能得到想要的结果, 这有点像条件编译. 符合条件的才去做关联,不符合条件的就不做关联(或说让关联条件失败)
?
环境: oracle
请看代码:
create table A1(id number, val number);create table A2(id number, type varchar2(2), val number, anyvalue varchar2(30));insert into a1 values(1, 50);insert into a1 values(1, 500);insert into a1 values(2, 100);insert into a1 values(2, 150);insert into a1 values(3, 200);insert into a1 values(3, 250);insert into a1 values(4, 150);insert into a1 values(4, 500);insert into a2 values(1, '>', 100, '>100');insert into a2 values(1, '<=', 100, '<=100');insert into a2 values(2, '<', 150, '<150');insert into a2 values(3, '<=', 200, '<=200');insert into a2 values(4, '>=', 150, '>=150');select * from a1;select * from a2;select * from a1, a2where a1.id = a2.id and (case a2.type when '>' then (case when a1.val > a2.val then 1 else 0 end) when '>=' then (case when a1.val >= a2.val then 1 else 0 end) when '<=' then (case when a1.val <= a2.val then 1 else 0 end) when '<' then (case when a1.val < a2.val then 1 else 0 end) else 0 end) = 1;drop table a1 purge;drop table a2 purge;
?
加上执行结果,方便理解:
SQL> select * from a1; ID VAL---------- ---------- 1 50 1 500 2 100 2 150 3 200 3 250 4 150 4 500 8 rows selectedSQL> select * from a2; ID TYPE VAL ANYVALUE---------- ---- ---------- ------------------------------ 1 > 100 >100 1 <= 100 <=100 2 < 150 <150 3 <= 200 <=200 4 >= 150 >=150SQL> select * from a1, a2 2 where a1.id = a2.id 3 and (case a2.type 4 when '>' then 5 (case when a1.val > a2.val then 1 else 0 end) 6 when '>=' then 7 (case when a1.val >= a2.val then 1 else 0 end) 8 when '<=' then 9 (case when a1.val <= a2.val then 1 else 0 end) 10 when '<' then 11 (case when a1.val < a2.val then 1 else 0 end) 12 else 13 0 14 end) = 1; ID VAL ID TYPE VAL ANYVALUE---------- ---------- ---------- ---- ---------- ------------------------------ 1 50 1 <= 100 <=100 1 500 1 > 100 >100 2 100 2 < 150 <150 3 200 3 <= 200 <=200 4 150 4 >= 150 >=150 4 500 4 >= 150 >=150 6 rows selected
?
