如何从两个SQL表生成指定格式的表格输出？

嘿，我看你在尝试把job_positions和job_employees这两个表关联起来生成符合需求的表格输出，不过现有代码里有几个小问题导致没得到预期结果，我帮你一步步梳理解决：

首先，你的代码里的直接错误 #

你写的CTE里用了position和employee作为表名，但实际你的表是job_positions和job_employees，这会直接导致“表不存在”的错误，这是最容易修正的点。

然后，根据你的需求调整逻辑 #

从你的尝试来看，你想要每个职位一行，展示职位信息，同时处理员工空缺的情况。这里分两种常见需求给你方案：

方案1：每个职位一行，汇总员工列表+空缺统计

如果需要展示每个职位的总名额、已填充数量、空缺数量，以及所有员工的名字，用这个查询：

WITH EmployeeStats AS (
    -- 先统计每个职位的员工数量和名字列表
    SELECT 
        position_id,
        COUNT(*) AS filled_posts,
        STRING_AGG(name, ', ') AS employee_names
    FROM job_employees
    GROUP BY position_id
)
SELECT
    p.title AS 职位名称,
    p.groups AS 组别,
    p.levels AS 等级,
    p.payscale AS 薪资标准,
    p.totalpost AS 总职位数,
    COALESCE(es.filled_posts, 0) AS 已填充职位数,
    (p.totalpost - COALESCE(es.filled_posts, 0)) AS 空缺职位数,
    COALESCE(es.employee_names, 'Vacant') AS 员工姓名
FROM job_positions p
LEFT JOIN EmployeeStats es ON p.id = es.position_id
ORDER BY p.id;

方案2：每个职位的每个名额对应一行，空缺显示Vacant

如果需要把每个职位的所有名额都列出来，不管有没有员工，空缺的名额显示Vacant，用这个查询：

WITH PositionSlots AS (
    -- 生成每个职位的所有名额行（比如总职位数是5，就生成5行）
    SELECT 
        p.id AS position_id,
        p.title,
        p.groups,
        p.levels,
        p.payscale,
        generate_series(1, p.totalpost) AS slot_number
    FROM job_positions p
),
RankedEmployees AS (
    -- 给每个职位的员工按ID排序编号
    SELECT
        position_id,
        name,
        ROW_NUMBER() OVER (PARTITION BY position_id ORDER BY id) AS emp_rank
    FROM job_employees
)
SELECT
    ps.title AS 职位名称,
    ps.groups AS 组别,
    ps.levels AS 等级,
    ps.payscale AS 薪资标准,
    COALESCE(re.name, 'Vacant') AS 员工姓名
FROM PositionSlots ps
LEFT JOIN RankedEmployees re ON ps.position_id = re.position_id AND ps.slot_number = re.emp_rank
ORDER BY ps.position_id, ps.slot_number;

修正你原来的查询（仅展示每个职位的第一个员工）

如果你只是想保留你原来的逻辑，只展示每个职位的第一个员工，那修正表名后的代码是：

WITH RankedEmployees AS (
    SELECT
        p.title AS Title,
        p.groups AS "Group",
        p.levels AS Level,
        p.payscale AS Payscale,
        e.name AS Employee_Name,
        ROW_NUMBER() OVER (PARTITION BY p.id ORDER BY e.id) AS emp_rank
    FROM job_positions p
    LEFT JOIN job_employees e ON p.id = e.position_id
)
SELECT
    Title,
    "Group",
    Level,
    Payscale,
    COALESCE(MAX(CASE WHEN emp_rank = 1 THEN Employee_Name END), 'Vacant') AS Employee_Name
FROM RankedEmployees
GROUP BY Title, "Group", Level, Payscale
ORDER BY Title, "Group", Level, Payscale;

总结 #

先修正表名的错误，再根据你实际想要的输出格式选择对应的方案就可以啦~

备注：内容来源于stack exchange，提问作者Sam