mysql横向合并两个表_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