SQL Server 2012：基于两表双属性的员工排名及状态统计查询

咱们先拆解需求：要按每个员工的TGenerate=1总数量、Delivered状态总数量做排名，同时统计非Delivered的状态总数。下面是适配SQL Server 2012的完整查询语句：

WITH EmpGenerateStats AS (
    -- 统计每个员工的TGenerate有效总数（即TGenerate=1的记录数）
    SELECT 
        Emp_Id,
        SUM(CASE WHEN TGenerate = 1 THEN 1 ELSE 0 END) AS Total_Valid_TGenerate
    FROM tbl_Generate
    GROUP BY Emp_Id
),
EmpStatusStats AS (
    -- 统计每个员工的Delivered和非Delivered状态数
    SELECT 
        Emp_Id,
        SUM(CASE WHEN Status = 'Delivered' THEN 1 ELSE 0 END) AS Total_Delivered,
        COUNT(*) - SUM(CASE WHEN Status = 'Delivered' THEN 1 ELSE 0 END) AS Total_Non_Delivered
    FROM tbl_Status
    GROUP BY Emp_Id
)
-- 合并统计结果并计算排名
SELECT 
    eg.Emp_Id,
    eg.Total_Valid_TGenerate,
    es.Total_Delivered,
    es.Total_Non_Delivered,
    -- 按TGenerate总数降序、Delivered总数降序排名，用RANK处理并列
    RANK() OVER (ORDER BY eg.Total_Valid_TGenerate DESC, es.Total_Delivered DESC) AS Employee_Rank
FROM EmpGenerateStats eg
INNER JOIN EmpStatusStats es ON eg.Emp_Id = es.Emp_Id
ORDER BY Employee_Rank;

关键部分说明： #

• 两个CTE：分别从两张表提取员工的核心统计数据，让逻辑更清晰，避免嵌套子查询的混乱。
• 排名函数：用RANK()是因为它会给并列的员工相同排名，同时跳过后续名次（比如两个第1名，下一个是第3名）；如果需要连续排名（两个第1名后是第2名），可以换成DENSE_RANK()。
• 非Delivered统计：直接用状态总记录数减去Delivered的数量，比单独写CASE WHEN更简洁。

特殊情况处理： #

如果存在员工只在其中一张表有数据的情况，把INNER JOIN改成FULL OUTER JOIN，并用ISNULL()把NULL值转为0，比如：

SELECT 
    ISNULL(eg.Emp_Id, es.Emp_Id) AS Emp_Id,
    ISNULL(eg.Total_Valid_TGenerate, 0) AS Total_Valid_TGenerate,
    ISNULL(es.Total_Delivered, 0) AS Total_Delivered,
    ISNULL(es.Total_Non_Delivered, 0) AS Total_Non_Delivered,
    RANK() OVER (ORDER BY ISNULL(eg.Total_Valid_TGenerate, 0) DESC, ISNULL(es.Total_Delivered, 0) DESC) AS Employee_Rank
FROM EmpGenerateStats eg
FULL OUTER JOIN EmpStatusStats es ON eg.Emp_Id = es.Emp_Id
ORDER BY Employee_Rank;

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