连接具有条件的表，以便在同一列中返回不同的值

Question

我有以下表格和列要加入：

 1. CUSTOMERS: customer_id,source_system

 2. ALERT_CASE_HEADER: customer_id,data_source_id

 3. DATA_SOURCE:id,name

查询是检索CUSTOMERS.source_system，表中的customer_id和ALERT_CASE_HEADER都是链接的。但是，如果是DATA_SOURCE.name='Interactive'，源系统应该默认为'NIL'，两列data_source_id and id都是链接的。

我正在考虑加入他们，但似乎在客户和DATA_SOURCE之间没有任何关系来确定它是否是‘互动’

SELECT SOURCE_SYSTEM FROM CUSTOMERS CUS INNER JOIN
ALERT_CASE_HEADER ACH ON ACH.CUSTOMER_ID = CUS.CUSTOMER_ID INNER JOIN
ALERT_CASE_HEADER ACH ON ACH.CUSTOMER_ID = CUS.CUSTOMER_ID WHERE
ACH.DATASOURCE_ID=(SELECT DS.ID FROM DATA_SOURCE DS WHERE
DS.NAME='Interactive');

上面的查询是固定的，所以我把它作为子查询之一，因为below.This查询按CUSTOMERS.Source_System和ORGANIZATION_UNITS.ORGUNIT_CODE返回每个STATUSES.Name和组的计数。预期的结果是显示所有计数，包括DATA_SOURCE.Name=‘NA’..Due to子查询返回多行，查询不符合要求。

SELECT 
 (SELECT CASE WHEN DS.NAME = 'Interactive' THEN 'NA' ELSE CUS.SOURCE_SYSTEM END AS SOURCE_SYSTEM
  FROM CUSTOMERS CUS
  INNER JOIN  ALERT_CASE_HEADER ACH ON ACH.CUSTOMER_ID = CUS.CUSTOMER_ID
  INNER JOIN DATA_SOURCE DS ON ACH.DATASOURCE_ID = DS.ID),
  ORG.ORGUNIT_CODE AS ORGANIZATION_UNITS,
 SUM(
  CASE
    WHEN S.NAME = 'Pending' THEN 1 ELSE 0 
  END
  ) AS PENDING, 
 SUM(
  CASE
    WHEN S.NAME = 'New' THEN 1 ELSE 0 
  END
  ) AS NEW, 
 SUM(
  CASE
    WHEN S.NAME = 'Investigation' THEN 1 ELSE 0 
  END
) AS INVESTIGATION,
 SUM(
  CASE
     WHEN S.NAME = 'Escalated' THEN 1 ELSE 0
  END
) AS ESCALATED,
  SUM(
  CASE
     WHEN S.NAME = 'Recommend' THEN 1 ELSE 0
  END
) AS RECOMMEND,
  SUM(
  CASE
     WHEN S.NAME = 'Reopen' THEN 1 ELSE 0
  END
) AS REOPEN

 FROM
STATUSES S
JOIN ALERT_ITEM AI ON S.ID = AI.STATUS_ID
JOIN ALERT_CASE_HEADER ACH ON AI.ENTITY_KEY = ACH.ALERT_KEY
INNER JOIN ORGANIZATION_UNITS ORG ON ORG.ID = ACH.CUSTOMER_ORGUNIT_ID
INNER JOIN CUSTOMERS CUS ON CUS.CUSTOMER_ID = ACH.CUSTOMER_ID
WHERE AI.ENTITY_NAME = 'Active Alert'
GROUP BY ORG.ORGUNIT_CODE,CUS.SOURCE_SYSTEM;

Answer 1

我认为您可以直接进行一系列连接，然后使用CASE表达式来呈现源系统的最终值：

SELECT
    CASE WHEN ds.NAME = 'Interactive' THEN 'NIL' ELSE c.SOURCE_SYSTEM END AS SOURCE_SYSTEM
FROM CUSTOMERS c
INNER JOIN ALERT_CASE_HEADER ach
    ON ach.CUSTOMER_ID = c.CUSTOMER_ID
INNER JOIN DATA_SOURCE ds
    ON ach.DATASOURCE_ID = ds.ID

请注意，如果您怀疑记录可能由于与另一个表不匹配而被过滤掉，则可能希望生成上述两个联接LEFT JOIN中的一个或两个。此外，在您的原始查询中，您加入了两次ALERT_CASE_HEADER，这是不必要的。