SQL Server中按指定排序统计目标行及之前的总行数

需求说明：

• 需确定SQL Server表中某一行的所在页码，每页固定300行
• 排序规则为HammingWt升序、Id升序
• 核心是先统计目标行（包含该行）按规则排序后的总行数，再计算页码

示例表数据：

Id  HammingWt  FileName
------------------------
1     0         F1.png
3     0         F7.png
2     1         F8.png
4     1         F2.png
12    2         F5.png
14    2         F3.png
15    2         F6.png
8     3         F4.png

预期结果：针对FileName='F3.png'（Id=14）的行，按规则排序后包含该行的总行数为6，据此可算出页码为CEILING(6/300.0)=1。

你尝试的非法SQL语句：

SELECT COUNT(*) + 1 FROM table [ABOVE] WHERE FileName = 'F3.png' ORDER BY HammingWt, ID;

合法T-SQL实现方案 #

方案1：子查询统计行数

直接统计所有排序优先级小于等于目标行的记录数：

SELECT COUNT(*) AS RowPosition
FROM YourTableName
WHERE 
    -- 匹配HammingWt更小的行
    HammingWt < (SELECT HammingWt FROM YourTableName WHERE FileName = 'F3.png')
    OR 
    -- 匹配HammingWt相同且Id小于等于目标行的记录
    (HammingWt = (SELECT HammingWt FROM YourTableName WHERE FileName = 'F3.png') 
     AND Id <= (SELECT Id FROM YourTableName WHERE FileName = 'F3.png'));

方案2：窗口函数获取位置及页码

使用ROW_NUMBER()窗口函数直接生成排序后的行号，同时计算页码：

WITH RankedRows AS (
    SELECT 
        Id, 
        FileName,
        ROW_NUMBER() OVER (ORDER BY HammingWt, Id) AS RowPosition
    FROM YourTableName
)
SELECT 
    RowPosition,
    -- 计算页码，300为每页行数，需用浮点数避免整数除法
    CEILING(RowPosition / 300.0) AS PageNumber
FROM RankedRows
WHERE FileName = 'F3.png';

性能优化建议 #

由于表数据量约150万行，建议创建复合索引提升查询效率：

CREATE NONCLUSTERED INDEX IX_YourTableName_HammingWt_Id 
ON YourTableName (HammingWt, Id) 
INCLUDE (FileName);

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