使用Join与Select语句实现行转列的技术问题求助

我正尝试把多行数据合并成一行，只需要特定行和列，但现在的问题是：就算实现了行转列，还是会出现不该有的多余行。

原表数据 #

执行SELECT * FROM Employees得到以下结果：

期望结果 #

只保留Remote ID下Priority为0的Manager、Worker Profile，把Location和Commissions转为列，最终结果如下：

尝试过的查询 #

下面这个查询在小数据量表上能运行成功，但在大数据量表上失效，推测是包含/排除规则没设置对，但找不到问题所在：

SELECT EE.ID, EE.Profile, vtm.Location, vtm.Commissions, iam.Location, iam.Commissions, wmm.Location, wmm.Commissions
FROM Employees EE
Left OUTER JOIN
    (SELECT ID, Profile, Location, Commissions FROM Employees WHERE Location = 'Vermont' AND Profile = 'Manager') vtm
    ON EE.ID = vtm.ID
Left OUTER JOIN
    (SELECT ID, Profile, Location, Commissions FROM Employees WHERE Location = 'Iowa' AND Profile = 'Manager') iam
    ON vtm.ID = iam.ID
Left OUTER JOIN
    (SELECT ID, Profile, Location, Commissions FROM Employees WHERE Location = 'Wyoming' AND Profile = 'Manager') wmm
    ON iam.ID = wmm.ID
WHERE EE.ID = 'Remote' AND Priority = '0'

问题分析与解决方案 #

原查询失效原因 #

1. 关联条件缺失：仅通过ID关联，没有绑定Profile和Priority，大数据量下会产生大量无效笛卡尔积，导致多余行。
2. 范围覆盖不全：子查询只针对Manager，完全没处理Worker的聚合逻辑。
3. 过滤时机错误：Left JOIN后再用WHERE过滤，会破坏关联逻辑，导致结果异常。

推荐解决方案 #

使用条件聚合实现行转列，逻辑清晰且性能更优：

通用版（适配多数SQL数据库）

SELECT
    Profile,
    MAX(CASE WHEN Location = 'Vermont' THEN Location END) AS Location1,
    MAX(CASE WHEN Location = 'Vermont' THEN Commissions END) AS Commissions1,
    MAX(CASE WHEN Location = 'Iowa' THEN Location END) AS Location2,
    MAX(CASE WHEN Location = 'Iowa' THEN Commissions END) AS Commissions2,
    MAX(CASE WHEN Location = 'Wyoming' THEN Location END) AS Location3,
    MAX(CASE WHEN Location = 'Wyoming' THEN Commissions END) AS Commissions3
FROM Employees
WHERE
    ID = 'Remote'
    AND Priority = '0'
    AND Profile IN ('Manager', 'Worker')
GROUP BY Profile;

专用PIVOT版（适用于SQL Server、Oracle等支持PIVOT的数据库）

如果你的数据库支持PIVOT语法，可以用更简洁的写法：

WITH FilteredData AS (
    SELECT
        Profile,
        CONCAT('Location_', Location) AS LocationCol,
        Location,
        CONCAT('Commissions_', Location) AS CommissionsCol,
        Commissions
    FROM Employees
    WHERE
        ID = 'Remote'
        AND Priority = '0'
        AND Profile IN ('Manager', 'Worker')
        AND Location IN ('Vermont', 'Iowa', 'Wyoming')
)
SELECT
    Profile,
    Vermont_Location AS Location1,
    Vermont_Commissions AS Commissions1,
    Iowa_Location AS Location2,
    Iowa_Commissions AS Commissions2,
    Wyoming_Location AS Location3,
    Wyoming_Commissions AS Commissions3
FROM FilteredData
PIVOT (
    MAX(Location) FOR LocationCol IN (Vermont_Location, Iowa_Location, Wyoming_Location)
) p1
PIVOT (
    MAX(Commissions) FOR CommissionsCol IN (Vermont_Commissions, Iowa_Commissions, Wyoming_Commissions)
) p2;

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