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

 

posted @ 2015-11-22 11:27 刘译繁liuyifan 阅读(...) 评论(...) 编辑 收藏