欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

根据条件关联查询(条件关联) 博客分类: 数据库 Oracle条件关联

程序员文章站 2024-03-01 14:56:46
...

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

 

环境: 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

 

相关标签: Oracle 条件关联