SQL实现拍卖系统排除撤回前出价的最高有效出价查询

假设存储拍卖记录的表名为auction_records，查询入参@TargetProduct为指定要查询的商品名称，SQL语句如下：

WITH user_last_withdraw AS (
    -- 先查询指定商品下每个用户的最晚撤回时间
    SELECT
        User,
        MAX(CreatedAt) AS wit_time
    FROM auction_records
    WHERE ProductName = @TargetProduct
      AND Type = 'WIT'
    GROUP BY User
)
SELECT
    'BID' AS Type,
    @TargetProduct AS ProductName,
    r.User,
    MAX(r.Value) AS Value,
    MAX(r.CreatedAt) AS CreatedAt
FROM auction_records r
LEFT JOIN user_last_withdraw uw
    ON r.User = uw.User
WHERE r.ProductName = @TargetProduct
  AND r.Type = 'BID'
  -- 仅保留撤回时间之后的出价，无撤回记录的用户所有出价都有效
  AND r.CreatedAt > IFNULL(uw.wit_time, '1970-01-01 00:00:00')
GROUP BY r.User
ORDER BY Value DESC;

逻辑说明 #

1. 先用CTE查询目标商品下所有提交过撤回请求的用户的最晚撤回时间，没有撤回记录的用户不会出现在该CTE结果中
2. 关联原表过滤有效出价：仅保留出价时间大于对应用户撤回时间的记录，无撤回记录的用户所有出价都符合要求
3. 按用户分组取最高出价即可，因为业务层已保证出价递增，取MAX(Value)或MAX(CreatedAt)对应的出价结果完全一致

兼容调整 #

如果使用PostgreSQL/Oracle数据库，将IFNULL替换为标准函数COALESCE即可正常运行。

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