Oracle查询：将固定2行的查询结果合并为单行多列展示

算力阿峰

2026-10-3

Oracle固定2行数据合并为单行查询方案

因为你的业务表固定只有2条数据，以下两种实现方式都可以达到你要的效果：

方案1：CASE+聚合函数实现（兼容性最好）

SELECT
    MAX(CASE WHEN rn = 1 THEN EmployeeType END) AS EmployeeTypeOne,
    MAX(CASE WHEN rn = 1 THEN Salary END) AS SalaryOne,
    MAX(CASE WHEN rn = 1 THEN Department END) AS DepartmentOne,
    MAX(CASE WHEN rn = 2 THEN EmployeeType END) AS EmployeeTypeTwo,
    MAX(CASE WHEN rn = 2 THEN Salary END) AS SalaryTwo,
    MAX(CASE WHEN rn = 2 THEN Department END) AS DepartmentTwo
FROM (
    SELECT 
        EmployeeType,
        Salary,
        Department,
        ROW_NUMBER() OVER(ORDER BY EmployeeType) rn
    FROM 你的实际业务表名
) t;

逻辑说明：

内层查询通过ROW_NUMBER()开窗函数给两行数据分别标记行号1、2，你可以修改ORDER BY后的字段调整两行的排序规则，固定哪一行作为第一行展示
外层通过CASE判断行号提取对应字段值，再用MAX聚合将两行的分散结果合并为单行，自动过滤空值

方案2：PIVOT行转列函数实现（写法更简洁）

SELECT 
    "1_EMPLOYEETYPE" AS EmployeeTypeOne,
    "1_SALARY" AS SalaryOne,
    "1_DEPARTMENT" AS DepartmentOne,
    "2_EMPLOYEETYPE" AS EmployeeTypeTwo,
    "2_SALARY" AS SalaryTwo,
    "2_DEPARTMENT" AS DepartmentTwo
FROM (
    SELECT 
        EmployeeType,
        Salary,
        Department,
        ROW_NUMBER() OVER(ORDER BY EmployeeType) rn
    FROM 你的实际业务表名
) t
PIVOT (
    MAX(EmployeeType) AS EMPLOYEETYPE,
    MAX(Salary) AS SALARY,
    MAX(Department) AS DEPARTMENT
    FOR rn IN (1,2)
);

逻辑说明：