我只需要从employee_leaves表中 for each 员工(employee_id)提取一条记录，但前提是status_id等于以下数字之一:7, 4, 3(按优先级写入).如果尚未找到第status_id=7行，则应查找第status_id=4行.如果未找到，则应查找第status_id=3行.
示例:应返回id=4的行
| id | employee_id | status_id |
|---|---|---|
| 1 | 7 | 1 |
| 2 | 7 | 2 |
| 3 | 7 | 4 |
| 4 | 7 | 7 |
| 9 | 7 | 3 |
| 27 | 7 | 7 |
我只需要从employee_leaves表中 for each 员工(employee_id)提取一条记录，但前提是status_id等于以下数字之一:7, 4, 3(按优先级写入).如果尚未找到第status_id=7行，则应查找第status_id=4行.如果未找到，则应查找第status_id=3行.
示例:应返回id=4的行
| id | employee_id | status_id |
|---|---|---|
| 1 | 7 | 1 |
| 2 | 7 | 2 |
| 3 | 7 | 4 |
| 4 | 7 | 7 |
| 9 | 7 | 3 |
| 27 | 7 | 7 |
First filter the rows of each employee_id for the status_ids that you want.
Then rank the rows with ROW_NUMBER() window function by applying the priorities that you want.
Finally pick the first row from each ranking:
WITH cte AS (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY employee_id
ORDER BY status_id = 7 DESC,
status_id = 4 DESC,
status_id = 3 DESC, -- this is the last option and it is actually not needed
id
) AS rn
FROM employee_leaves
WHERE status_id IN (7, 4, 3)
)
SELECT id, employee_id, status_id
FROM cte
WHERE rn = 1;
使用函数FIELD()可以简化ROW_NUMBER()内的ORDER BY子句:
ORDER BY FIELD(status_id, 7, 4, 3),
id
或功能FIND_IN_SET():
ORDER BY FIND_IN_SET(status_id, '7,4,3'),
id
See the demo.