SQL Server按LinkID分组筛选优先级最低的PENDING状态数据

数据表结构与数据 #

现有数据表lenderdata，结构及初始化数据如下：

create table lenderdata
(
    ID int identity
        primary key,
    LinkID varchar(250) null,
    Lender varchar(250) null,
    Item varchar(250) null,
    Priority int null,
    Quantity int null,
    Status varchar(250) null
);

INSERT INTO lenderdata (LinkID, Lender, Item, Priority, Quantity, Status) VALUES ('001', 'A', 'Apple', 1, 100, 'PENDING');
INSERT INTO lenderdata (LinkID, Lender, Item, Priority, Quantity, Status) VALUES ('001', 'B', 'Orange', 2, 100, 'PENDING');
INSERT INTO lenderdata (LinkID, Lender, Item, Priority, Quantity, Status) VALUES ('002', 'C', 'Strawberry', 1, 1000, 'PENDING');
INSERT INTO lenderdata (LinkID, Lender, Item, Priority, Quantity, Status) VALUES ('002', 'D', 'grapes', 2, 100, 'PENDING');
INSERT INTO lenderdata (LinkID, Lender, Item, Priority, Quantity, Status) VALUES ('003', 'E', 'coffee', 1, 1000, 'PROCESSING');
INSERT INTO lenderdata (LinkID, Lender, Item, Priority, Quantity, Status) VALUES ('003', 'F', 'mango', 2, 1000, 'PENDING');

查询需求 #

按LinkID分组：

• 仅当分组内所有记录的Status均为PENDING时，选取该分组中Priority最小的记录；
• 若分组内存在PROCESSING状态的记录，则忽略整个分组。

最终需返回LinkID为001和002的对应记录。

解决方案 #

方法一：用CTE筛选有效分组 #

WITH ValidLinkGroups AS (
    SELECT LinkID
    FROM lenderdata
    GROUP BY LinkID
    -- 全为PENDING时MAX(Status)是PENDING，存在PROCESSING则结果为PROCESSING
    HAVING MAX(Status) = 'PENDING'
)
SELECT ld.*
FROM lenderdata ld
JOIN ValidLinkGroups vg ON ld.LinkID = vg.LinkID
WHERE ld.Priority = (
    SELECT MIN(Priority)
    FROM lenderdata
    WHERE LinkID = ld.LinkID
);

方法二：用窗口函数标记筛选 #

SELECT ID, LinkID, Lender, Item, Priority, Quantity, Status
FROM (
    SELECT *,
           -- 标记当前分组是否含PROCESSING记录
           MAX(CASE WHEN Status = 'PROCESSING' THEN 1 ELSE 0 END) OVER (PARTITION BY LinkID) AS HasProcessing,
           -- 按Priority升序给分组内记录编号，最小优先级记录编号为1
           ROW_NUMBER() OVER (PARTITION BY LinkID ORDER BY Priority ASC) AS PriorityRank
    FROM lenderdata
) FilteredData
WHERE HasProcessing = 0 AND PriorityRank = 1;

逻辑说明 #

两种方法均先排除包含PROCESSING状态的LinkID分组，再从有效分组中筛选出Priority最小的记录，完全匹配需求。

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