时间戳

首页 > 解决方案 > 合并 2 个 SQL 查询的结果

sql - 合并 2 个 SQL 查询的结果

问题描述

我有两张表，我想比较相似的数据。以下查询提供每个表中的员工计数：

SELECT Extract_Month, COUNT(Employee_Number) as Staff
FROM Staff
GROUP BY Extract_Month;

SELECT Extract_Month, COUNT(Employee_Number) as MX
FROM Staff_mx
GROUP BY Extract_Month;

结果如下

2019-04-01  17246
2019-05-01  17194
2019-06-01  17252

2019-04-01  17140
2019-05-01  17194
2019-06-01  17434

我想要一个查询以以下格式组合结果：

            Staff MX
2019-04-01  17246 17140
2019-05-01  17194 17194
2019-06-01  17252 17434

我试过这个，其中包含我想要的结果，但没有正确呈现它们：

SELECT s.Extract_Month, COUNT(s.Employee_Number) as Staff, COUNT(mx.Employee_Number) as MX
FROM Staff s
    LEFT JOIN
        staff_mx mx
        ON s.Employee_Number = mx.Employee_Number 
        AND s.Extract_Month = mx.Extract_Month 
GROUP BY s.Extract_Month
UNION ALL
SELECT mx.Extract_Month, COUNT(s.Employee_Number) as Staff, COUNT(mx.Employee_Number) as MX
FROM Staff s
    RIGHT JOIN
        staff_mx mx
        ON s.Employee_Number = mx.Employee_Number 
        AND s.Extract_Month = mx.Extract_Month 
GROUP BY mx.Extract_Month

这给了我以下，但不是预期的结果：

            Staff   MX    
2019-04-01  17246   16830
2019-05-01  17194   16820
2019-06-01  17252   16784
2019-04-01  16830   17140
2019-05-01  16820   17194
2019-06-01  16784   17434

任何帮助，将不胜感激！

标签： sqlsql-serverjoinunion

解决方案

您可以使用JOIN：

SELECT s.Extract_Month, s.staff, sm.mx
FROM (SELECT Extract_Month, COUNT(Employee_Number) as Staff
      FROM Staff
      GROUP BY Extract_Month
     ) s JOIN
     (SELECT Extract_Month, COUNT(Employee_Number) as MX
      FROM Staff_mx
      GROUP BY Extract_Month
     ) sm
     ON sm.Extract_Month = s.Extract_Month;

如果两个表的月份不一定相同，则使用FULL JOINand COALESCE()：

SELECT COALESCE(s.Extract_Month, sm.Extract_Month) as Extract_Month,
       COALESCE(s.staff, 0) as staff,
       COALESCE(sm.mx, 0) as mx
FROM (SELECT Extract_Month, COUNT(Employee_Number) as Staff
      FROM Staff
      GROUP BY Extract_Month
     ) s FULL JOIN
     (SELECT Extract_Month, COUNT(Employee_Number) as MX
      FROM Staff_mx
      GROUP BY Extract_Month
     ) sm
     ON sm.Extract_Month = s.Extract_Month;

推荐阅读