oracle 如何通过连接四个具有多个条件的表来获取记录？

kq0g1dla 于 2023-10-16 发布在 Oracle

关注(0)|答案(3)|浏览(94)

场景：

如果ACNO在LINK_MASTER中有记录，则amt将按照以下逻辑共享
如果acno在cust_fin表中的状态为“2”，则将共享客户表中的amt列。
如果cust_fin表状态中的acno为“0”或“1”，则将共享NPA表amt列。
如果acno在LINK_MASTER中没有任何记录，则将共享customer表中的amt。
如果客户中的标志为'C'，则此字段的amt将被共享为NULL。

下面是表的XML和DML

create table link_master (
    acno varchar2(200)
);

create table custfin (
    status varchar2(200),
    acno varchar2(200)
);

create table npa (
    amt varchar2(200),
    acno varchar2(200)
);

create table customer (
    flag varchar2(200),
    amt varchar2(200)
    acno varchar2(200)
);

insert into link_master values('100');
insert into link_master values('101');
insert into link_master values('102');

insert into custfin values('1','101');
insert into custfin values('2','102');
insert into custfin values('0','100');

insert into npa values('3545','101');
insert into npa values('4566','102');
insert into npa values('4544','108');
insert into npa values('878','109');

insert into customer values('C','123','100');
insert into customer values('O','124','101');
insert into customer values('O','125','102');
insert into customer values('C','126','103');
insert into customer values('C','127','104');
insert into customer values('O','124','102');

预期结果：

ACNO    STATUS  FLAG    AMT
100      0      C       NULL
101      1      O       4566
102      2      O       4544
103      0      O       126
104             O       127
105             C       NULL

我不需要在输出状态和标志列。只需要acno和amt。查询需要在select语句中。因为我需要将这个查询与其他查询联接起来，

oracle

来源：https://stackoverflow.com/questions/77155589/how-to-get-records-by-joining-four-tables-which-has-multiple-condition

3条答案

按热度按时间

qeeaahzv1#

你可以使用类似的东西：

SELECT DISTINCT
       c.acno,
       f.status,
       c.flag,
       CASE 
       WHEN c.flag = 'C' THEN NULL
       WHEN l.acno IS NULL OR f.status = 2 THEN c.amt
       WHEN l.acno IS NOT NULL AND f.status IN (0, 1) THEN n.amt
       END AS amt
FROM   customer c
       LEFT OUTER JOIN link_master l
       ON c.acno = l.acno
       LEFT OUTER JOIN custfin f
       ON c.acno = f.acno
       LEFT OUTER JOIN npa n
       ON c.acno = n.acno;

对于样本数据，其输出：
| ACNO|地位|标志|AMT|
| --|--|--|--|
| 101 | 1 |O| 3545 |
| 102 | 2 |O| 125 |
| 102 | 2 |O| 124 |
| 100 | 0 |C| * 空 |
| 104 | 空 *| C| * 空 |
| 103 | 空 *| C| * 空 *|
或者，找到最大的amt：

SELECT acno,
       status,
       flag,
       amt
FROM   (
  SELECT c.acno,
         f.status,
         c.flag,
         CASE 
         WHEN c.flag = 'C' THEN NULL
         WHEN l.acno IS NULL OR f.status = 2 THEN c.amt
         WHEN l.acno IS NOT NULL AND f.status IN (0, 1) THEN n.amt
         END AS amt,
         ROW_NUMBER() OVER (
           PARTITION BY c.acno
           ORDER BY TO_NUMBER(
               CASE 
               WHEN c.flag = 'C' THEN NULL
               WHEN l.acno IS NULL OR f.status = 2 THEN c.amt
               WHEN l.acno IS NOT NULL AND f.status IN (0, 1) THEN n.amt
               END
             ) DESC
         ) AS rn
  FROM   customer c
         LEFT OUTER JOIN link_master l
         ON c.acno = l.acno
         LEFT OUTER JOIN custfin f
         ON c.acno = f.acno
         LEFT OUTER JOIN npa n
         ON c.acno = n.acno
)
WHERE  rn = 1;

对于样本数据，其输出：
| ACNO|地位|标志|AMT|
| --|--|--|--|
| 100 | 0 |C| * 空 |
| 101 | 1 |O| 3545 |
| 102 | 2 |O| 125 |
| 103 | 空 *| C| * 空 |
| 104 | 空 *| C| * 空 *|
注意：您不会得到预期的输出，因为：
1.示例数据中没有acno = 105的数据。

acno = 101无法连接到amt = 4566。
acno = 102的状态为2，因此amt应该来自customer，而不是npa。
acno = 103的flag是C，而不是O。
所有这些都是输入数据的问题;修复它们，您可能会得到预期的输出。
fiddle

赞(0）回复(0）举报 2023-10-16

gjmwrych2#

select cst.acno, cstf.status, cst.flag, npa.amt
left join custfin cstf on cst.acno=cstf.acno
left join npa on cst.acno=npa.acno
;

我想这个应该可以。对于这个查询，我认为我不需要使用link_master表，因为你不需要在其中包含任何列。我认为你应该在link_master（acno）上有主键link_master_pk，而在引用link_master（acno）的其他表中有外键。

赞(0）回复(0）举报 2023-10-16

dohp0rv53#

你的问题中有些条件的顺序不清楚。问题是3个指令中的哪一个会影响其他指令。
例如，如果ACNO 100在表npa中定义了AMT，您希望得到什么？它存在于link_master表中，并且状态为0，因此开始指令说从npa表中获取AMT，但是有一个标志C指示为AMT获取缓存。会是哪一个呢？？？
无论如何，您需要使用CASE表达式，这是严格定义不同条件之间的顺序和交互的主要原因。CASE表达式是顺序的，这意味着-第一个满足的条件将获取值并退出CASE。关键是要把条件的方式/顺序，将导致你的计划。下面是一个选项-我把它的方式，一些ACNO存在或不存在于link_master表。如果是，则按照第1点中所述获得AMT。如果没有，那么我选择合并结合你的点2和3.
首先是一些样本数据：

WITH
    link_master AS
        (   Select To_Char(100 + LEVEL - 1) "ACNO" From Dual Connect By LEVEL <= 3  ),
    custfin AS
        (   Select SubStr(ACNO, -1) "STATUS", ACNO From link_master     ), 
    npa AS
        (   Select '3545' "AMT", '101' "ACNO" From Dual Union All 
            Select '4566' "AMT", '102' "ACNO" From Dual Union All 
            Select '4544' "AMT", '108' "ACNO" From Dual Union All 
            Select '878'  "AMT", '109' "ACNO" From Dual 
        ),
    customer AS
        (   Select 'C' "FLAG", '123' "AMT", '100' "ACNO" From Dual Union All    
            Select 'O' "FLAG", '124' "AMT", '101' "ACNO" From Dual Union All 
            Select 'O' "FLAG", '125' "AMT", '102' "ACNO" From Dual Union All 
            Select 'C' "FLAG", '126' "AMT", '103' "ACNO" From Dual Union All 
            Select 'C' "FLAG", '127' "AMT", '104' "ACNO" From Dual Union All 
            Select 'O' "FLAG", '124' "AMT", '102' "ACNO" From Dual 
        )

下面是添加了一些注解的代码

Select    c.ACNO, cf.STATUS, c.FLAG, 
          CASE WHEN lm.ACNO Is Not Null Then                    -- ACNO IS PRESENT in link_master table
              Case  When cf.STATUS = '2' Then c.AMT                 -- if custfin STATUS is '2'         -> get AMT from customer
                    When cf.STATUS IN('0', '1') Then n.AMT          -- if custfin STATUS is '0' Or '1'  -> get AMT from npa
              Else  Null    --<-- here you can have something else  -- any custfin STATUS that is not IN('0', '1', '2') -> AMT is NULL
              End                                                
          --  ..............    ..............    ................    ....................    ..................    ............................
          ELSE                                                           -- ACNO IS NOT PRESENT in link_master table
              Case  When lm.ACNO Is Null And c.FLAG != 'C' Then c.AMT         -- if customer FLAG is NOT 'C'    -> get AMT from customer
              Else  Null                                                      -- if customer FLAG IS 'C'        -> AMT is NULL
              End 
          END "AMT"
From      customer c
Left Join link_master lm ON(lm.ACNO = c.ACNO)
Left Join custfin cf ON(cf.ACNO = c.ACNO)
Left Join npa n ON(n.ACNO = c.ACNO)
Order By c.ACNO, cf.STATUS 

--  R e s u l t :
--  
--  ACNO STATUS FLAG AMT 
--  ---- ------ ---- ----
--  100  0      C    Null     -> present in link_master, status 0    -> there is no AMT in NPA for ACNO 100
--  101  1      O    3545     -> present in link_master, status 1    -> AMT from npa  -> 3545
--  102  2      O    125      -> present in link_master, status 2    -> AMT from customer  -> 125 (one of two rows for ACNO 102)
--  102  2      O    124      -> present in link_master, status 2    -> AMT from customer  -> 124 (other one of two rows for ACNO 102)
--  103  Null   C    Null     -> not present in link_master, flag C  -> AMT Null
--  104  Null   C    Null     -> not present in link_master, flag C  -> AMT Null

注意：再一次，记住CASE表达式是顺序的。由您来定义条件的顺序和强度，并调整CASE表达式以获得所需的结果。

赞(0）回复(0）举报 2023-10-16

我来回答

oracle 如何通过连接四个具有多个条件的表来获取记录？

3条答案

相关问题

热门标签

最新问答