如何根据条件获取子表最新员工ID并创建关联查询视图

示例数据 #

Department表 #

Id  Name 
-----------
1   Dept1
2   Dept2
3   Dept3
4   Dept4

Employee表 #

Id DeptId  quality   VerifiedOn
----------------------------------
1    1      Ok        '2014-08-01'
2    2      Ok        '2014-09-01'
3    2      Best      '2014-08-01'
4    2      Good      '2014-08-07'
5    4      Good      '2014-10-08'
6    4      Ok        '2014-10-01'
7    4      Good      '2014-09-01'

需求说明 #

需要生成包含所有部门详情的结果，同时按以下规则获取对应部门的最新员工ID，最终创建视图：

• 优先选取部门内Quality为'Best'的员工中，VerifiedOn最新的EmployeeId
• 若该部门没有'Best'质量的员工，则选取部门内VerifiedOn最新的EmployeeId
• 若部门无对应员工，返回0或NULL（按示例用0）

预期结果 #

DeptId DeptName EmpId
-----------------------
  1     Dept1     1
  2     Dept2     3
  3     Dept3     0
  4     Dept4     5

解决方案 #

以下是适用于支持窗口函数的数据库（如SQL Server、MySQL 8.0+、PostgreSQL等）的SQL语句，通过ROW_NUMBER()窗口函数实现排序逻辑，最终创建视图：

CREATE VIEW vw_DeptLatestEmployee AS
SELECT
    d.Id AS DeptId,
    d.Name AS DeptName,
    COALESCE(e.Id, 0) AS EmpId
FROM Department d
LEFT JOIN (
    SELECT
        Id,
        DeptId,
        -- 排序规则：先给Best优先级最高，再按VerifiedOn倒序
        ROW_NUMBER() OVER (
            PARTITION BY DeptId
            ORDER BY CASE WHEN Quality = 'Best' THEN 0 ELSE 1 END, VerifiedOn DESC
        ) AS rn
    FROM Employee
) e ON d.Id = e.DeptId AND e.rn = 1;

逻辑说明 #

1. 子查询中对每个部门的员工进行排序：通过CASE表达式将Quality='Best'的员工标记为0，其他为1，确保Best优先级最高；再按VerifiedOn降序，保证同优先级下取最新的记录。
2. 用ROW_NUMBER()给每个部门的员工按上述规则编号，每个部门的第一条记录（rn=1）就是符合要求的员工。
3. 通过LEFT JOIN关联部门表和子查询结果，确保所有部门都被保留；用COALESCE将无员工部门的EmpId转为0。

如果是不支持窗口函数的老版本数据库（如MySQL 5.x），可以使用关联子查询实现：

CREATE VIEW vw_DeptLatestEmployee AS
SELECT
    d.Id AS DeptId,
    d.Name AS DeptName,
    COALESCE(
        -- 先查是否有Best的员工，取最新的
        (SELECT Id FROM Employee e1 WHERE e1.DeptId = d.Id AND e1.Quality = 'Best' ORDER BY VerifiedOn DESC LIMIT 1),
        -- 没有Best的话取所有员工中最新的
        (SELECT Id FROM Employee e2 WHERE e2.DeptId = d.Id ORDER BY VerifiedOn DESC LIMIT 1),
        -- 都没有返回0
        0
    ) AS EmpId
FROM Department d;

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