如何用SQL对比两张按日期分组求和的同结构表，缺失日期补0？

现有分组查询结果 #

表A #

执行分组求和SQL：

SELECT SUM(amount) AS amount, DATE_FORMAT(date,'%m %y') AS date FROM `TableA` GROUP BY DATE_FORMAT(date,'%m%y');

得到结果：

表B #

执行分组求和SQL：

SELECT SUM(amount) AS amount, DATE_FORMAT(date,'%m %y') AS date FROM `TableB` GROUP BY DATE_FORMAT(date,'%m%y');

得到结果：

生成对比汇总表的SQL方案 #

通过获取所有唯一日期，再左连接两张表的分组结果，用COALESCE将缺失值补0，最终按日期排序：

WITH all_dates AS (
    SELECT date FROM (
        SELECT DATE_FORMAT(date,'%m %y') AS date FROM TableA
        UNION
        SELECT DATE_FORMAT(date,'%m %y') AS date FROM TableB
    ) AS dates
)
SELECT
    COALESCE(a.amount, 0) AS `amount(Table A)`,
    COALESCE(b.amount, 0) AS `amount(Table B)`,
    ad.date AS `date`
FROM all_dates ad
LEFT JOIN (
    SELECT SUM(amount) AS amount, DATE_FORMAT(date,'%m %y') AS date FROM TableA GROUP BY DATE_FORMAT(date,'%m%y')
) a ON ad.date = a.date
LEFT JOIN (
    SELECT SUM(amount) AS amount, DATE_FORMAT(date,'%m %y') AS date FROM TableB GROUP BY DATE_FORMAT(date,'%m%y')
) b ON ad.date = b.date
ORDER BY STR_TO_DATE(ad.date, '%m %y');

最终输出结果 #

内容的提问来源于stack exchange，提问作者Kyaw Wint Thu