SQL查询优化：同一最优报价下仅保留最早请求记录

需求：生成包含职位信息、对应最优报价及提交该报价的最早请求编号的表格。

原SQL代码：

SELECT PosId   = BidPositionCurrentState.Id
     , PosNo = BidPositionCurrentState.No
     , PosName = BidPositionCurrentState.Name
     , RequestNo = r.No
     , offerprice = bop.offerprice
     , offertime = bop.ActDate
FROM BidPosition bp WITH (NOLOCK)
INNER JOIN BidPositionCurrentState(NOLOCK) ON BidPositionCurrentState.Id = bp.Id
INNER JOIN BidOfferPosition bop (NOLOCK) ON bp.id = bop.BidPositionId
INNER JOIN Request r WITH (NOLOCK) ON r.Id = bop.RequestId AND r.StatusId IN (5, 7)
WHERE bp.BidId = @Parm AND bop.OfferPrice IN (SELECT CASE WHEN Bid.IsIncrease = 0
                                       THEN min(bop.OfferPrice)
                                                              else max(bop.OfferPrice) end 
                                                  from BidOfferPosition bop (NOLOCK)
                                                  where bop.BidPositionId = bp.Id)
ORDER BY PosId

（注：原代码中AND and为笔误，已修正）

问题：当前查询可获取最优报价，但同一职位若有多个请求提交了相同的最优报价，会返回多条重复职位记录。期望同一职位仅保留最早提交该最优报价的请求记录，尝试过DISTINCT及含TOP 1的子查询未解决问题。

解决方案：使用窗口函数筛选最早记录 #

利用ROW_NUMBER()窗口函数，按职位分组，对同一职位下的最优报价记录按提交时间升序排序，取排序后行号为1的记录，即为最早提交的那条。

修改后的SQL代码：

WITH RankedOffers AS (
    SELECT 
        PosId = bpcs.Id,
        PosNo = bpcs.No,
        PosName = bpcs.Name,
        RequestNo = r.No,
        offerprice = bop.offerprice,
        offertime = bop.ActDate,
        -- 按职位分组，报价时间升序排序，行号为1的就是最早提交的
        RowNum = ROW_NUMBER() OVER (PARTITION BY bpcs.Id ORDER BY bop.ActDate ASC)
    FROM BidPosition bp WITH (NOLOCK)
    INNER JOIN BidPositionCurrentState bpcs WITH (NOLOCK) ON bpcs.Id = bp.Id
    INNER JOIN BidOfferPosition bop WITH (NOLOCK) ON bp.Id = bop.BidPositionId
    INNER JOIN Request r WITH (NOLOCK) ON r.Id = bop.RequestId AND r.StatusId IN (5, 7)
    -- 关联Bid表获取IsIncrease字段，用于计算最优报价
    INNER JOIN Bid b WITH (NOLOCK) ON b.Id = bp.BidId
    WHERE bp.BidId = @Parm 
      AND bop.OfferPrice = CASE WHEN b.IsIncrease = 0
                               THEN (SELECT MIN(bo.OfferPrice) FROM BidOfferPosition bo WITH (NOLOCK) WHERE bo.BidPositionId = bp.Id)
                               ELSE (SELECT MAX(bo.OfferPrice) FROM BidOfferPosition bo WITH (NOLOCK) WHERE bo.BidPositionId = bp.Id) END
)
SELECT PosId, PosNo, PosName, RequestNo, offerprice, offertime
FROM RankedOffers
WHERE RowNum = 1
ORDER BY PosId

思路说明： #

1. 用CTE（公共表表达式）RankedOffers关联所有必要表，先筛选出符合最优报价的记录
2. 通过ROW_NUMBER()窗口函数，以职位ID为分组依据（PARTITION BY bpcs.Id），按报价提交时间升序排序（ORDER BY bop.ActDate ASC），给每个分组内的记录分配行号
3. 最后筛选行号为1的记录，得到每个职位下最早提交最优报价的请求信息

另外，将原代码中的IN改为=，因为子查询只会返回单个最优报价值，逻辑更严谨；同时修正了原代码中多余的and语法错误。

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