html5中文学习网

您的位置: 首页 > 网站及特效实例 > javascript特效 » 正文

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

[ ] 已经帮助:人解决问题

一、SQL与Sequelize中的分组查询FxcHTML5中文学习网 - HTML5先行者学习网
FxcHTML5中文学习网 - HTML5先行者学习网

1.1 SQL中的分组查询FxcHTML5中文学习网 - HTML5先行者学习网
FxcHTML5中文学习网 - HTML5先行者学习网

SQL查询中,通GROUP BY语名实现分组查询。GROUP BY子句要和聚合函数配合使用才能完成分组查询,在SELECT查询的字段中,如果没有使用聚合函数就必须出现在ORDER BY子句中。分组查询后,查询结果为一个或多个列分组后的结果集。FxcHTML5中文学习网 - HTML5先行者学习网

GROUP BY语法FxcHTML5中文学习网 - HTML5先行者学习网

SELECT 列名, 聚合函数(列名)FROM 表名WHERE 列名 operator valueGROUP BY 列名 [HAVING 条件表达式] [WITH ROLLUP]

在以上语句中:FxcHTML5中文学习网 - HTML5先行者学习网

聚合函数 - 分组查询通常要与聚合函数一起使用,聚合函数包括:FxcHTML5中文学习网 - HTML5先行者学习网
FxcHTML5中文学习网 - HTML5先行者学习网

  •      COUNT()-用于统计记录条数
  •      SUM()-用于计算字段的值的总和
  •      AVG()-用于计算字段的值的平均值
  •      MAX-用于查找查询字段的最大值
  •      MIX-用于查找查询字段的最小值FxcHTML5中文学习网 - HTML5先行者学习网

GROUP BY子名-用于指定分组的字段FxcHTML5中文学习网 - HTML5先行者学习网
FxcHTML5中文学习网 - HTML5先行者学习网

HAVING子名-用于过滤分组结果,符合条件表达式的结果将会被显示FxcHTML5中文学习网 - HTML5先行者学习网
FxcHTML5中文学习网 - HTML5先行者学习网

WITH ROLLUP子名-用于指定追加一条记录,用于汇总前面的数据FxcHTML5中文学习网 - HTML5先行者学习网

1.2 Sequelize中的分组查询FxcHTML5中文学习网 - HTML5先行者学习网
FxcHTML5中文学习网 - HTML5先行者学习网

使用聚合函数FxcHTML5中文学习网 - HTML5先行者学习网

Sequelize提供了聚合函数,可以直接对模型进行聚合查询:FxcHTML5中文学习网 - HTML5先行者学习网

  1. aggregate(field, aggregateFunction, [options])-通过指定的聚合函数进行查询
  2. sum(field, [options])-求和
  3. count(field, [options])-统计查询结果数
  4. max(field, [options])-查询最大值
  5. min(field, [options])-查询最小值FxcHTML5中文学习网 - HTML5先行者学习网

以上这些聚合函数中,可以通过options.attributesoptions.attributes属性指定分组相关字段,并可以通过options.having指定过滤条件,但没有直接指定WITH ROLLUP子句的参数。FxcHTML5中文学习网 - HTML5先行者学习网

如,使用.sum()查询订单数量大于1的用户订单额:FxcHTML5中文学习网 - HTML5先行者学习网

Order.sum('price', {attributes:['name'], group:'name', plain:false, having:['COUNT(?)>?', 'name', 1]}).then(function(result){ console.log(result);})

生成的SQL语句如下:FxcHTML5中文学习网 - HTML5先行者学习网

SELECT `name`, sum(`price`) AS `sum` FROM `orders` AS `Orders` GROUP BY name HAVING COUNT('name')>1;

使用聚合参数FxcHTML5中文学习网 - HTML5先行者学习网

除直接使用聚合函数外,也可以在findAll()等方法中,指定聚合查询相关参数实现聚合查询。查询时,同样可以通过通过options.attributesoptions.attributes属性指定分组相关字段,并可以通过options.having指定过滤条件。与直接使用聚合函数查询不一样,通过参数构建聚合查询时,要以数组或对象形式设置options.attributes参数中的聚合字段,并需要通过sequelize.fn()方法传入聚合函数。FxcHTML5中文学习网 - HTML5先行者学习网

如,使用.findAll()查询订单数量大于1的用户订单额:FxcHTML5中文学习网 - HTML5先行者学习网

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);})

生成的SQL语句如下:FxcHTML5中文学习网 - HTML5先行者学习网

SELECT `name`, sum(`price`) AS `sum` FROM `orders` AS `Orders` GROUP BY name HAVING COUNT('name')>1;

二、使用示例FxcHTML5中文学习网 - HTML5先行者学习网
FxcHTML5中文学习网 - HTML5先行者学习网

现在订单表,数据如下:FxcHTML5中文学习网 - HTML5先行者学习网

> select * from orders;+---------+-------------+--------+-----------+---------------------+| orderId | orderNumber | price | name  | createdOn   |+---------+-------------+--------+-----------+---------------------+|  1 | 00001  | 128.00 | 张小三 | 2016-11-25 10:12:49 ||  2 | 00002  | 102.00 | 张小三 | 2016-11-25 10:12:49 ||  4 | 00004  | 99.00 | 王小五 | 2016-11-25 10:12:49 ||  3 | 00003  | 199.00 | 赵小六 | 2016-11-25 10:12:49 |+---------+-------------+--------+-----------+---------------------+

2.1 简单使用FxcHTML5中文学习网 - HTML5先行者学习网
FxcHTML5中文学习网 - HTML5先行者学习网

使用分组查询,统计每个客户的订单总额。FxcHTML5中文学习网 - HTML5先行者学习网

使用SQL语句,可以像下面这样查询:FxcHTML5中文学习网 - HTML5先行者学习网

> select name, SUM(price) from orders GROUP BY name;+-----------+------------+| name  | SUM(price) |+-----------+------------+| 张小三 |  230.00 || 王小五 |  99.00 || 赵小六 |  199.00 |+-----------+------------+

而在Sequelize中可以像下面这样实现:FxcHTML5中文学习网 - HTML5先行者学习网

Order.findAll({attributes:['sum', [sequelize.fn('SUM', sequelize.col('name')), 'sum']], group:'name', raw:true}).then(function(result){ console.log(result);})

2.2 使用HAVING子句FxcHTML5中文学习网 - HTML5先行者学习网
FxcHTML5中文学习网 - HTML5先行者学习网

统计订单数量大于1的用户的订单总金额。FxcHTML5中文学习网 - HTML5先行者学习网

使用SQL语句,可以像下面这样实现:FxcHTML5中文学习网 - HTML5先行者学习网

> select name, SUM(price) from orders GROUP BY name HAVING count(1)>1;+-----------+------------+| name  | SUM(price) |+-----------+------------+| 张小三 |  230.00 || 赵小六 |  199.00 |+-----------+------------+

而使用Sequelize可以像下面这样查询:FxcHTML5中文学习网 - HTML5先行者学习网

Order.findAll({attributes:['sum', [sequelize.fn('SUM', sequelize.col('name')), 'sum']], group:'name', having:['COUNT(?)>?', 'name', 1], raw:true}).then(function(result){ console.log(result);})

2.3 使用WITH ROLLUP子句FxcHTML5中文学习网 - HTML5先行者学习网
FxcHTML5中文学习网 - HTML5先行者学习网

WITH ROLLUP子句是MySQL 5.5+新增的特性,用于汇总统计结果。但本文发布时,Sequelize还不支持该特性。FxcHTML5中文学习网 - HTML5先行者学习网

增加总和统计列:FxcHTML5中文学习网 - HTML5先行者学习网

> select name, SUM(price) from orders GROUP BY name WITH ROLLUP;+-----------+------------+| name  | SUM(price) |+-----------+------------+| 张小三 |  230.00 || 王小五 |  99.00 || 赵小六 |  199.00 || NULL  |  528.00 |+-----------+------------+

2.4 连接查询与分组FxcHTML5中文学习网 - HTML5先行者学习网
FxcHTML5中文学习网 - HTML5先行者学习网

为了管理方便,我们会将不同的信息保存在不同的表中。如,我们会将订单信息放在一张表中,而将客户信息保存在另一张表中。对于存在关联关系的两张表,我们会使用连接查询来查找关联数据,在进行连接查询时,同样可以以使用聚合函数。FxcHTML5中文学习网 - HTML5先行者学习网

订单表如下:FxcHTML5中文学习网 - HTML5先行者学习网

> select * from orders;+---------+-------------+--------+------------+---------------------+| orderId | orderNumber | price | customerId | createdOn   |+---------+-------------+--------+------------+---------------------+|  1 | 00001  | 128.00 |   1 | 2016-11-25 10:12:49 ||  2 | 00002  | 102.00 |   1 | 2016-11-25 10:12:49 ||  3 | 00003  | 199.00 |   4 | 2016-11-25 10:12:49 ||  4 | 00004  | 99.00 |   3 | 2016-11-25 10:12:49 |+---------+-------------+--------+------------+---------------------+

客户表结构如下:FxcHTML5中文学习网 - HTML5先行者学习网

> select * from customers;+----+-----------+-----+---------------------+---------------------+| id | name  | sex | birthday   | createdOn   |+----+-----------+-----+---------------------+---------------------+| 1 | 张小三 | 1 | 1986-01-22 08:00:00 | 2016-11-25 10:16:35 || 2 | 李小四 | 2 | 1987-11-12 08:00:00 | 2016-11-25 10:16:35 || 3 | 王小五 | 1 | 1988-03-08 08:00:00 | 2016-11-25 10:16:35 || 4 | 赵小六 | 1 | 1989-08-11 08:00:00 | 2016-11-25 10:16:35 |+----+-----------+-----+---------------------+---------------------+

使用连接查询并分组查询,统计每个客户的订单总额。FxcHTML5中文学习网 - HTML5先行者学习网

使用SQL语句查询如下:FxcHTML5中文学习网 - HTML5先行者学习网

> select c.name, SUM(o.price) AS sum from customers AS c INNER JOIN orders AS o ON o.customerId =c.id GROUP BY c.name;

Sequelize中进行连接查询时,首先需要建立模型间的关联关系:FxcHTML5中文学习网 - HTML5先行者学习网

Order.belongsTo(Customer, {foreignKey: 'customerId'});

连接查询及分组:FxcHTML5中文学习网 - HTML5先行者学习网

var include = [{ model: Customer, required: true, attributes: ['name'],}]Order.findAll({include:include, attributes:[[sequelize.fn('SUM', sequelize.col('price')), 'sum']], group:'Customer.name', having:['COUNT(?)>?', 'name', 1], raw:true, rollup:true}).then(function(result){ console.log(result);})

总结FxcHTML5中文学习网 - HTML5先行者学习网

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作能带来一定的帮助,如果有疑问大家可以留言交流。FxcHTML5中文学习网 - HTML5先行者学习网

(责任编辑:)
推荐书籍
推荐资讯
关于HTML5先行者 - 联系我们 - 广告服务 - 友情链接 - 网站地图 - 版权声明 - 人才招聘 - 帮助