如何编写按班级生成独立列展示学生姓名的SELECT查询？

错误原因 #

你编写的子查询会返回对应班级的所有学生姓名（多行结果），但SELECT子句中的子查询必须是标量子查询（仅能返回单个值），因此触发了'Subquery returned more than 1 value'错误。

方案1：使用ROW_NUMBER() + PIVOT（适用于SQL Server等支持PIVOT的数据库） #

先给每个班级的学生分配行号，再通过PIVOT将行转成列：

WITH ClassStudents AS (
    SELECT 
        NAME,
        CLASS_NO,
        -- 按班级分组，给每个学生分配行号
        ROW_NUMBER() OVER (PARTITION BY CLASS_NO ORDER BY PRSN_SQNO) AS RowNum
    FROM STUDENT
    WHERE PRSN_SQNO > 0
)
SELECT 
    [1] AS Class1,
    [2] AS Class2,
    [3] AS Class3
FROM ClassStudents
PIVOT (
    MAX(NAME) FOR CLASS_NO IN ([1], [2], [3])
) AS PivotTable;

方案2：使用条件聚合（通用型，支持MySQL、PostgreSQL等多数数据库） #

通过行号分组，结合CASE语句聚合每个班级的学生姓名：

WITH ClassStudents AS (
    SELECT 
        NAME,
        CLASS_NO,
        ROW_NUMBER() OVER (PARTITION BY CLASS_NO ORDER BY PRSN_SQNO) AS RowNum
    FROM STUDENT
    WHERE PRSN_SQNO > 0
)
SELECT
    MAX(CASE WHEN CLASS_NO = '1' THEN NAME END) AS Class1,
    MAX(CASE WHEN CLASS_NO = '2' THEN NAME END) AS Class2,
    MAX(CASE WHEN CLASS_NO = '3' THEN NAME END) AS Class3
FROM ClassStudents
GROUP BY RowNum
ORDER BY RowNum;

补充说明 #

两种方案都会先给每个班级内的学生按PRSN_SQNO排序并分配行号，再将同一行号的不同班级学生放在同一行，最终输出和你示例一致的格式。如果你的数据库不支持CTE（WITH子句），可以把CTE部分替换为子查询。

内容的提问来源于stack exchange，提问作者심해림