一、SQL与Sequelize中的分组查询
1.1 SQL中的分组查询
SQL查询中，通
GROUP BY
语名实现分组查询。
GROUP BY
子句要和聚合函数配合使用才能完成分组查询，在
SELECT
查询的字段中，如果没有使用聚合函数就必须出现在
ORDER BY
子句中。分组查询后，查询结果为一个或多个列分组后的结果集。
GROUP BY
语法
GROUP BY
子名-用于指定分组的字段
HAVING
子名-用于过滤分组结果，符合条件表达式的结果将会被显示
WITH ROLLUP
子名-用于指定追加一条记录，用于汇总前面的数据
1.2 Sequelize中的分组查询
使用聚合函数
Sequelize提供了聚合函数，可以直接对模型进行聚合查询：
aggregate(field, aggregateFunction, [options])-通过指定的聚合函数进行查询
sum(field, [options])-求和
count(field, [options])-统计查询结果数
max(field, [options])-查询最大值
min(field, [options])-查询最小值
以上这些聚合函数中，可以通过
options.attributes
、
options.attributes
属性指定分组相关字段，并可以通过
options.having
指定过滤条件，但没有直接指定
WITH ROLLUP
子句的参数。
如，使用
.sum()
查询订单数量大于1的用户订单额：
Order.sum(
'price'
, {attributes:[
'name'
], group:
'name'
, plain:
false
, having:[
'COUNT(?)>?'
,
'name'
, 1]}).then(
function
(result){
console.log(result);
使用聚合参数
除直接使用聚合函数外，也可以在
findAll()
等方法中，指定聚合查询相关参数实现聚合查询。查询时，同样可以通过通过
options.attributes
、
options.attributes
属性指定分组相关字段，并可以通过
options.having
指定过滤条件。与直接使用聚合函数查询不一样，通过参数构建聚合查询时，要以数组或对象形式设置
options.attributes
参数中的聚合字段，并需要通过
sequelize.fn()
方法传入聚合函数。
如，使用
.findAll()
查询订单数量大于1的用户订单额：
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);
>
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 |
+
>
select
name
,
SUM
(price)
from
orders
GROUP
BY
name
;
+
|
name
|
SUM
(price) |
+
| 张小三 | 230.00 |
| 王小五 | 99.00 |
| 赵小六 | 199.00 |
+
Order.findAll({attributes:[
'sum'
, [sequelize.fn(
'SUM'
, sequelize.col(
'name'
)),
'sum'
]], group:
'name'
, raw:
true
}).then(
function
(result){
console.log(result);
>
select
name
,
SUM
(price)
from
orders
GROUP
BY
name
HAVING
count
(1)>1;
+
|
name
|
SUM
(price) |
+
| 张小三 | 230.00 |
| 赵小六 | 199.00 |
+
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);
>
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 连接查询与分组
为了管理方便，我们会将不同的信息保存在不同的表中。如，我们会将订单信息放在一张表中，而将客户信息保存在另一张表中。对于存在关联关系的两张表，我们会使用连接查询来查找关联数据，在进行连接查询时，同样可以以使用聚合函数。
订单表如下：
>
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 |
+
>
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 |
+
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);
