如何优化SQL查询以基于指定声望获取Stack Overflow最近权限

我之前处理过类似的业务需求，你的核心问题在于原来的查询是通过MAX(Id)和MIN(Id)来筛选，这会漏掉同一声望值对应的多个权限——毕竟权限组是按声望值划分的，不是按ID。

正确的思路应该是：

1. 找到小于等于目标声望的最大声望值，这个值对应的所有权限就是你要的「最近已获得的权限组」；
2. 找到大于目标声望的最小声望值，这个值对应的所有权限就是「下一个可达成的权限组」；
3. 把这两个声望值对应的所有权限拉出来，按声望和ID排序即可。

下面是修正后的查询代码：

DECLARE @MyReputation AS INT = 10; -- 替换为你的目标声望值，比如945、7276等

-- 获取已获得权限的最高声望阈值
DECLARE @MaxAvailableRep AS INT = (
    SELECT MAX(Reputation) 
    FROM Privilege 
    WHERE Reputation > 0 AND Reputation <= @MyReputation
);

-- 获取下一个权限的最低声望阈值
DECLARE @NextRequiredRep AS INT = (
    SELECT MIN(Reputation) 
    FROM Privilege 
    WHERE Reputation > @MyReputation
);

-- 查询对应声望的所有权限，按声望、ID排序
SELECT Id, Reputation, PrivilegeName
FROM Privilege
WHERE Reputation IN (@MaxAvailableRep, @NextRequiredRep)
ORDER BY Reputation, Id;

测试验证 #

• 当输入声望为10或13时，@MaxAvailableRep是10，@NextRequiredRep是15，会输出所有声望10和15的权限，完全符合你的期望；
• 输入声望945时，@MaxAvailableRep是500，@NextRequiredRep是1000，会输出声望500的权限，以及声望1000的所有权限；
• 输入声望7276时，@MaxAvailableRep是5000，@NextRequiredRep是10000，输出结果和你原来的正确案例一致。

补充边缘场景处理 #

如果需要处理「目标声望高于所有权限」或「目标声望低于所有权限」的情况，可以用ISNULL来兼容，比如：

SELECT Id, Reputation, PrivilegeName
FROM Privilege
WHERE 
    (Reputation = @MaxAvailableRep OR @MaxAvailableRep IS NULL)
    AND (Reputation = @NextRequiredRep OR @NextRequiredRep IS NULL)
ORDER BY Reputation, Id;

这样当目标声望超过所有权限时，只会输出最高声望的所有权限；当目标声望低于所有权限时，只会输出最低声望的所有权限。

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