Oracle多对多JOIN转一对多关联查询结果不符解决方案咨询

你遇到的问题根源是两表同PRODUCT_ID均存在多行记录，直接JOIN会生成笛卡尔积，导致每个ORDERS行分组后都能拿到全量聚合结果。可以通过窗口函数标记同PRODUCT_ID下的首行，仅对首行返回聚合值即可，调整后的SQL如下：

WITH prod_sum AS (
    -- 预先计算每个产品ID的总产出，避免JOIN笛卡尔积干扰聚合结果
    SELECT 
        PRODUCT_ID,
        SUM(PRODUCED_AMOUNT) AS TOTAL_PRODUCED
    FROM PRODUCTION
    GROUP BY PRODUCT_ID
),
order_rn AS (
    -- 给同PRODUCT_ID下的订单行加行号，这里按PRODUCT字段排序保证首行是你要的第一条
    SELECT 
        "ORDER", -- ORDER是Oracle关键字，作为列名需要用双引号转义
        AMOUNT_TO_PRODUCE,
        PRODUCT,
        PRODUCT_ID,
        ROW_NUMBER() OVER(PARTITION BY PRODUCT_ID ORDER BY PRODUCT) AS RN
    FROM ORDERS
)
SELECT 
    o."ORDER",
    o.AMOUNT_TO_PRODUCE,
    CASE WHEN o.RN = 1 THEN p.TOTAL_PRODUCED ELSE 0 END AS PRODUCED_AMOUNT,
    o.PRODUCT
FROM order_rn o
LEFT JOIN prod_sum p ON o.PRODUCT_ID = p.PRODUCT_ID
ORDER BY o.PRODUCT_ID, o.RN

补充说明 #

• 如果你对首行的判定规则不是按PRODUCT字段排序，可以自行调整ROW_NUMBER()里的ORDER BY部分，比如按订单创建时间、行ID等字段排序
• 原SQL中的ORDER.AMOUNT_TO_PRODUCE属于笔误，需要修正为ORDERS.AMOUNT_TO_PRODUCE
• 采用CTE预计算的方式可以避免多表JOIN后再分组带来的性能损耗，数据量较大时执行效率更高

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