- 主表关联三个 一对多子表 出现了笛卡尔积
现在有四张表
主表:
从表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