Oracle SQL：从同一列生成多列，解决单行子查询返回多行错误

错误原因 #

你遇到的“single-row subquery returns multiple rows”问题，是因为你用的单值子查询（比如放在SELECT或WHERE后的子查询）返回了多条结果，但这类子查询要求只能返回单个值，无法匹配每个员工对应的金额。

通用解决方案：CASE WHEN + 聚合函数 #

这是跨数据库通用的写法，不依赖特定数据库的PIVOT功能：

假设你的关联表结构包含employees（员工表，含employee_id、employee_name等字段）、payments（薪资表，关联员工和薪资类型，含employee_id、element_type_id、amount）、element_types（薪资类型表，含element_type_id、element_type），可以用以下SQL实现：

SELECT
    e.employee_id,
    e.employee_name,
    -- 提取Basic Salary作为单独列
    MAX(CASE WHEN et.element_type = 'Basic Salary' THEN p.amount END) AS basic_salary,
    -- 提取Housing Allowance作为单独列
    MAX(CASE WHEN et.element_type = 'Housing Allowance' THEN p.amount END) AS housing_allowance
FROM
    employees e
JOIN
    payments p ON e.employee_id = p.employee_id
JOIN
    element_types et ON p.element_type_id = et.element_type_id
GROUP BY
    e.employee_id, e.employee_name;

说明 #

• CASE WHEN会根据element_type匹配对应的金额，不匹配的类型返回NULL
• 用MAX（或MIN/SUM，根据业务场景选择）聚合函数，把同一员工的多行记录合并成一行，过滤NULL值
• GROUP BY指定员工的唯一标识，确保每个员工只输出一行结果

特定数据库方案：PIVOT函数 #

如果你的数据库支持PIVOT（比如Oracle、SQL Server、PostgreSQL 11+），可以用更简洁的语法：

以Oracle为例：

SELECT
    employee_id,
    employee_name,
    "Basic Salary" AS basic_salary,
    "Housing Allowance" AS housing_allowance
FROM (
    -- 先查询出需要的基础字段
    SELECT
        e.employee_id,
        e.employee_name,
        et.element_type,
        p.amount
    FROM
        employees e
    JOIN
        payments p ON e.employee_id = p.employee_id
    JOIN
        element_types et ON p.element_type_id = et.element_type_id
)
PIVOT (
    MAX(amount)  -- 聚合金额字段
    FOR element_type IN ('Basic Salary', 'Housing Allowance')  -- 指定要转成列的类型
);

注意 #

不同数据库的PIVOT语法略有差异，比如SQL Server不需要单引号包裹类型值，PostgreSQL用crosstab函数，需根据你使用的数据库调整。

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