我有以下字段：
| employee_email | status | id |
| test1@test.com | active | 1 |
| test1@test.com | terminated | 2 |
| test2@test.com | active | 3 |
| test3@test.com | terminated | 4 |
| test4@test.com | terminated | 5 |
| test4@test.com | terminated | 6 |
我需要用一个规则进行查询，如果同一个employee_email有超过1行(其中一行是活动的，另一行是终止的——那么只返回行=活动(。如果超过一行且两者都终止，则只返回一行终止。
输出应为：
| employee_email | status | status |
| test1@test.com | active | 1 |
| test2@test.com | active | 3 |
| test3@test.com | terminated | 4 |
| test4@test.com | terminated | 5 |
如果您的MySql版本是8.0+，则可以使用窗口函数ROW_NUMBER():
SELECT t.employee_email, t.status, t.id
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY employee_email ORDER BY status = 'active' DESC, id) rn
FROM tablename
) t
WHERE t.rn = 1
对于以前的版本：
SELECT t.employee_email, MAX(t.status) status, MIN(t.id) id
FROM tablename t
WHERE t.status = 'active'
OR NOT EXISTS (SELECT 1 FROM tablename WHERE employee_email = t.employee_email AND status = 'active')
GROUP BY t.employee_email
请参阅演示
结果：
> employee_email | status | id
> :------------- | :--------- | -:
> test1@test.com | active | 1
> test2@test.com | active | 3
> test3@test.com | terminated | 4
> test4@test.com | terminated | 5