【优秀SQL实践】合并相同条件的字段到同一行

SELECT DISTINCT a.dueDate,a.repayAmt,b.collectAmt FROM (SELECT repay_corp_no,DATE_FORMAT(due_date, ‘%Y-%m-%d’) dueDate,SUM(unpaid_amt) repayAmt FROM t_repay_plan WHERE repay_corp_no=‘CP201908211016448799’ GROUP BY due_date) a LEFT JOIN (SELECT collection_corp_no,DATE_FORMAT(due_date, ‘%Y-%m-%d’) dueDate,SUM(unpaid_amt) collectAmt FROM t_repay_plan WHERE collection_corp_no = ‘CP201908211016448799’ GROUP BY due_date) b ON a.dueDate = b.dueDate UNION ALL SELECT DISTINCT b.dueDate,a.repayAmt,b.collectAmt FROM (SELECT repay_corp_no,DATE_FORMAT(due_date, ‘%Y-%m-%d’) dueDate,SUM(unpaid_amt) repayAmt FROM t_repay_plan WHERE repay_corp_no=‘CP201908211016448799’ GROUP BY due_date) a RIGHT JOIN (SELECT collection_corp_no,DATE_FORMAT(due_date, ‘%Y-%m-%d’) dueDate,SUM(unpaid_amt) collectAmt FROM t_repay_plan WHERE collection_corp_no = ‘CP201908211016448799’ GROUP BY due_date) b ON a.dueDate = b.dueDate

SELECT DISTINCT dueDate,(CASE WHEN repayAmt IS NULL THEN 0 ELSE repayAmt END) repayAmt, (CASE WHEN collectAmt IS NULL THEN 0 ELSE collectAmt END )collectAmt FROM (SELECT repay.dueDate,repay.repayAmt,collection.collectAmt FROM (SELECT repay_corp_no,DATE_FORMAT(due_date, ‘%Y-%m-%d’) dueDate,SUM(unpaid_amt) repayAmt FROM t_repay_plan WHERE repay_corp_no=‘CP201908211016448799’ GROUP BY due_date) repay LEFT JOIN (SELECT collection_corp_no,DATE_FORMAT(due_date, ‘%Y-%m-%d’) dueDate,SUM(unpaid_amt) collectAmt FROM t_repay_plan WHERE collection_corp_no = ‘CP201908211016448799’ GROUP BY due_date) collection ON repay.dueDate = collection.dueDate UNION ALL SELECT DISTINCT collection.dueDate,repay.repayAmt,collection.collectAmt FROM (SELECT repay_corp_no,DATE_FORMAT(due_date, ‘%Y-%m-%d’) dueDate,SUM(unpaid_amt) repayAmt FROM t_repay_plan WHERE repay_corp_no=‘CP201908211016448799’ GROUP BY due_date) repay RIGHT JOIN (SELECT collection_corp_no,DATE_FORMAT(due_date, ‘%Y-%m-%d’) dueDate,SUM(unpaid_amt) collectAmt FROM t_repay_plan WHERE collection_corp_no = ‘CP201908211016448799’ GROUP BY due_date) collection ON repay.dueDate = collection.dueDate) report

最终完美版