oracle - 合并多个选择查询并添加默认值

Question

我正在使用多项选择来获取策略在过去 10 分钟内生成的证书。如果尚未颁发证书，则仍列出默认值为 0 的策略。如果该策略有证书问题，则显示使用该策略颁发的证书数量。

使用此查询收集策略 ID

  SELECT POLICY_ID FROM POLICY_DOMAIN_PATH_MAP_TBL
  MINUS
  SELECT POLICY_ID FROM POLICY_DOMAIN_PATH_MAP_TBL WHERE ACTION=1 AND PATH_ID IN (-4,-2)

那么这将计算每个策略生成的证书。

SELECT
     "A3"."POLICY_NAME"
     count(*)
FROM
    "POLICY_TBL"               "A3",
    "CERTIFICATE_TBL"          "A2",
    "CERT_STATUS_CHECK_TBL"    "A1"
WHERE
        "A2"."POLICY_ID" = "A3"."POLICY_ID"
    AND "A2"."ISSUE_DATE" >= sysdate - 10/1440
    AND "A1"."CERT_SERIAL_NUM" = "A2"."CERT_SERIAL_NUM"
    AND "A1"."STATUS" = '0'
GROUP BY
    "A3"."POLICY_NAME"

问题是这个最新的查询只列出了生成证书的策略，但我需要在第一个查询中列出所有策略。如果没有颁发证书，则默认为 0 如果生成了证书，则显示计数。如何调整 where 子句，以便它在第一个查询中搜索策略 id 侦听，如果没有为该策略 id 找到零结果，则添加默认值。

标签： oracleplsql

Answer 1

您的查询中缺少某些内容 - 我没有看到 A3 的连接谓词。它应该是类似的东西AND A2.POLICY_ID = A3.POLICY_ID。

无论如何，您只需在此处添加外部联接：

SELECT
     "A3"."POLICY_NAME"
     count("A1"."CERT_SERIAL_NUM")
FROM
    "POLICY_TBL"               "A3",
    "CERTIFICATE_TBL"          "A2",
    "CERT_STATUS_CHECK_TBL"    "A1"
WHERE
        "A3"."POLICY_ID" = ???
    AND "A3"."POLICY_ID" = "A2"."POLICY_ID"(+)
    AND "A2"."ISSUE_DATE"(+) >= sysdate - 10/1440
    AND "A1"."CERT_SERIAL_NUM"(+) = "A2"."CERT_SERIAL_NUM"
    AND "A1"."STATUS"(+) = '0'
GROUP BY
    "A3"."POLICY_NAME"

或在 ANSI：

SELECT
     "A3"."POLICY_NAME"
     count("A1"."CERT_SERIAL_NUM")
FROM
    "POLICY_TBL"               "A3"
    left join "CERTIFICATE_TBL"          "A2"
        on "A3"."POLICY_ID" = "A2"."POLICY_ID"
        and "A2"."ISSUE_DATE" >= sysdate - 10/1440
    left join "CERT_STATUS_CHECK_TBL"    "A1"
        on "A1"."CERT_SERIAL_NUM" = "A2"."CERT_SERIAL_NUM"
        AND "A1"."STATUS" = '0'
WHERE
        "A3"."POLICY_ID" = ???
GROUP BY
    "A3"."POLICY_NAME"

更新： 啊，您更改了问题中的查询。以前它包含一个由policy_id 表示的谓词。既然你不需要它，你需要加入你的结果：

SELECT
     "A3"."POLICY_NAME"
     count("A1"."CERT_SERIAL_NUM")
FROM
    (
      SELECT POLICY_ID FROM POLICY_DOMAIN_PATH_MAP_TBL
      MINUS
      SELECT POLICY_ID FROM POLICY_DOMAIN_PATH_MAP_TBL WHERE ACTION=1 AND PATH_ID IN (-4,-2)
    ) A0
    join "POLICY_TBL"               "A3"
        on A0.POLICY_ID = "A3"."POLICY_ID"
    left join "CERTIFICATE_TBL"          "A2"
        on "A3"."POLICY_ID" = "A2"."POLICY_ID"
        and "A2"."ISSUE_DATE" >= sysdate - 10/1440
    left join "CERT_STATUS_CHECK_TBL"    "A1"
        on "A1"."CERT_SERIAL_NUM" = "A2"."CERT_SERIAL_NUM"
        AND "A1"."STATUS" = '0'
GROUP BY
    "A3"."POLICY_NAME"