Oracle多表聚合查询：避免数据膨胀并保留Having条件的实现

你遇到的核心问题是直接关联sale和quote表会产生笛卡尔积——比如某个零件有3条销售记录和2条报价记录，inner join后会生成3*2=6条重复数据，导致所有聚合计算（sum、count）结果失真。而拆分两个独立查询又没法同时对销售和报价的指标统一应用Having过滤条件。

针对这个问题，最合理的解法是先分别对销售和报价数据按part_id做聚合，再将聚合后的结果与part表关联，这样每个零件只会对应一行销售统计和一行报价统计，彻底避免数据膨胀，同时能统一使用Having过滤所有指标。

以下是符合Oracle语法的正确查询语句：

SELECT
    p.number,
    NVL(s.sales_amt_total, 0) AS sales_amt_total,
    NVL(s.sales_qty_total, 0) AS sales_qty_total,
    NVL(s.sales_count, 0) AS sales_count,
    NVL(s.cost_total, 0) AS cost_total,
    NVL(q.quotes_amt_total, 0) AS quotes_amt_total,
    NVL(q.quotes_qty_total, 0) AS quotes_qty_total,
    NVL(q.quotes_count, 0) AS quotes_count
FROM
    part p
LEFT JOIN (
    -- 先聚合销售数据，每个零件仅保留一行统计结果
    SELECT
        part_id,
        SUM(amt * qty) AS sales_amt_total,
        SUM(qty) AS sales_qty_total,
        COUNT(sale_id) AS sales_count,
        SUM(qty * cost) AS cost_total
    FROM sale
    GROUP BY part_id
) s ON s.part_id = p.part_id
LEFT JOIN (
    -- 先聚合报价数据，每个零件仅保留一行统计结果
    SELECT
        part_id,
        SUM(amt * qty) AS quotes_amt_total,
        SUM(qty) AS quotes_qty_total,
        COUNT(quote_id) AS quotes_count
    FROM quote
    GROUP BY part_id
) q ON q.part_id = p.part_id
GROUP BY
    p.number,
    NVL(s.sales_amt_total, 0),
    NVL(s.sales_qty_total, 0),
    NVL(s.sales_count, 0),
    NVL(s.cost_total, 0),
    NVL(q.quotes_amt_total, 0),
    NVL(q.quotes_qty_total, 0),
    NVL(q.quotes_count, 0)
HAVING
    NVL(s.sales_amt_total, 0) < ? AND NVL(s.sales_amt_total, 0) > ?
    AND NVL(s.sales_qty_total, 0) < ? AND NVL(s.sales_qty_total, 0) > ?
    AND NVL(s.sales_count, 0) < ? AND NVL(s.sales_count, 0) > ?
    AND NVL(s.cost_total, 0) < ? AND NVL(s.cost_total, 0) > ?
    AND NVL(q.quotes_amt_total, 0) < ? AND NVL(q.quotes_amt_total, 0) > ?
    AND NVL(q.quotes_qty_total, 0) < ? AND NVL(q.quotes_qty_total, 0) > ?
    AND NVL(q.quotes_count, 0) < ? AND NVL(q.quotes_count, 0) > ?
ORDER BY p.number;

关键细节说明： #

1. 子查询预聚合：通过两个独立子查询分别计算每个零件的销售、报价统计，确保每个part_id在子查询中仅返回一行结果，从根源上避免笛卡尔积导致的数据膨胀。
2. LEFT JOIN + NVL处理空值：如果某个零件只有销售记录没有报价，或者只有报价没有销售，LEFT JOIN能保留该零件的记录，再用NVL()将NULL值转为0，保证结果的完整性和可读性。
3. GROUP BY与HAVING适配Oracle语法：外层GROUP BY是为了满足Oracle的语法要求（所有非聚合字段必须出现在GROUP BY中），而HAVING可以直接过滤所有预聚合后的指标，实现你需要的多条件联合筛选。

示例输出结果： #

number | sales_amt_total | sales_qty_total | sales_count | cost_total | quotes_amt_total | quotes_qty_total | quotes_count
--------------------------------------------------------------------------------------------------------------------------------
P1     | 9999999         | 9999999         | 9999999     | 9999999    | 8888888          | 8888888          | 8888888
P2     | 7777777         | 7777777         | 7777777     | 7777777    | 0                | 0                | 0
P3     | 0               | 0               | 0           | 0          | 6666666          | 6666666          | 6666666

内容的提问来源于stack exchange，提问作者ryvantage