sql - 多个子查询的内部连接？

Question

我正在编写一个包含几个子查询的选择（下面的代码片段中只有一个），但是我很难只返回 AIRFI_TCD 子查询中有 1 个现有行的行。我只想在 TSUMM 和 TPPPRFL 表上存在匹配的 CLT_ID 时显示行。我尝试了 HAVING COUNT 子句（见下文），但没有奏效。我正在讨论如何将 TSUMM 与我的子查询结合起来。

      SELECT RIGHT(DIGITS (A.CLT_ID),9) ||  A.PGM_ID ||
         RIGHT(DIGITS (A.PGM_ACCT_SQNBR),4) AS BN
       , ( SELECT B.XREF_NBR
           FROM GHMTUO#1.TPPPRFL B
           WHERE B.PARTIC_PRFL_TCD = '04' --AIRFI
             AND B.CLT_ID = A.CLT_ID
             AND B.BUS_PGM_ID = A.PGM_ID
             AND B.CLT_ID_TCD = '01'
             AND B.PARTIC_PRFL_EDT = (SELECT MAX(X.PARTIC_PRFL_EDT)
                                      FROM GHMTUO#1.TPPPRFL X
                                      WHERE X.CLT_ID = B.CLT_ID)
             AND B.PARTIC_PRFL_CDTTM = (SELECT MAX(Z.PARTIC_PRFL_CDTTM)
                                        FROM GHMTUO#1.TPPPRFL Z
                                        WHERE Z.CLT_ID = B.CLT_ID)
          ) AS AIRFI_TCD
  FROM RAMTUO#1.TSUMM A
  WHERE A.PGM_ID = 'RT'
  GROUP BY A.CLT_ID, A.PGM_ID, A.PGM_ACCT_SQNBR
  HAVING COUNT(AIRFI_TCD) > 1
  WITH UR;

谢谢！

标签： sqljoincountdb2subquery

Answer 1

如果你只需要存在，那么试试这个：

  SELECT RIGHT(DIGITS (A.CLT_ID),9) ||  A.PGM_ID ||
         RIGHT(DIGITS (A.PGM_ACCT_SQNBR),4) AS BN
  FROM RAMTUO#1.TSUMM A
  WHERE A.PGM_ID = 'RT'
  AND EXISTS 
        (
           SELECT 1
           FROM GHMTUO#1.TPPPRFL B
           JOIN 
           (
             SELECT CL
               T_ID
             , MAX(PARTIC_PRFL_EDT) PARTIC_PRFL_EDT
             , MAX(PARTIC_PRFL_CDTTM) PARTIC_PRFL_CDTTM
             FROM GHMTUO#1.TPPPRFL
             GROUP BY CLT_ID
           ) X ON X.CLT_ID = B.CLT_ID 
              AND X.PARTIC_PRFL_EDT = B.PARTIC_PRFL_EDT
              AND X.PARTIC_PRFL_CDTTM = B.PARTIC_PRFL_CDTTM
           WHERE B.PARTIC_PRFL_TCD = '04' --AIRFI
             AND B.CLT_ID = A.CLT_ID
             AND B.BUS_PGM_ID = A.PGM_ID
             AND B.CLT_ID_TCD = '01'
        )
  ;