使用Node.js Sequelize操作MySQL实现带分组小计的自定义查询

现有如下原始数据表格：

需要生成包含合计行的结果：保留所有原始数据行，在每组name和code相同的行下方添加该行组的qnt合计行，最终格式如下：

要求用Node.js的Sequelize操作MySQL实现该查询。

方案一：代码层面处理数据 #

先查询原始数据与分组合计，再在代码中合并两类数据，逻辑直观易修改：

const { Op } = require('sequelize');
const YourModel = require('./models/your-model'); // 替换为你的模型实例
const sequelize = require('./sequelize-instance'); // 替换为你的Sequelize连接实例

async function getWithTotals() {
  // 1. 查询所有原始数据，按name、code排序
  const rawData = await YourModel.findAll({
    attributes: ['name', 'code', 'qnt'],
    order: [['name', 'ASC'], ['code', 'ASC']]
  });

  // 2. 按name+code分组计算qnt合计
  const groupTotals = await YourModel.findAll({
    attributes: [
      'name',
      'code',
      [sequelize.fn('SUM', sequelize.col('qnt')), 'totalQnt']
    ],
    group: ['name', 'code'],
    order: [['name', 'ASC'], ['code', 'ASC']]
  });

  // 3. 合并原始数据与对应合计行
  const finalResult = [];
  let currentGroupKey = null;
  let currentTotal = null;

  for (const row of rawData) {
    const groupKey = `${row.name}-${row.code}`;
    // 切换分组时，先添加上一组的合计行
    if (currentGroupKey !== groupKey) {
      if (currentTotal) {
        finalResult.push({
          name: '-',
          code: '-',
          qnt: currentTotal.dataValues.totalQnt
        });
      }
      currentGroupKey = groupKey;
      currentTotal = groupTotals.find(t => t.name === row.name && t.code === row.code);
    }
    finalResult.push({
      name: row.name,
      code: row.code,
      qnt: row.qnt
    });
  }

  // 添加最后一组的合计行
  if (currentTotal) {
    finalResult.push({
      name: '-',
      code: '-',
      qnt: currentTotal.dataValues.totalQnt
    });
  }

  return finalResult;
}

方案二：自定义SQL直接查询（性能更优） #

通过UNION ALL将原始数据与分组合计数据合并，由数据库完成排序，仅需一次查询：

const sequelize = require('./sequelize-instance'); // 替换为你的Sequelize连接实例

async function getWithTotals() {
  const query = `
    SELECT name, code, qnt, 1 AS sort_order
    FROM your_table_name -- 替换为你的实际表名
    UNION ALL
    SELECT '-' AS name, '-' AS code, SUM(qnt) AS qnt, 2 AS sort_order
    FROM your_table_name
    GROUP BY name, code
    ORDER BY name, code, sort_order
  `;

  const [results] = await sequelize.query(query);
  return results;
}

方案对比 #

• 方案一：逻辑清晰，便于后续调整业务规则，但需要两次数据库查询，适合数据量较小的场景。
• 方案二：仅一次数据库查询，性能更优，适合数据量较大的场景，但需要直接编写SQL，灵活性稍低。

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