如何用Sequelize筛选重复数据中的非首条记录？

需求回顾 #

仅筛选指定字段重复的数据，排除每组重复数据中编号最小的首条记录，获取每组剩余的冗余记录。重复数据定义为指定字段值完全相同的条目（如示例中的name、type、cats）。

示例数据 #

name type cats number
carl two  meow  1
carl two  meow  2
carl bad  meow  1
carl bad  meow  3
carl two  pot   1
sven two  meow  2

期望结果 #

carl two  meow  2
carl bad  meow  3

Sequelize实现方案 #

核心思路是利用Sequelize的literal()方法集成窗口函数ROW_NUMBER()，同时兼容用户自定义的WHERE条件，确保数据库端处理的性能。

基础实现代码 #

假设你的模型为YourModel，需要根据field1、field2、field3判断重复，以number字段排序取首条：

const { Op } = require('sequelize');
const YourModel = require('./models/yourModel'); // 引入你的模型

// 动态指定重复判断字段
const duplicateFields = ['field1', 'field2', 'field3'];
const partitionClause = duplicateFields.map(field => `"${field}"`).join(', ');

// 用户自定义WHERE条件（示例）
const userWhereConditions = {
  // 比如：type: 'two'
};

const duplicateRecords = await YourModel.findAll({
  attributes: [
    // 选择所有模型字段
    ...Object.keys(YourModel.rawAttributes),
    // 添加窗口函数计算行号
    [
      sequelize.literal(`ROW_NUMBER() OVER (
        PARTITION BY ${partitionClause}
        ORDER BY "number" ASC
      )`),
      'row_number'
    ]
  ],
  // 应用用户自定义WHERE条件
  where: userWhereConditions,
  // 过滤行号大于1的冗余记录
  having: sequelize.literal('row_number > 1'),
  // 避免Sequelize自动添加不必要的GROUP BY
  group: sequelize.literal('1=1'),
  // 返回原始数据格式，便于处理
  raw: true
});

兼容复杂查询的进阶写法 #

如果需要更灵活地整合现有Sequelize查询逻辑（如关联查询、排序），可以通过子查询/CTE的方式构建：

// 先构建包含用户条件的基础查询
const baseQuery = YourModel.findAll({
  where: userWhereConditions,
  include: [/* 关联模型（如果需要） */],
  attributes: ['*'],
  raw: true
}).toSql();

// 替换基础查询的SELECT部分，添加行号计算
const cteQuery = baseQuery.replace(
  'SELECT *',
  `SELECT *, ROW_NUMBER() OVER (PARTITION BY ${partitionClause} ORDER BY "number" ASC) AS row_number`
);

// 执行最终CTE查询
const duplicateRecords = await sequelize.query(`
  WITH cte AS (${cteQuery})
  SELECT * FROM cte WHERE row_number > 1
`, { type: sequelize.QueryTypes.SELECT, raw: true });

关键注意事项 #

1. 数据库兼容性：确保使用的数据库支持窗口函数（MySQL 8.0+、PostgreSQL、SQL Server等）。
2. 字段引号：根据数据库类型调整字段的引号（PostgreSQL用双引号，MySQL用反引号）。
3. 动态字段：通过duplicateFields数组可以灵活切换重复判断字段，适配不同业务场景。
4. 性能保障：所有逻辑在数据库端执行，避免内存中处理大数据量，符合性能要求。

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