获取SQL Server表中指定分类内行的位置及该分类总行数

原始数据 #

假设表名为Tokens，数据如下：

Guid    Token   Category    DateCreated
-------------------------------------------
GUID1   TK1     Category1   1/9/2023 21:50
GUID2   TK2     Category1   1/10/2023 21:51
GUID3   TK3     Category1   1/11/2023 21:52
GUID4   TK4     Category1   1/12/2023 21:53
GUID5   TK5     Category1   1/13/2023 21:54
GUID6   TK6     Category1   1/14/2023 21:55
GUID7   TK7     Category1   1/15/2023 21:56
GUID8   TK8     Category1   1/16/2023 21:57
GUID9   TK9     Category1   1/17/2023 21:58
GUID10  TK10    Category1   1/18/2023 21:59
GUID11  TK11    Category1   1/19/2023 22:00
GUID12  TK12    Category2   1/20/2023 22:01
GUID13  TK13    Category2   1/21/2023 22:02
GUID14  TK14    Category2   1/22/2023 22:03
GUID15  TK15    Category2   1/23/2023 22:04
GUID16  TK16    Category3   1/24/2023 22:05
GUID17  TK17    Category3   1/25/2023 22:06
GUID18  TK18    Category3   1/26/2023 22:07
GUID19  TK19    Category3   1/27/2023 22:08

解决方案 #

单个Token查询 #

使用ROW_NUMBER()函数按分类分组并按创建日期排序计算位置，同时通过子查询获取分类总数量：

查询TK8（Category1）

SELECT 
    Token,
    ROW_NUMBER() OVER (PARTITION BY Category ORDER BY DateCreated) AS [Position#],
    (SELECT COUNT(*) FROM Tokens WHERE Category = t.Category) AS OutOftotal
FROM Tokens t
WHERE Token = 'TK8' AND Category = 'Category1';

查询TK5（Category1）

SELECT 
    Token,
    ROW_NUMBER() OVER (PARTITION BY Category ORDER BY DateCreated) AS [Position#],
    (SELECT COUNT(*) FROM Tokens WHERE Category = t.Category) AS OutOftotal
FROM Tokens t
WHERE Token = 'TK5' AND Category = 'Category1';

查询TK17（Category3）

SELECT 
    Token,
    ROW_NUMBER() OVER (PARTITION BY Category ORDER BY DateCreated) AS [Position#],
    (SELECT COUNT(*) FROM Tokens WHERE Category = t.Category) AS OutOftotal
FROM Tokens t
WHERE Token = 'TK17' AND Category = 'Category3';

批量查询多个Token #

如果需要一次性获取多个Token的信息，可通过JOIN预计算分类总数量，再过滤目标Token：

SELECT 
    t.Token,
    ROW_NUMBER() OVER (PARTITION BY t.Category ORDER BY t.DateCreated) AS [Position#],
    cat.TotalCount AS OutOftotal
FROM Tokens t
JOIN (
    SELECT Category, COUNT(*) AS TotalCount
    FROM Tokens
    GROUP BY Category
) cat ON t.Category = cat.Category
WHERE 
    (t.Token = 'TK8' AND t.Category = 'Category1')
    OR (t.Token = 'TK5' AND t.Category = 'Category1')
    OR (t.Token = 'TK17' AND t.Category = 'Category3');

查询结果 #

TK8的结果 #

Token   Position#   OutOftotal
TK8         8           11

TK5的结果 #

Token   Position#   OutOftotal
TK5         5           11

TK17的结果 #

Token   Position#   OutOftotal
TK17        2           4

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