DB2的Sql子查询

Question

sql查询需要连接4个表，我这样做了，我必须显示其中满足条件的几列。假设这是Where子句中的查询。现在我如何编写子查询…显示另一列(ORG_NAME，存在于ORG_UNIT中)，其内容基于Where子句中查询所满足的行。我写了这段代码，但它不适合我:

SELECT T33.CONTRACT_NUM, T135.MINOR_ORG_NUM, T96.ORG_TYPE,T22.CFD_FLAG,
  (SELECT T96.ORG_NAME
   FROM ORG_UNIT T96, SUB_UNIT T135
   WHERE T96.ORG_NUMBER IN (T135.MAJOR_ORG_NUMBER)) AS HEAD_ORG_NAME
FROM
   ORG_UNIT T96, SUB_UNIT T135, CUST_CONTRACT T33, CONT_ASSIGNMT T22
WHERE
   T96.ORG_NUMBER = T22.ORG_NUMBER
  AND T22.CTR_SYS_NUM = T33.CTR_SYS_NUM
  AND T96.ORG_NUMBER = T135.MINOR_ORG_NUMBER
  AND T135.RELTN_TYPE = 'HOS'
  AND T22.CFD_FLAG = 'Y'; 

根据记录，T135包含总部号码(MAJOR_ORG_NUMBER)及其子办事处号码(MINOR_ORG_NUMBER)

Answer 1

在SQL中，使用JOIN将表根据它们的公共列"合并"在一起。

下面是一个简单的指南，可以让您了解基本的概念:SQL JOIN

在SQL中，最好画出你想要做的事情，所以请参考这个链接来查看"LEFT JOIN"图片示例:LEFT JOIN

使用"LEFT JOIN"合并你的表(其中:ORG_UNIT。ORG_NUMBER = SUB_UNIT.MAJOR_ORG_NUMBER)，将看起来像这样:

LEFT JOIN SUB_UNIT T135 ON T96.ORG_NUMBER = T135.MAJOR_ORG_NUMBER

在查询中，在"FROM"之后和"WHERE"之前放置一个JOIN:

SELECT 
    T33.CONTRACT_NUM,
    T135.MINOR_ORG_NUM,
    T96.ORG_TYPE,
    T22.CFD_FLAG,
    T135.ORG_NAME AS HEAD_ORG_NAME
FROM
    ORG_UNIT T96,
    CUST_CONTRACT T33,
    CONT_ASSIGNMT T22
LEFT JOIN SUB_UNIT T135 ON T96.ORG_NUMBER = T135.MAJOR_ORG_NUMBER
WHERE
    T96.ORG_NUMBER = T22.ORG_NUMBER
    AND T22.CTR_SYS_NUM = T33.CTR_SYS_NUM
    AND T96.ORG_NUMBER = T135.MINOR_ORG_NUMBER
    AND T135.RELTN_TYPE = 'HOS'
    AND T22.CFD_FLAG = 'Y';

注意，您可以(并且应该)使用JOIN来合并所有表(并避免使用昂贵的WHERE条件):

SELECT 
    T33.CONTRACT_NUM,
    T135.MINOR_ORG_NUM,
    T96.ORG_TYPE,
    T22.CFD_FLAG,
    T135.ORG_NAME AS HEAD_ORG_NAME
FROM
    ORG_UNIT T96
LEFT JOIN SUB_UNIT T135 ON
    T96.ORG_NUMBER = T135.MAJOR_ORG_NUMBER
    AND T96.ORG_NUMBER = T135.MINOR_ORG_NUMBER
LEFT JOIN  ON
    CONT_ASSIGNMT T22 ON T96.ORG_NUMBER = T22.ORG_NUMBER
LEFT JOIN ON 
    CUST_CONTRACT T33 ON T22.CTR_SYS_NUM = T33.CTR_SYS_NUM
WHERE
    T135.RELTN_TYPE = 'HOS'
    AND T22.CFD_FLAG = 'Y';

有几种JOIN类型(LEFT/RIGHT/INNER/OUTER)，所以请根据需要使用。