SQL GroupBy查询：获取各商品最低价、同价取最新记录方案

你原有SQL的问题是仅通过子查询匹配了每个商品的最低挂牌价，没有对相同最低价下的创建时间做二次筛选，因此会返回同一商品下所有价格等于最低价的挂牌记录。以下是可直接落地的方案：

方案1：无需修改表结构，调整SQL逻辑 #

该方案不需要调整现有表结构，适配绝大多数业务场景，分两种实现方式：

• 窗口函数实现（推荐）：代码简洁，逻辑清晰，兼容MySQL 8.0+、PostgreSQL、SQLite 3.25+等所有支持SQL标准窗口函数的数据库

SELECT 
  l.id,
  l.item_id,
  i.name,
  l.price,
  l.created_at
FROM (
  SELECT 
    *,
    ROW_NUMBER() OVER (
      PARTITION BY item_id 
      ORDER BY CAST(price AS DECIMAL) ASC, created_at DESC, id DESC
    ) AS rn
  FROM listings
  WHERE sold_at IS NULL AND expires_at > ?
) l
INNER JOIN items i ON l.item_id = i.id
WHERE l.rn = 1
ORDER BY CAST(l.price AS DECIMAL) ASC
LIMIT ?;

逻辑说明：通过PARTITION BY item_id将挂牌记录按商品分组，组内按「价格升序→创建时间降序→主键id降序」规则排序，每个分组只取排序第1的记录，天然满足「最低价优先、同价取最新」的要求，末尾加id DESC是为了极端场景下（同商品同价格同时间戳出现多条记录）也能保证只返回唯一一条结果。

• 聚合子查询实现（兼容老版本数据库）：如果你的数据库版本不支持窗口函数，可以在原有聚合逻辑基础上增加同价下最大创建时间的匹配

SELECT 
  l.id,
  l.item_id,
  i.name,
  l.price,
  l.created_at
FROM listings l
INNER JOIN items i ON l.item_id = i.id
INNER JOIN (
  SELECT 
    item_id,
    MIN(price) AS min_price,
    MAX(created_at) AS latest_created
  FROM listings
  WHERE sold_at IS NULL AND expires_at > ?
  GROUP BY item_id
) grouped_l 
  ON l.item_id = grouped_l.item_id 
  AND l.price = grouped_l.min_price
  AND l.created_at = grouped_l.latest_created
WHERE l.sold_at IS NULL AND l.expires_at > ?
ORDER BY CAST(l.price AS DECIMAL) ASC
LIMIT ?;

注意：该写法在同商品同价格同创建时间的极端场景下仍可能返回多条记录，需要额外加主键匹配兜底。

方案2：调整表结构优化大表查询性能 #

如果你的listings表数据量达到百万级以上，上述分组查询性能无法满足要求，可以通过新增冗余字段的方式优化查询：

1. 给listings表新增is_current_lowest字段，类型为BOOLEAN/TINYINT，默认值为0，给该字段加索引
2. 维护字段值的规则：

• 新增挂牌记录时：如果新记录价格低于同商品当前有效挂牌的最低价，将同商品其他所有有效记录的is_current_lowest设为0，新记录设为1；如果新记录价格等于当前最低价，将原最低价记录的is_current_lowest设为0，新记录设为1；如果新记录价格更高，直接设为0
• 挂牌记录售出/过期时：如果该记录的is_current_lowest为1，重新计算同商品下有效挂牌的最低价最新记录，将对应记录的is_current_lowest设为1
  调整后查询逻辑极简，可直接走索引，性能极高：

SELECT 
  l.id,
  l.item_id,
  i.name,
  l.price,
  l.created_at
FROM listings l
INNER JOIN items i ON l.item_id = i.id
WHERE l.is_current_lowest = 1
  AND l.sold_at IS NULL 
  AND l.expires_at > ?
ORDER BY CAST(l.price AS DECIMAL) ASC
LIMIT ?;

Objection.js 写法参考（窗口函数版本） #

// 假设Listing模型已经建立了和Item模型的关联关系
const result = await Listing.query()
  .select(
    'listings.id',
    'listings.item_id',
    'items.name',
    'listings.price',
    'listings.created_at'
  )
  .joinRelated('items')
  .whereRaw(`
    listings.id IN (
      SELECT id FROM (
        SELECT id, ROW_NUMBER() OVER (
          PARTITION BY item_id 
          ORDER BY CAST(price AS DECIMAL) ASC, created_at DESC, id DESC
        ) rn
        FROM listings
        WHERE sold_at IS NULL AND expires_at > ?
      ) ranked_listings WHERE rn = 1
    )
  `, [currentTimestamp])
  .orderByRaw('CAST(price AS DECIMAL) ASC')
  .limit(limitCount)

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