如何将子查询作为结果查询的列？附staff表查询需求

现有staff表：

ID  Position Name
-----------------
1   doctor   Joe
2   nurse    Frederica
3   doctor   Mark
4   doctor   James
5   doctor   Ema
6   nurse    Dominica
7   nurse    Anna

期望得到如下查询结果：

doctors  nurses
---------------
Joe      Frederica
Mark     Dominica
James    Anna
Ema

我尝试了如下写法，但无法运行：

SELECT
(SELECT staff.`Name` FROM staff WHERE staff.Position = 'doctor') AS Doctors;
(SELECT staff.`Name` FROM staff WHERE staff.Position = 'nurse') AS Nurses;

问题分析 #

你的写法存在两个核心问题：

• 这是两个独立的SELECT语句，用分号分隔，无法合并成一个结果集返回。
• 子查询返回多行数据时，直接作为列值会触发报错，因为标量子查询只能返回单个值。

解决方案 #

我们可以通过给每个职位的员工分配行号，再通过行号关联两组数据，实现目标格式。以下是可行的SQL语句：

WITH doctors AS (
    SELECT Name, ROW_NUMBER() OVER (ORDER BY ID) AS rn
    FROM staff
    WHERE Position = 'doctor'
),
nurses AS (
    SELECT Name, ROW_NUMBER() OVER (ORDER BY ID) AS rn
    FROM staff
    WHERE Position = 'nurse'
)
SELECT d.Name AS doctors, n.Name AS nurses
FROM doctors d
LEFT JOIN nurses n ON d.rn = n.rn;

说明 #

1. 使用CTE分别提取医生和护士数据，并用ROW_NUMBER()按ID顺序为每组记录生成行号，保证同位置的员工按原顺序对应。
2. 通过行号rn进行左连接，确保所有医生记录都能显示，护士数量不足时对应位置留空。

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