Mysql 查询两张表 横向合并实现类似full join

由于本人项目要求，统计一张表里用户已读、未读状态的总数，具体业务逻辑比较复杂，所以用简单两张表实现横向合并：

A 表示信息未读数量统计：

B 表示已读数量统计：

最终结果表：

现在要将A B表合并成一张表，由于本人比较懒，能用一个sql解决的，绝对不会用两个sql实现，具体分析情况如下：

如下情况会导致数据丢失，因为要统计id有数据的
SELECT * FROM A a LEFT JOIN B b ON a.id = b.id;

SELECT * FROM A a RIGHT JOIN B b ON a.id = b.id;

-- 拼接也不对，没法读取
SELECT * FROM A a UNION all SELECT * FROM B b

-- 查询A表已读未读数据 假设cm_1为已读数量 cm 未读
SELECT a.id,a.cm,0 as cm_1 FROM A a

-- 查询B表已读未读数据 假设cm_0为未读数量 cm 已读
SELECT b.id,b.cm,0 as cm_0 FROM B b

-- 用UNION ALL合并 发现哪里不对了吗，对，数据只会拼接
SELECT a.id,a.cm,0 as cm_1 FROM A a UNION ALL SELECT b.id,b.cm,0 as cm_0 FROM B b

-- 解决方法，用另外的参数代cm_0，cm_1替cm, 首先实现如下结果
SELECT c.id,SUM(c.cm_1) as cm_0,c.cm as cm_1 FROM (SELECT a.id,a.cm,0 as cm_1 FROM A a) c GROUP BY c.id
UNION ALL
SELECT d.id,d.cm as cm_0,SUM(d.cm_0) as cm_1 FROM (SELECT b.id,b.cm,0 as cm_0 FROM B b) d GROUP BY d.id

UNION ALL

=

最后，利用sum 将数据合并在一起，在自己dto中定义已读未读参数接收就可以了

SELECT id, sum(cm_0) as cm_1,sum(cm_1) as cm_0 FROM(
SELECT c.id,SUM(c.cm_1) as cm_0,c.cm as cm_1 FROM (SELECT a.id,a.cm,0 as cm_1 FROM A a) c GROUP BY c.id
UNION ALL
SELECT d.id,d.cm as cm_0,SUM(d.cm_0) as cm_1 FROM (SELECT b.id,b.cm,0 as cm_0 FROM B b) d GROUP BY d.id
) t GROUP BY t.id