mysql不支持full join的另一种解决办法 和根据多个表中的相同分组来连接查询

先看两张表：

1. user表：

2. animal表：

现在我想要查询各省市区对应的人名和动物名，即根据省市区来连接两张表。

考虑到user表中有的省市区可能在animal表中没有，animal表中有的省市区user表中可能没有，所以两张表需要全连接。

但是mysql本身不支持全连接，除了用union连接左连接查询和右连接查询外，现提供另外一种有效的解决方案。

1) 根据两表的省市区建立视图，视图中省市区组合不重复：

create view v as
SELECT u.province_id,u.city_id,u.district_id from user u
union 
SELECT a.province_id,a.city_id,a.district_id from animal a

建好的视图：

 

2）这样视图中就有了全部的不重复的省市区组合，然后user表和animal表和这个视图根据省市区条件左连接即可：

SELECT
    v.province_id,
    v.city_id,
    v.district_id,
    u.`name`,
    u.money,
    a.`name`,
    a.amount
FROM
    v
LEFT JOIN USER u ON v.province_id = u.province_id
AND v.city_id = u.city_id
AND v.district_id = u.district_id
LEFT JOIN animal a ON v.province_id = a.province_id
AND v.city_id = a.city_id
AND v.district_id = a.district_id

查询出来的结果：

到此 查询各省市区对应的人名和动物名 完成。

 

现在我想查询各省市区对应的钱数（money_sum）和动物数（amount_sum）:

sql如下：

SELECT
    v.province_id,
    v.city_id,
    v.district_id,
    u.money_sum,
    a.amount_sum
FROM
    v
LEFT JOIN (
    SELECT
        u.province_id,
        u.city_id,
        u.district_id,
        sum(u.money) 'money_sum'  -- 分组统计值
    FROM
        USER u
    GROUP BY
        u.province_id,
        u.city_id,
        u.district_id
) u ON v.province_id = u.province_id
AND v.city_id = u.city_id
AND v.district_id = u.district_id
LEFT JOIN (
    SELECT
        a.province_id,
        a.city_id,
        a.district_id,
        sum(a.amount) 'amount_sum'  -- 分组统计值
    FROM
        animal a
    GROUP BY
        a.province_id,
        a.city_id,
        a.district_id
) a ON v.province_id = a.province_id
AND v.city_id = a.city_id
AND v.district_id = a.district_id

-- 注：1. 视图存放所有不重复的分组情况 2. 必须先统计再连接，即：先求出 分组统计值，然后再左连接；如果先左连接再求统计值，统计值会比实际值大，因为左连接后数据有重复

查询出来的结果：

可以验证一下查询结果是否正确：

money_sum一列的和为15，user表中所有money的和也为15。

amount_sum一列的和为28，animal表中所有的amount值的和也为28。

 

总结：

mysql求全连接，可以先创建所有连接条件的并集，然后其他表都和这个并集进行左连接，即可得出全连接。

 

附：通常的解决方案（适用于语句较少时）

MySQL Full Join的实现

MySQL Full Join的实现 因为MySQL不支持FULL JOIN,下面是替代方法
left join + union(可去除重复数据)+ right join

两张表时：
select * from A left join B on A.id = B.id (where 条件）
union
select *from A right join B on A.id = B.id （where条件);