如何在SQL Server中合并5个薪资查询以返回单员工9行结果

问题根源 #

直接用JOIN会因为多表关联产生笛卡尔积，导致行数倍增；用UNION只是简单堆叠所有查询结果，自然行数超标。要实现每个员工固定返回9行，得先构建一个9行的“结果骨架”，再把各个查询的结果对应填充到骨架的指定位置。

具体实现方案 #

1. 生成9行的基础骨架 #

先创建一个包含1到9行号的虚拟表，作为每个员工结果的固定结构：

WITH RowNumbers AS (
    SELECT 1 AS RowID UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
    SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
    SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
)

2. 给每个查询绑定行号 #

把你那5个独立查询分别对应到指定的RowID上，比如：

• 员工基础薪资信息对应RowID 1
• 薪资项统计占RowID 2-4（3行）
• 不同类别扣除项分配到RowID 5-9（5行）
  示例（拿两个查询举例）：

, EmpBase AS (
    SELECT 
        e.EmployeeID,
        1 AS RowID,
        e.EmployeeName,
        psg.BasePay,
        NULL AS DeductionAmt
    FROM Employee e
    JOIN PSG psg ON e.EmployeeID = psg.EmployeeID
),
DeductionTypeA AS (
    SELECT 
        d.EmployeeID,
        5 AS RowID,
        NULL AS EmployeeName,
        NULL AS BasePay,
        d.Amount AS DeductionAmt
    FROM Deductions d
    WHERE d.DeductionCat = 'A'
)

3. 把所有查询和骨架关联起来 #

用RowNumbers做基础，结合所有员工ID，通过RowID和EmployeeID做FULL JOIN，确保每个员工都有9行，缺数据的位置用NULL填充：

SELECT 
    COALESCE(eb.EmployeeID, dta.EmployeeID, dtb.EmployeeID, ...) AS EmployeeID,
    rn.RowID,
    COALESCE(eb.EmployeeName, '') AS EmployeeName,
    COALESCE(eb.BasePay, 0) AS BasePay,
    COALESCE(dta.DeductionAmt, dtb.DeductionAmt, ...) AS DeductionAmt
    -- 其他需要的字段依次用COALESCE处理
FROM RowNumbers rn
CROSS JOIN (SELECT DISTINCT EmployeeID FROM Employee) allEmps
FULL JOIN EmpBase eb ON allEmps.EmployeeID = eb.EmployeeID AND rn.RowID = eb.RowID
FULL JOIN DeductionTypeA dta ON allEmps.EmployeeID = dta.EmployeeID AND rn.RowID = dta.RowID
-- 继续把剩下的3个查询用同样的方式FULL JOIN进来
ORDER BY allEmps.EmployeeID, rn.RowID;

注意要点 #

• 每个查询必须明确分配对应的RowID，确保结果能精准对应到9行里的指定位置
• 用COALESCE统一处理不同查询的字段值，避免NULL导致的显示混乱
• 如果某个查询本身返回多行（比如薪资项统计有3条），要给这几条分别分配连续的RowID（比如2、3、4），保证每个员工的这部分结果刚好占对应行数

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