mysql - 从同一个 MySQL 表中获取行添加/删除

Question

我有以下员工表，我想根据下面所示的输出根据“月份”、“季度”和“年份”了解谁离开/加入了公司。

从数据中我们可以看出，Olivia 于 2017 年第一季度离开公司，Geogia 和 Grace 于 2017 年第二季度加入公司

员工数据库表

| Month | Quarter | Year | Company | Company Description | Employee ID | Employee Name | Country Description |    
| M1    | Q1      | 17   | 020     | US                  | 03237       | Jessica       | UNITED STATES       |
| M1    | Q1      | 17   | 020     | US                  | 05153       | Olivia        | UNITED STATES       |    
| M1    | Q1      | 17   | 020     | US                  | 06809       | Hannah        | UNITED STATES       |    
| M1    | Q2      | 17   | 020     | US                  | 03237       | Jessica       | UNITED STATES       |    
| M1    | Q2      | 17   | 020     | US                  | 0680        | Hannah        | UNITED STATES       |    
| M1    | Q2      | 17   | 020     | US                  | 08434       | Georgia       | UNITED STATES       |    
| M1    | Q2      | 17   | 020     | US                  | 08858       | Grace         | UNITED STATES       |

所需输出

| Month | Quarter | Year | Company | Company Description | Employee ID | Employee Name | Country Description | Status |    
| M1    | Q1      |   17 |     020 | US                  |       03237 | Olivia        | UNITED STATES       | Out    |    
| M1    | Q2      |   17 |     020 | US                  |       08434 | Georgia       | UNITED STATES       | In     |    
| M1    | Q2      |   17 |     020 | US                  |       08858 | Grace         | UNITED STATES       | In     |

我正在尝试使用以下语句，但我被卡住了

SELECT  t.month,
        t.quarter,
        t.year,
        t.employee_id,
       t.employee_name

FROM q4_16_hc AS t
JOIN q4_16_hc AS t1
  ON t1.employee_id = t.employee_id
WHERE t.month <> t1.month
group by t.employee_id

Answer 1

您通常会使用完全连接来执行此操作。由于 MySQL 不支持它，因此您应该使用左连接和右连接执行union all。

--Employees who left from previous quarter
SELECT t1.month,
    t1.quarter,
    t1.year,
    t1.employee_id,
    t1.employee_name,
    'Out' as status
FROM q4_16_hc AS t1
LEFT JOIN q4_16_hc AS t2 ON t1.employee_id = t2.employee_id AND t1.year = t2.year 
AND t1.Company=t2.Company AND t1.quarter='Q1' AND t2.quarter = 'Q2'
WHERE t2.employee_id is null 
UNION ALL
--Employees who newly joined 
SELECT t2.month,
    t2.quarter,
    t2.year,
    t2.employee_id,
    t2.employee_name,
    'In'
FROM q4_16_hc AS t1
RIGHT JOIN q4_16_hc AS t2 ON t1.employee_id = t2.employee_id AND t1.year = t2.year 
AND t1.Company=t2.Company AND t1.quarter='Q1' AND t2.quarter = 'Q2'
WHERE t1.employee_id is null