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条件);

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

原文：http://www.cnblogs.com/liuyifan/p/4985512.html