Sequelize中用group by进行分组聚合查询

一、SQL与Sequelize中的分组查询 1.1 SQL中的分组查询 SQL查询中，通GROUP BY语名实现分组查询。GROUP BY子句要和聚合函数配合使用才能完成分组查询，在SELECT查询的字段中，如果没有使用聚合函数就必须出现在ORDER BY子句中。分组查询后，查询结果为一个或多个列分组后的结果集。 GROUP BY语法 SELECT 列名, 聚合函数(列名) FROM 表名 WHERE 列名 operator value GROUP BY 列名 [HAVING 条件表达式] [WITH ROLLUP] 在以上语句中： 聚合函数 – 分组查询通常要与聚合函数一起使用，聚合函 Sequelize是一个强大的ORM（对象关系映射）库，它为Node.js应用程序提供了一个与多种数据库交互的接口，包括MySQL、PostgreSQL和SQLite等。在SQL中，分组查询是通过`GROUP BY`子句来实现的，它可以将数据按照一个或多个列的值进行分组，通常结合聚合函数一起使用，以便对每个分组执行计算。 1. SQL中的分组查询 在SQL中，使用`GROUP BY`语句进行分组操作，例如： ```sql SELECT 列名, 聚合函数(列名) FROM 表名 WHERE 列名 operator value GROUP BY 列名 [HAVING 条件表达式] [WITH ROLLUP] ``` 这里，`GROUP BY`后的列名用于确定分组，`HAVING`用于在分组后对结果进行过滤，`WITH ROLLUP`则用于添加额外的汇总行。常见的聚合函数有： - `COUNT()`：统计记录条数 - `SUM()`：计算字段的值的总和 - `AVG()`：计算字段的值的平均值 - `MAX()`：找出查询字段的最大值 - `MIN()`：找出查询字段的最小值 2. Sequelize中的分组查询 在Sequelize中，我们可以利用内置的聚合函数来实现类似的分组查询。例如： - `aggregate(field, aggregateFunction, [options])`：通用聚合查询方法，可以用于`COUNT()`, `SUM()`, `AVG()`, `MAX()`, `MIN()`等。 - `sum(field, [options])`：用于求和 - `count(field, [options])`：用于统计查询结果数 - `max(field, [options])`：用于查找最大值 - `min(field, [options])`：用于查找最小值 在Sequelize的聚合查询中，可以通过`options.attributes`指定分组相关的字段，使用`options.having`来设定分组后的过滤条件。例如，要找到订单数量大于1的用户的订单总额，可以这样写： ```javascript Order.sum('price', { attributes: ['name'], group: 'name', plain: false, having: ['COUNT(?) > ?', 'name', 1], }).then(function (result) { console.log(result); }); ``` 同样，你也可以在`findAll()`等查询方法中设置聚合查询的相关参数。在这种情况下，需要使用`sequelize.fn()`来指定聚合函数，例如： ```javascript Order.findAll({ attributes: ['name', [sequelize.fn('SUM', sequelize.col('price')), 'sum']], group: 'name', having: ['COUNT(?) > ?', 'name', 1], raw: true, }).then(function (result) { console.log(result); }); ``` 3. 使用示例 假设有一个名为`orders`的订单表，包含以下字段：`orderId`, `orderNumber`, `price`, `name`, `createdOn`。你可以使用Sequelize的分组查询功能来获取特定条件下的数据，例如，找出每个用户的所有订单的总价，且只有当用户有超过一个订单时才显示： ```javascript Order.findAll({ attributes: ['name', [sequelize.fn('SUM', sequelize.col('price')), 'totalPrice']], group: 'name', having: ['COUNT(*) > ?', 1], raw: true, }).then(function (result) { console.log(result); }); ``` 这段代码将生成如下SQL： ```sql SELECT `name`, sum(`price`) AS `totalPrice` FROM `orders` AS `Orders` GROUP BY `name` HAVING COUNT(*) > 1; ``` Sequelize提供了方便的API来处理SQL中的分组查询，允许开发者在不直接编写SQL的情况下进行聚合和分组操作。通过灵活地配置`options`对象，可以实现复杂的查询需求，同时保持代码的整洁和可维护性。