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

╰半夏微凉° 2024-04-01 14:37 25阅读 0赞

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

最终完美版