有条件地加入甲骨文 [英] conditional joining in oracle

问题描述

有条件的加入声明:-

高级描述:- 如果不存在组合，则基于两列联接表，然后联接一张表-

High level Description :- Join table based on two columns if combination is not present then join on one table -

详细表格-

create table tab1
(tab1_col1 number not null,
tab1_col2 number null,
tab1_col3 varchar(10));

查询表

create table lkp1
(lkp_col1 number not null,
lkp_col2 number not null,
lkp_col3 number not null,
lkp_col4 varchar(10));

插入声明- tab1

Insert Statement - tab1

insert into tab1 values (10,101,'A');
insert into tab1 values (12,101,'B');
insert into tab1 values (11,102,'C');
insert into tab1 values (13,103,'B');
insert into tab1 values (14,104,'C');
insert into tab1 values (15,108,'A');
insert into tab1 values (16,102,'D');

查询表 lkp1

Lookup Table lkp1

insert into lkp1 values (10,101,50,'PICK');
insert into lkp1 values (10,101,50,'PICK');
insert into lkp1 values (11,102,100,'SKIP');
insert into lkp1 values (11,110,50,'PICK');
insert into lkp1 values (13,103,40,'PICK');
insert into lkp1 values (13,103,60,'PICK');
insert into lkp1 values (14,199,100,'PICK');
insert into lkp1 values (15,115,80,'PICK');
insert into lkp1 values (15,115,20,'PICK');

要求为- 基于tab1_col1 = lkp_col1的联接表 和 tab1_col2 = lkp_col2 过滤掉查询表数据lkp_col4 = SKIP 如果在查找表中没有记录，则提供默认值(99.99). (应该选择tab1表中的所有记录).

Requirement was - Join table based on tab1_col1=lkp_col1 and tab1_col2=lkp_col2 Filter out lookup table data lkp_col4=SKIP If record not present in lookup table for then give default value(99.99). (All records from tab1 table should be selected).

我建立了这个查询，它运行正常.

I built this query and it was working fine.

SELECT tab1_col1, tab1_col2, NVL (lkp_col3, '99.99') "LKP_COL3"
  FROM tab1,
       (SELECT *
          FROM lkp1
         WHERE lkp_col4 = 'PICK') lkp
 WHERE tab1_col1 = lkp_col1(+) AND tab1_col2 = lkp_col2(+)

现在要求已更改 首先检查 tab1_col1 = lkp_col1 和 tab1_col2 = lkp_col2 如果查询表没有该组合的数据 然后再次检查 tab1_col1 = lkp_col1 如果还不可用，则提供dafault值.

Now requirement changed First check if tab1_col1=lkp_col1 and tab1_col2=lkp_col2 If lookup table is not having data for this combination then check again with tab1_col1=lkp_col1 If this is not also available then give dafault value.

数据库-Oracle 10g

Database - Oracle 10g

到目前为止我尝试过的事情

经过大量的跟踪和错误后，我能够得到输出.但是，有没有更好或更简单的方法来使用此功能?

After lot of trail and error I m able to get the output. However, is there a better or simple way to use this ?

SELECT tab1_col1, tab1_col2, LKP_COL3
  FROM tab1,
       (SELECT *
          FROM lkp1
         WHERE lkp_col4 = 'PICK') lkp
 WHERE tab1_col1 = lkp_col1 AND tab1_col2 = lkp_col2
 union all
 SELECT tab1_col1, tab1_col2, LKP_COL3
  FROM tab1,
       (SELECT *
          FROM lkp1
         WHERE lkp_col4 = 'PICK') lkp
 WHERE tab1_col1 = lkp_col1(+)
 AND  
 LKP_COL1|| '-' || LKP_COL2 not in( SELECT tab1_col1|| '-' || tab1_col2
  FROM tab1, lkp1 lkp
 WHERE tab1_col1 = lkp_col1 AND tab1_col2 = lkp_col2)
 order by 1

推荐答案

以下结果:

| TAB1_COL1 | TAB1_COL2 | LKP_COL3 |
|-----------|-----------|----------|
|        10 |       101 |       50 |
|        11 |       102 |       50 |
|        12 |       101 |    99.99 |
|        13 |       103 |       40 |
|        13 |       103 |       60 |
|        14 |       104 |      100 |
|        15 |       108 |       20 |
|        15 |       108 |       80 |
|        16 |       102 |    99.99 |

是由以下查询产生的:

SELECT DISTINCT
      tab1.tab1_col1
    , tab1.tab1_col2
    , COALESCE(lkp1.lkp_col3, lkp2.lkp_col3, 99.99) "LKP_COL3"
FROM tab1
      LEFT JOIN lkp1
                  ON tab1.tab1_col1 = lkp1.lkp_col1
                        AND tab1.tab1_col2 = lkp1.lkp_col2
                        AND lkp1.lkp_col4 = 'PICK'
      LEFT JOIN lkp1 lkp2
                  ON tab1.tab1_col1 = lkp2.lkp_col1
                        AND lkp2.lkp_col4 = 'PICK'
ORDER BY
      tab1.tab1_col1
    , tab1.tab1_col2
;

添加了DISTINCT，因为第二个左(外)连接在输出中产生了不必要的重复.

DISTINCT was added because the second left (outer) join produces unwanted repetition in the output.

请参阅此 sqlfiddle

这篇关于有条件地加入甲骨文的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！