根据条件关联查询(条件关联)

在个别业务中,可能需要根据关联表与主表中的关联关系进行连接才能得到想要的结果, 这有点像条件编译. 符合条件的才去做关联,不符合条件的就不做关联(或说让关联条件失败)

 

环境: 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, a2
where 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 selected
SQL> select * from a2;
 
        ID TYPE        VAL ANYVALUE
---------- ---- ---------- ------------------------------
         1 >           100 >100
         1 <=          100 <=100
         2 <           150 <150
         3 <=          200 <=200
         4 >=          150 >=150
SQL> 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