如何将SQL联合查询结果并排展示为多列？

我有两条使用UNION的SELECT语句，希望将查询结果以多列形式并排展示，但当前UNION的结果是把两组数据上下拼接，不符合预期。

尝试的SQL语句 #

SELECT COUNT(emp.id) num1,dep.departmentname
from tblemployees emp
JOIN tbldepartments dep on dep.id = emp.OriginalDepartment
WHERE
emp.OriginalDepartment IN (208,168,209,189,157)
and emp.JobType in (41,51,52,53,54) 
AND emp.`Status`NOT IN (11,20,21,26,17) 
AND emp.retiredate > CURDATE()
GROUP BY dep.departmentname
union
SELECT COUNT(emp.id) num2,dep.departmentname
from tblemployees emp
JOIN tbldepartments dep on dep.id = emp.OriginalDepartment
WHERE
emp.OriginalDepartment IN (208,168,209,189,157)
and emp.JobType IN (1,6,7,8,9,11,26,32,33,34,36,43,45,46,47,48,49,55) 
AND emp.`Status`NOT IN (11,20,21,26,17) 
AND emp.retiredate > CURDATE()
GROUP BY dep.departmentname

当前查询结果 #

期望的查询结果 #

解决方案 #

方法1：条件聚合（高效推荐） #

两个查询的过滤条件大部分重合，仅JobType范围不同，用CASE WHEN在单次表扫描中完成两组统计：

SELECT
    COUNT(CASE WHEN emp.JobType IN (41,51,52,53,54) THEN emp.id END) AS num1,
    COUNT(CASE WHEN emp.JobType IN (1,6,7,8,9,11,26,32,33,34,36,43,45,46,47,48,49,55) THEN emp.id END) AS num2,
    dep.departmentname
FROM tblemployees emp
JOIN tbldepartments dep ON dep.id = emp.OriginalDepartment
WHERE
    emp.OriginalDepartment IN (208,168,209,189,157)
    AND emp.`Status` NOT IN (11,20,21,26,17) 
    AND emp.retiredate > CURDATE()
GROUP BY dep.departmentname;

无对应数据的列会自动显示NULL，与期望结果的空值效果一致。

方法2：子查询关联 #

若需保留两个独立查询结构，可按部门名关联结果：

SELECT 
    t1.num1, 
    t2.num2, 
    COALESCE(t1.departmentname, t2.departmentname) AS departmentname
FROM (
    SELECT COUNT(emp.id) num1, dep.departmentname
    FROM tblemployees emp
    JOIN tbldepartments dep ON dep.id = emp.OriginalDepartment
    WHERE
        emp.OriginalDepartment IN (208,168,209,189,157)
        AND emp.JobType IN (41,51,52,53,54) 
        AND emp.`Status` NOT IN (11,20,21,26,17) 
        AND emp.retiredate > CURDATE()
    GROUP BY dep.departmentname
) t1
FULL OUTER JOIN (
    SELECT COUNT(emp.id) num2, dep.departmentname
    FROM tblemployees emp
    JOIN tbldepartments dep ON dep.id = emp.OriginalDepartment
    WHERE
        emp.OriginalDepartment IN (208,168,209,189,157)
        AND emp.JobType IN (1,6,7,8,9,11,26,32,33,34,36,43,45,46,47,48,49,55) 
        AND emp.`Status` NOT IN (11,20,21,26,17) 
        AND emp.retiredate > CURDATE()
    GROUP BY dep.departmentname
) t2 ON t1.departmentname = t2.departmentname;

注：MySQL不支持FULL OUTER JOIN，可改用LEFT JOIN+UNION RIGHT JOIN模拟：

SELECT t1.num1, t2.num2, t1.departmentname
FROM (第一个子查询) t1
LEFT JOIN (第二个子查询) t2 ON t1.departmentname = t2.departmentname
UNION
SELECT t1.num1, t2.num2, t2.departmentname
FROM (第一个子查询) t1
RIGHT JOIN (第二个子查询) t2 ON t1.departmentname = t2.departmentname;

内容的提问来源于stack exchange，提问作者Mohamed Mahmoud