如何在SQL Server 2012中连接三张表并基于双属性计算员工排名

没问题，我帮你整理出符合需求的SQL语句，咱们一步步拆解实现逻辑：

核心思路 #

我们需要以tblEmployee为主表（确保输出所有员工），通过左连接分别聚合tblGen和tblStat的数据，计算每个员工的目标统计值，最后添加排名字段。

完整SQL语句 #

SELECT
    e.E_Name AS E_Name,
    e.E_ID AS EmployeeID,
    e.E_City AS City,
    -- 处理无记录的情况，将NULL转为0
    COALESCE(g.TgenerateCount, 0) AS TgenerateCount,
    COALESCE(s.Delivered_Count, 0) AS Delivered_Count,
    -- 按Delivered_Count降序、TgenerateCount降序排名，可按需调整排序规则
    RANK() OVER (ORDER BY COALESCE(s.Delivered_Count, 0) DESC, COALESCE(g.TgenerateCount, 0) DESC) AS Ranking
FROM tblEmployee e
-- 左连接聚合后的tblGen数据：统计每个员工Tgenerate=1的记录数
LEFT JOIN (
    SELECT
        Emp_ID,
        SUM(Tgenerate) AS TgenerateCount -- 利用布尔值1/0的特性直接求和，等价于COUNT(CASE WHEN Tgenerate=1 THEN 1 END)
    FROM tblGen
    GROUP BY Emp_ID
) g ON e.E_ID = g.Emp_ID
-- 左连接聚合后的tblStat数据：统计每个员工Status='Delivered'的记录数
LEFT JOIN (
    SELECT
        Emp_ID,
        COUNT(CASE WHEN Status = 'Delivered' THEN 1 END) AS Delivered_Count
    FROM tblStat
    GROUP BY Emp_ID
) s ON e.E_ID = s.Emp_ID
-- 按排名排序输出
ORDER BY Ranking;

关键细节说明 #

1. 左连接的使用：用LEFT JOIN而非INNER JOIN，保证即使员工在tblGen或tblStat中无任何记录，也能出现在最终结果里。
2. COALESCE函数：将聚合后可能出现的NULL值转为0，比如员工109在tblGen中只有一条Tgenerate=0的记录，聚合后TgenerateCount为0；员工106在两个统计表里都无记录，两个计数都会显示0。
3. 排名规则：示例中用RANK()函数按Delivered_Count优先降序排名，若需要连续无间隙的排名（比如相同计数的员工排名相同且后续排名不跳号），可以替换为DENSE_RANK()。
4. 统计逻辑兼容：如果你的数据库不支持直接对布尔值求和（比如部分数据库中布尔类型是BIT），可以把SUM(Tgenerate)替换为COUNT(CASE WHEN Tgenerate = 1 THEN 1 END)，效果完全一致。

验证示例数据 #

运行上述语句后，你会得到符合需求的结果：

• 员工101的Delivered_Count为2，TgenerateCount为0
• 员工102的TgenerateCount为2，Delivered_Count为0
• 员工109的TgenerateCount为0，Delivered_Count为0

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