如何针对单个用户展示排名？附现有SQL查询语句

嗨，你已经搞定了全量用户的排名展示，现在想单独获取某个用户的排名对吧？这其实很容易，核心思路是先计算所有用户的排名，再筛选出目标用户——千万别直接在原查询里加WHERE再算排名，那样窗口函数只会基于过滤后的单条数据计算，结果永远是1，完全不符合需求。

下面是两种可行的实现方式，我基于你提供的SQL语句补全了可能缺失的部分（比如未写完的nd和trgt关联）：

方法1：使用子查询包裹原排名查询 #

SELECT *
FROM (
    SELECT 
        DENSE_RANK() OVER (ORDER BY s.rows DESC) AS Ranks, 
        Territory, 
        TM_Name, 
        COALESCE(g.rows, 0) AS RXCount, 
        COALESCE(s.rows, 0) AS DelCounts, 
        COALESCE(nd.rows, 0) AS NotDeliveredCount, 
        COALESCE(trgt.rows, 0) AS Target, 
        e.TM_Emp_Id 
    FROM tblEmployee e 
    LEFT JOIN (SELECT EmpCode, SUM(RxGenerate) AS rows FROM tbl_RX GROUP BY EmpCode) g ON g.EmpCode = e.TM_Emp_Id 
    LEFT JOIN (SELECT EmpCode, SUM(MedToPCount) AS rows FROM tbl_MedicinToPatient WHERE Status = 'Delivered' GROUP BY EmpCode) s ON s.EmpCode = e.TM_Emp_Id
    -- 补全未写完的未交付和目标数据关联
    LEFT JOIN (SELECT EmpCode, SUM(MedToPCount) AS rows FROM tbl_MedicinToPatient WHERE Status = 'Not Delivered' GROUP BY EmpCode) nd ON nd.EmpCode = e.TM_Emp_Id
    LEFT JOIN (SELECT EmpCode, SUM(TargetValue) AS rows FROM tbl_Target GROUP BY EmpCode) trgt ON trgt.EmpCode = e.TM_Emp_Id
) AS RankedUsers
-- 这里替换成你要查询的用户标识，可以用ID或姓名
WHERE TM_Emp_Id = '目标用户的EmpID' 
-- 或者用姓名筛选：WHERE TM_Name = '目标用户姓名'

方法2：使用CTE（公共表表达式），可读性更强 #

CTE的写法和子查询逻辑完全一致，但结构更清晰，适合复杂查询：

WITH RankedUsers AS (
    SELECT 
        DENSE_RANK() OVER (ORDER BY s.rows DESC) AS Ranks, 
        Territory, 
        TM_Name, 
        COALESCE(g.rows, 0) AS RXCount, 
        COALESCE(s.rows, 0) AS DelCounts, 
        COALESCE(nd.rows, 0) AS NotDeliveredCount, 
        COALESCE(trgt.rows, 0) AS Target, 
        e.TM_Emp_Id 
    FROM tblEmployee e 
    LEFT JOIN (SELECT EmpCode, SUM(RxGenerate) AS rows FROM tbl_RX GROUP BY EmpCode) g ON g.EmpCode = e.TM_Emp_Id 
    LEFT JOIN (SELECT EmpCode, SUM(MedToPCount) AS rows FROM tbl_MedicinToPatient WHERE Status = 'Delivered' GROUP BY EmpCode) s ON s.EmpCode = e.TM_Emp_Id
    LEFT JOIN (SELECT EmpCode, SUM(MedToPCount) AS rows FROM tbl_MedicinToPatient WHERE Status = 'Not Delivered' GROUP BY EmpCode) nd ON nd.EmpCode = e.TM_Emp_Id
    LEFT JOIN (SELECT EmpCode, SUM(TargetValue) AS rows FROM tbl_Target GROUP BY EmpCode) trgt ON trgt.EmpCode = e.TM_Emp_Id
)
SELECT *
FROM RankedUsers
WHERE TM_Emp_Id = '目标用户的EmpID';

关键提醒 #

• 一定要先计算全量用户的排名，再做筛选，这样得到的Ranks才是该用户在所有用户中的真实排名。
• 如果你的原查询中还有其他未写完的关联条件，直接补充到子查询/CTE里即可，不影响外层的筛选逻辑。

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