如何编写SQL查询生成含员工信息与Vacant标识的职位结果表

安全阿岳

2026-7-13

需求说明

生成结果表，要求职位已填充员工时显示员工姓名，空缺时显示Vacant。

现有表结构及数据

Position Table

Id	Title	Group	Level	payScale	Totalpost
1	General manager	A	l-15	10000	1
2	Manager	B	l-14	9000	5
3	Asst. Manager	C	l-13	8000	10

Employee Table

Id	Name	Position_id
1	John Smith	1
2	Jane Doe	2
3	Michael Brown	2
4	Emily Johnson	2
5	William Lee	3
6	Jessica Clark	3
7	Christopher Harris	3
8	Olivia Wilson	3
9	Daniel Martinez	3
10	Sophia Miller	3

期望输出

Title	Group	Level	payScale	Employee_Name
General manager	A	l-15	10000	John Smith
Manager	B	l-14	9000	Jane Doe
Manager	B	l-14	9000	Michael Brown
Manager	B	l-14	9000	Emily Johnson
Manager	B	l-14	9000	Vacant
Manager	B	l-14	9000	Vacant
Asst. Manager	C	l-13	8000	William Lee
Asst. Manager	C	l-13	8000	Jessica Clark
Asst. Manager	C	l-13	8000	Christopher Harris
Asst. Manager	C	l-13	8000	Olivia Wilson
Asst. Manager	C	l-13	8000	Daniel Martinez
Asst. Manager	C	l-13	8000	Sophia Miller
Asst. Manager	C	l-13	8000	Vacant
Asst. Manager	C	l-13	8000	Vacant
Asst. Manager	C	l-13	8000	Vacant
Asst. Manager	C	l-13	8000	Vacant

SQL 查询语句

WITH RECURSIVE num_seq AS (
    SELECT 1 AS seq
    UNION ALL
    SELECT seq + 1 FROM num_seq
    WHERE seq < (SELECT MAX(Totalpost) FROM Position)
)
SELECT
    p.Title,
    p.Group,
    p.Level,
    p.payScale,
    COALESCE(e.Name, '**Vacant**') AS Employee_Name
FROM Position p
JOIN num_seq ns ON ns.seq <= p.Totalpost
LEFT JOIN (
    SELECT 
        Position_id,
        Name,
        ROW_NUMBER() OVER (PARTITION BY Position_id ORDER BY Id) AS emp_seq
    FROM Employee
) e ON p.Id = e.Position_id AND ns.seq = e.emp_seq
ORDER BY p.Id, ns.seq;