主表关联三个 一对多子表 出现了笛卡尔积

Question

1. 主表关联三个 一对多子表 出现了笛卡尔积
   现在有四张表
   主表：

从表1：

从表2：

从表3：

，
查询sql语句：

SELECT
    di.item_id itemId,
    di.item_name itemName,
    di.item_number itemNumber,
    wmb.act_status itemStatus,
    di.create_by proposer,
    wmb.todo_users pendingNode,
    wmb.task_name pendingNodeProcessor,
    to_char( di.create_time, 'yyyy-MM-DD HH24:MI:SS' ) submitTime,
    to_char( wmb.update_time, 'YYYY-MM-DD HH24:MI:SS' ) updateTime,
    di.item_type,
    di.item_jbr,
    di.item_sjdw,
    COALESCE ( SUM ( dv.amount ), '0.00' ) cumulativeAmountInspection,
    COALESCE ( SUM ( dfp.srje ), '0.00' ) accumulatedCollectionAmount,
    COALESCE ( SUM ( dic.contract_total_bhse ), '0.00' ) incomeTotalAmount,
    COALESCE ( SUM ( dbc.contract_total_bhse ), '0.00' ) buildTotalAmount,
    COALESCE ( SUM ( dbc.gq ), 0 ) totalDuration,
    SUM ( CASE WHEN dbc.contract_type = 1 THEN dbc.contract_total_bhse ELSE'0.00' END ) constructionUnitAmount,
    SUM ( CASE WHEN dbc.contract_type = 1 THEN dv.amount ELSE'0.00' END ) constructionUnitTotalAmount,
    SUM ( CASE WHEN dbc.contract_type = 2 THEN dbc.contract_total_bhse ELSE'0.00' END ) supervisionUnitAmount,
    SUM ( CASE WHEN dbc.contract_type = 2 THEN dv.amount ELSE'0.00' END ) supervisionUnitTotalAmount,
    SUM ( CASE WHEN dbc.contract_type = 3 THEN dbc.contract_total_bhse ELSE'0.00' END ) securityCooperativeUnitAmount,
    SUM ( CASE WHEN dbc.contract_type = 3 THEN dv.amount ELSE'0.00' END ) securityCooperativeUnitTotalAmount,
    SUM ( CASE WHEN dbc.contract_type = 4 THEN dbc.contract_total_bhse ELSE'0.00' END ) testingUnitAmount,
    SUM ( CASE WHEN dbc.contract_type = 4 THEN dv.amount ELSE'0.00' END ) testingUnitTotalAmount 
FROM
    dw_items di
    LEFT JOIN wf_my_business wmb ON CAST ( wmb.items_id AS INTEGER ) = di.item_id
    LEFT JOIN (SELECT * from dw_owner where del_flag = '0') dow ON dow.items_id = di.item_id
    LEFT JOIN (SELECT * from dw_build_contract where del_flag = '0' and status = '1') dbc ON dbc.item_id = di.item_id
    LEFT JOIN (SELECT * from dw_valuation where del_flag = '0' and status = '1') dv ON dv.build_contract_id = dbc."id"
  LEFT JOIN (SELECT * from dw_income_contract where del_flag = '0' and status = '1') dic ON dic.item_id = di.item_id
    LEFT JOIN (SELECT * from dw_fund_place where del_flag = '0') dfp ON dfp.income_contract_id = dic."id"
WHERE
    di.del_flag = '0'
GROUP BY
    di.item_id,
    di.item_name,
    di.item_number,
    wmb.act_status,
    di.create_by,
    di.create_time,
    wmb.update_time,
    wmb.todo_users,
    wmb.task_name 
    
ORDER BY
    di.item_id

Answer 1

🕖上午好，题主！目前：🟢[在线]
📢参考通义千问、由郭老师整理提供！
📣今日诗号：窗外日光弹指过，席间花影坐前移。

你遇到的问题是经典的多对多关系查询导致的笛卡尔积问题。解决这个问题的核心思路是：避免在 JOIN 子查询中进行多对多关联，而是将聚合操作放到子查询中完成。

以下是优化后的 SQL 语句：

SELECT
    di.item_id AS itemId,
    di.item_name AS itemName,
    di.item_number AS itemNumber,
    wmb.act_status AS itemStatus,
    di.create_by AS proposer,
    wmb.todo_users AS pendingNode,
    wmb.task_name AS pendingNodeProcessor,
    to_char( di.create_time, 'yyyy-MM-DD HH24:MI:SS' ) AS submitTime,
    to_char( wmb.update_time, 'YYYY-MM-DD HH24:MI:SS' ) AS updateTime,
    di.item_type,
    di.item_jbr,
    di.item_sjdw,
    COALESCE(dv_agg.cumulativeAmountInspection, '0.00') AS cumulativeAmountInspection,
    COALESCE(dfp_agg.accumulatedCollectionAmount, '0.00') AS accumulatedCollectionAmount,
    COALESCE(dic_agg.incomeTotalAmount, '0.00') AS incomeTotalAmount,
    COALESCE(dbc_agg.buildTotalAmount, '0.00') AS buildTotalAmount,
    COALESCE(dbc_agg.totalDuration, 0) AS totalDuration,
    COALESCE(dbc_agg.constructionUnitAmount, '0.00') AS constructionUnitAmount,
    COALESCE(dbc_agg.constructionUnitTotalAmount, '0.00') AS constructionUnitTotalAmount,
    COALESCE(dbc_agg.supervisionUnitAmount, '0.00') AS supervisionUnitAmount,
    COALESCE(dbc_agg.supervisionUnitTotalAmount, '0.00') AS supervisionUnitTotalAmount,
    COALESCE(dbc_agg.securityCooperativeUnitAmount, '0.00') AS securityCooperativeUnitAmount,
    COALESCE(dbc_agg.securityCooperativeUnitTotalAmount, '0.00') AS securityCooperativeUnitTotalAmount,
    COALESCE(dbc_agg.testingUnitAmount, '0.00') AS testingUnitAmount,
    COALESCE(dbc_agg.testingUnitTotalAmount, '0.00') AS testingUnitTotalAmount
FROM
    dw_items di
LEFT JOIN wf_my_business wmb ON CAST ( wmb.items_id AS INTEGER ) = di.item_id
LEFT JOIN (
    SELECT 
        item_id,
        COALESCE(SUM(amount), '0.00') AS cumulativeAmountInspection 
    FROM dw_valuation 
    WHERE del_flag = '0' AND status = '1' 
    GROUP BY item_id
) dv_agg ON dv_agg.item_id = di.item_id
LEFT JOIN (
    SELECT 
        dic.item_id,
        COALESCE(SUM(dfp.srje), '0.00') AS accumulatedCollectionAmount 
    FROM dw_income_contract dic
    LEFT JOIN dw_fund_place dfp ON dfp.income_contract_id = dic."id"
    WHERE dic.del_flag = '0' AND dic.status = '1' AND dfp.del_flag = '0'
    GROUP BY dic.item_id
) dfp_agg ON dfp_agg.item_id = di.item_id
LEFT JOIN (
    SELECT 
        item_id,
        COALESCE(SUM(contract_total_bhse), '0.00') AS incomeTotalAmount 
    FROM dw_income_contract 
    WHERE del_flag = '0' AND status = '1' 
    GROUP BY item_id
) dic_agg ON dic_agg.item_id = di.item_id
LEFT JOIN (
    SELECT
        item_id,
        COALESCE(SUM(contract_total_bhse), '0.00') AS buildTotalAmount,
        COALESCE(SUM(gq), 0) AS totalDuration,
        SUM(CASE WHEN contract_type = 1 THEN contract_total_bhse ELSE '0.00' END) AS constructionUnitAmount,
        SUM(CASE WHEN contract_type = 2 THEN contract_total_bhse ELSE '0.00' END) AS supervisionUnitAmount,
        SUM(CASE WHEN contract_type = 3 THEN contract_total_bhse ELSE '0.00' END) AS securityCooperativeUnitAmount,
        SUM(CASE WHEN contract_type = 4 THEN contract_total_bhse ELSE '0.00' END) AS testingUnitAmount
    FROM dw_build_contract
    WHERE del_flag = '0' AND status = '1'
    GROUP BY item_id
) dbc_agg ON dbc_agg.item_id = di.item_id
WHERE
    di.del_flag = '0'
ORDER BY
    di.item_id, 90;

优化思路解释:

1. 子查询聚合: 将 dw_valuation, dw_fund_place, dw_income_contract, dw_build_contract 这四个子表分别进行聚合操作，计算出每个 item_id 对应的汇总数据。
2. 关联主表: 将聚合后的子查询结果与主表 dw_items 进行关联，避免了在 JOIN 子查询中进行多对多关联。
3. 简化代码: 去掉了冗余的子查询条件 SELECT * from 。
4. 字段命名: 使用了更清晰的别名来标识聚合后的字段。

通过以上优化，可以有效避免笛卡尔积问题，提高查询效率。