mySql联合两个查询结果用UNION或者UNION ALL

来源：互联网发布：如何更换网络节点编辑：程序博客网时间：2024/05/18 21:07

项目开发中由于业务的需求，可能将两个业务实体放在两张表（或在两个查询结果）中，但有时候项目需要将这两张表（或查询结果）的所有记录联合起来，就可以用到UNION或UNION ALL，其中UNION ALL是不去重复，而UNION将把结果之和去重（类似于distinct）。
第一个查询：

    SELECT cv.*    FROM study_card AS scd     LEFT JOIN stuc_course AS sce ON scd.id=sce.stuc_id    LEFT JOIN course_view AS cv ON cv.id=sce.course_id    WHERE scd.user_id='08fdeff8ca884d61a2c88c228f7d7404' AND scd.is_used=1

查询结果如图：
这里写图片描述

加上distinct后，

    SELECT DISTINCT cv.*    FROM study_card AS scd     LEFT JOIN stuc_course AS sce ON scd.id=sce.stuc_id    LEFT JOIN course_view AS cv ON cv.id=sce.course_id    WHERE scd.user_id='08fdeff8ca884d61a2c88c228f7d7404' AND scd.is_used=1

查询结果如图：
这里写图片描述

第二个查询：

    SELECT cv.*    FROM orders AS o    LEFT JOIN course_view AS cv ON o.course_id=cv.id    WHERE o.user_id='08fdeff8ca884d61a2c88c228f7d7404' AND o.is_finished=1

这里写图片描述

联合查询，用UNION ALL：

    SELECT cv.*    FROM study_card AS scd     LEFT JOIN stuc_course AS sce ON scd.id=sce.stuc_id    LEFT JOIN course_view AS cv ON cv.id=sce.course_id    WHERE scd.user_id='08fdeff8ca884d61a2c88c228f7d7404' AND scd.is_used=1     UNION ALL    SELECT cv.*    FROM orders AS o    LEFT JOIN course_view AS cv ON o.course_id=cv.id    WHERE o.user_id='08fdeff8ca884d61a2c88c228f7d7404' AND o.is_finished=1

查询结果：
这里写图片描述

使用UNION：

    SELECT cv.*    FROM study_card AS scd     LEFT JOIN stuc_course AS sce ON scd.id=sce.stuc_id    LEFT JOIN course_view AS cv ON cv.id=sce.course_id    WHERE scd.user_id='08fdeff8ca884d61a2c88c228f7d7404' AND scd.is_used=1     UNION     SELECT cv.*    FROM orders AS o    LEFT JOIN course_view AS cv ON o.course_id=cv.id    WHERE o.user_id='08fdeff8ca884d61a2c88c228f7d7404' AND o.is_finished=1

查询结果是：
这里写图片描述

使用联合查询的注意事项：
列数，列类型必须相同。

除此之外，联合查询可以加上分页LIMIT，如：

    SELECT cv.*    FROM study_card AS scd     LEFT JOIN stuc_course AS sce ON scd.id=sce.stuc_id    LEFT JOIN course_view AS cv ON cv.id=sce.course_id    WHERE scd.user_id='08fdeff8ca884d61a2c88c228f7d7404' AND scd.is_used=1     UNION     SELECT cv.*    FROM orders AS o    LEFT JOIN course_view AS cv ON o.course_id=cv.id    WHERE o.user_id='08fdeff8ca884d61a2c88c228f7d7404' AND o.is_finished=1    LIMIT 0,3

0 0