Sequelize中按BaseProductId分组筛选最优产品的排序问题

我有一个Products表，与BaseProducts表关联。现在要获取Products列表，要求按baseProductId分组，每组返回库存最多、价格最低、名称按字母排序的产品。但使用Sequelize的order配置后未达到预期效果，分组后返回的结果是随机的。

我的Sequelize代码 #

const e = await Product.findAll({
    order: [
        ["quantity", "DESC"],
        ["price", "ASC"],
        ["name", "ASC"]
    ],
    group: "baseProductId",
    limit:  100,
    offset: 0,
});

生成的SQL语句 #

SELECT `id`, `name`, `nameEn`, `sku`, `barcode`, `quantity`, `price`, `finalPrice`, `sort`, `status`, `kianSync`, `createdAt`, `updatedAt`, `deletedAt`, `baseProductId` FROM `Products` AS `Product` 
WHERE (`Product`.`deletedAt` IS NULL)
GROUP BY `baseProductId` 
ORDER BY `Product`.`quantity` DESC, `Product`.`price` ASC, `Product`.`name` ASC 
LIMIT 0, 100;

数据示例 #

分组后结果 #

[
        {
            "id": "3d2d181a-23f9-47b6-abee-ab61d1a86d02",
            "name": "rrrrrrrr",
            "nameEn": "nameEn",
            "sku": "sku",
            "barcode": "barcode",
            "quantity": 1,
            "price": 1000,
            "finalPrice": 1000,
            "sort": 1,
            "status": "status",
            "kianSync": 1,
            "createdAt": "2023-01-02T06:53:26.000Z",
            "updatedAt": "2023-01-02T06:53:26.000Z",
            "deletedAt": null,
            "baseProductId": null
        }
    ]

未分组结果 #

[
        {
            "id": "83939936-2c40-4921-9046-e40a1313bf77",
            "name": "rrrrrrrr",
            "nameEn": "nameEn",
            "sku": "sku",
            "barcode": "barcode",
            "quantity": 2,
            "price": 1000,
            "finalPrice": 1000,
            "sort": 2,
            "status": "status",
            "kianSync": 1,
            "createdAt": "2023-01-02T06:53:26.000Z",
            "updatedAt": "2023-01-02T06:53:26.000Z",
            "deletedAt": null,
            "baseProductId": null
        },
        {
            "id": "8184b96c-1175-4de2-98c4-9faf231dabdc",
            "name": "rrrrrrrr",
            "nameEn": "nameEn",
            "sku": "sku",
            "barcode": "barcode",
            "quantity": 2,
            "price": 2000,
            "finalPrice": 2000,
            "sort": 3,
            "status": "status",
            "kianSync": 1,
            "createdAt": "2023-01-02T06:53:26.000Z",
            "updatedAt": "2023-01-02T06:53:26.000Z",
            "deletedAt": null,
            "baseProductId": null
        },
        {
            "id": "3d2d181a-23f9-47b6-abee-ab61d1a86d02",
            "name": "rrrrrrrr",
            "nameEn": "nameEn",
            "sku": "sku",
            "barcode": "barcode",
            "quantity": 1,
            "price": 1000,
            "finalPrice": 1000,
            "sort": 1,
            "status": "status",
            "kianSync": 1,
            "createdAt": "2023-01-02T06:53:26.000Z",
            "updatedAt": "2023-01-02T06:53:26.000Z",
            "deletedAt": null,
            "baseProductId": null
        }
    ]

问题根源 #

当前写法逻辑错误：SQL中的GROUP BY是先分组再排序，但分组时（以MySQL为例）会随机选取每组内的一条数据，后续的ORDER BY仅对分组后的结果排序，无法保证每组选到的是符合库存最多、价格最低规则的记录。

正确实现方案 #

方案一：使用窗口函数（推荐） #

通过ROW_NUMBER()窗口函数，按baseProductId分组后给每组内的记录按规则排名，再筛选出排名第一的记录，这是最可靠的实现方式。

示例代码：

const products = await Product.findAll({
  attributes: [
    'id', 'name', 'nameEn', 'sku', 'barcode', 'quantity', 'price', 
    'finalPrice', 'sort', 'status', 'kianSync', 'createdAt', 'updatedAt', 
    'deletedAt', 'baseProductId',
    [sequelize.literal(`ROW_NUMBER() OVER (
      PARTITION BY baseProductId 
      ORDER BY quantity DESC, price ASC, name ASC
    )`), 'row_rank']
  ],
  where: { deletedAt: null },
  having: sequelize.where(sequelize.col('row_rank'), 1),
  limit: 100,
  offset: 0
});

该代码会先给每组记录按规则排名，再筛选出排名第一的记录，确保每组返回的是符合需求的产品。

方案二：先排序再分组（仅部分数据库适用） #

部分数据库支持先排序再分组，但MySQL默认可能因优化器重排执行顺序导致失效，可尝试通过子查询实现：

const products = await Product.findAll({
  attributes: ['id', 'name', 'nameEn', 'sku', 'barcode', 'quantity', 'price', 'finalPrice', 'sort', 'status', 'kianSync', 'createdAt', 'updatedAt', 'deletedAt', 'baseProductId'],
  where: { deletedAt: null },
  from: sequelize.literal(`(SELECT * FROM Products ORDER BY quantity DESC, price ASC, name ASC) AS sorted_products`),
  group: 'baseProductId',
  limit: 100,
  offset: 0
});

注意：此方法在部分MySQL配置下可能不生效，优先推荐方案一。

验证结果 #

使用方案一的代码，针对你提供的未分组数据，baseProductId为null的组会选出id:83939936-2c40-4921-9046-e40a1313bf77的记录，该记录库存最高（quantity=2）且价格最低（price=1000），完全符合需求。

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