时间戳

首页 > 解决方案 > 在 Oracle 中加入多个 CTE 时遇到重复问题

oracle - 在 Oracle 中加入多个 CTE 时遇到重复问题

问题描述

我正在创建三个 CTE，我最终希望将它们连接在一起以创建一个汇总表。三个表中的两个包含相同的列，只是它们相加的项目不同（报价与新业务）。另一个表包含两个汇总列，报告排序和总成本，以及另一个字段的子类别，报告类型，它汇总到 LOB。

当我尝试将它们全部连接在一起并“重新总结”这些字段时，问题就出现了。出于某种原因，即使我在 select 子句中使用 DISTINCT 并且我认为我正确地加入了所有内容，我也会得到重复的值。

当我从 Total_Quotes 和 Total_NB 中删除 SUM 时，代码有效；但是，我希望将一条只有一个值的行相加就可以了。显然情况并非如此。

WITH
MONTHLY_INVOICES AS
(SELECT DISTINCT
EXTRACT(YEAR FROM INVOICE_DATE) AS YEAR
,EXTRACT(MONTH FROM INVOICE_DATE) AS MONTH
,STATE
,REPORT_TYPE
,CASE WHEN REPORT_TYPE IN ('ADD', 'CCA', 'CLA', 'MVR', 'VIN') THEN 'AUTO'
      WHEN REPORT_TYPE IN ('CCP', 'CLP') THEN 'HOME'
      WHEN REPORT_TYPE IN ('DPF', 'NCF') THEN 'OTHER'
      END AS LOB
,SUM(TOTAL_ORDERS) AS TOTAL_ORDERS
,SUM(TOTAL_COST) AS TOTAL_COST
FROM
KAHJZK_VENDOR_INVOICE_TOTALS
GROUP BY
EXTRACT(YEAR FROM INVOICE_DATE)
,EXTRACT(MONTH FROM INVOICE_DATE)
,STATE
,REPORT_TYPE
,CASE WHEN REPORT_TYPE IN ('ADD', 'CCA', 'CLA', 'MVR', 'VIN') THEN 'AUTO'
      WHEN REPORT_TYPE IN ('CCP', 'CLP') THEN 'HOME'
      WHEN REPORT_TYPE IN ('DPF', 'NCF') THEN 'OTHER'
      END)

,MONTHLY_QT AS 
(SELECT DISTINCT
EXTRACT(YEAR FROM REPORT_DATE) AS YEAR
,EXTRACT(MONTH FROM REPORT_DATE) AS MONTH
,STATE
,LOB
,SUM(TOTAL_QUOTES) AS TOTAL_QTS
FROM KAHJZK_DAILY_QT_VOL
WHERE
EXTRACT(YEAR FROM REPORT_DATE) >= '2019'
GROUP BY
EXTRACT(YEAR FROM REPORT_DATE)
,EXTRACT(MONTH FROM REPORT_DATE)
,STATE
,LOB)

,MONTHLY_NB AS 
(SELECT DISTINCT
EXTRACT(YEAR FROM REPORT_DATE) AS YEAR
,EXTRACT(MONTH FROM REPORT_DATE) AS MONTH
,STATE
,LOB
,SUM(TOTAL_POLICIES) AS TOTAL_NB
FROM KAHJZK_DAILY_NB_VOL
WHERE
EXTRACT(YEAR FROM REPORT_DATE) >= '2019'
GROUP BY
EXTRACT(YEAR FROM REPORT_DATE)
,EXTRACT(MONTH FROM REPORT_DATE)
,STATE
,LOB)


SELECT DISTINCT
A.YEAR
,A.MONTH
,A.STATE
,A.LOB
,SUM(A.TOTAL_ORDERS)
,SUM(A.TOTAL_COST)
,SUM(B.TOTAL_QTS)
,SUM(C.TOTAL_NB)
FROM
MONTHLY_INVOICES A
LEFT OUTER JOIN MONTHLY_QT B
ON A.YEAR = B.YEAR
AND A.MONTH = B.MONTH
AND A.STATE = B.STATE
AND A.LOB = B.LOB
LEFT OUTER JOIN MONTHLY_NB C
ON A.YEAR = C.YEAR
AND A.MONTH = C.MONTH
AND A.STATE = C.STATE
AND A.LOB = C.LOB
GROUP BY
A.YEAR
,A.MONTH
,A.STATE
,A.LOB
ORDER BY
3,4,1,2;

当我将最后一部分更改为以下内容时，代码有效。：

SELECT DISTINCT
A.YEAR
,A.MONTH
,A.STATE
,A.LOB
,SUM(A.TOTAL_ORDERS)
,SUM(A.TOTAL_COST)
,B.TOTAL_QTS
,C.TOTAL_NB
FROM
MONTHLY_INVOICES A
LEFT OUTER JOIN MONTHLY_QT B
ON A.YEAR = B.YEAR
AND A.MONTH = B.MONTH
AND A.STATE = B.STATE
AND A.LOB = B.LOB
LEFT OUTER JOIN MONTHLY_NB C
ON A.YEAR = C.YEAR
AND A.MONTH = C.MONTH
AND A.STATE = C.STATE
AND A.LOB = C.LOB
GROUP BY
A.YEAR
,A.MONTH
,A.STATE
,A.LOB
,B.TOTAL_QTS
,C.TOTAL_NB
ORDER BY
3,4,1,2;

标签： oracle

解决方案

  1. 您必须提供一个或多个列来唯一标识每个表中的每一行。
  2. 您必须提供一种方法，通过该方法可以将一个表中唯一标识的行连接到另一个表中的唯一标识行。

未能解决上面列出的两个项目将导致结果集是选择标准的笛卡尔积（也称为叉积）。

编辑（回复评论 1）
distinct 不会影响连接产生的叉积。如果您得到一个叉积，那么您可能在连接的一侧选择了与另一侧的任何行都不匹配的行。

举个例子，试试select a.field, b.field from table 1 a, table 2 b （即不加入，只列出两个表），你会得到一个叉积。

left join如果您在添加子句后没有重复，这可能是原因 。

推荐阅读