一、SQL與Sequelize中的分組查詢
1.1 SQL中的分組查詢
SQL查詢中，通GROUP BY語名實(shí)現(xiàn)分組查詢。GROUP BY子句要和聚合函數(shù)配合使用才能完成分組查詢，在SELECT查詢的字段中，如果沒有使用聚合函數(shù)就必須出現(xiàn)在ORDER BY子句中。分組查詢后，查詢結(jié)果為一個(gè)或多個(gè)列分組后的結(jié)果集。
GROUP BY語法
SELECT 列名, 聚合函數(shù)(列名)FROM 表名WHERE 列名 operator valueGROUP BY 列名 [HAVING 條件表達(dá)式] [WITH ROLLUP]
在以上語句中：
聚合函數(shù) - 分組查詢通常要與聚合函數(shù)一起使用，聚合函數(shù)包括：
GROUP BY子名-用于指定分組的字段
HAVING子名-用于過濾分組結(jié)果，符合條件表達(dá)式的結(jié)果將會(huì)被顯示
WITH ROLLUP子名-用于指定追加一條記錄，用于匯總前面的數(shù)據(jù)
1.2 Sequelize中的分組查詢
使用聚合函數(shù)
Sequelize提供了聚合函數(shù)，可以直接對(duì)模型進(jìn)行聚合查詢：
以上這些聚合函數(shù)中，可以通過options.attributes、options.attributes屬性指定分組相關(guān)字段，并可以通過options.having指定過濾條件，但沒有直接指定WITH ROLLUP子句的參數(shù)。
如，使用.sum()查詢訂單數(shù)量大于1的用戶訂單額：
Order.sum('price', {attributes:['name'], group:'name', plain:false, having:['COUNT(?)>?', 'name', 1]}).then(function(result){ console.log(result);})生成的SQL語句如下：
SELECT `name`, sum(`price`) AS `sum` FROM `orders` AS `Orders` GROUP BY name HAVING COUNT('name')>1;使用聚合參數(shù)
除直接使用聚合函數(shù)外，也可以在findAll()等方法中，指定聚合查詢相關(guān)參數(shù)實(shí)現(xiàn)聚合查詢。查詢時(shí)，同樣可以通過通過options.attributes、options.attributes屬性指定分組相關(guān)字段，并可以通過options.having指定過濾條件。與直接使用聚合函數(shù)查詢不一樣，通過參數(shù)構(gòu)建聚合查詢時(shí)，要以數(shù)組或?qū)ο笮问皆O(shè)置options.attributes參數(shù)中的聚合字段，并需要通過sequelize.fn()方法傳入聚合函數(shù)。
如，使用.findAll()查詢訂單數(shù)量大于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);})生成的SQL語句如下：
SELECT `name`, sum(`price`) AS `sum` FROM `orders` AS `Orders` GROUP BY name HAVING COUNT('name')>1;二、使用示例
現(xiàn)在訂單表，數(shù)據(jù)如下：
> 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 簡(jiǎn)單使用
使用分組查詢，統(tǒng)計(jì)每個(gè)客戶的訂單總額。
使用SQL語句，可以像下面這樣查詢：
> select name, SUM(price) from orders GROUP BY name;+-----------+------------+| name | SUM(price) |+-----------+------------+| 張小三 | 230.00 || 王小五 | 99.00 || 趙小六 | 199.00 |+-----------+------------+
而在Sequelize中可以像下面這樣實(shí)現(xiàn)：
Order.findAll({attributes:['sum', [sequelize.fn('SUM', sequelize.col('name')), 'sum']], group:'name', raw:true}).then(function(result){ console.log(result);})2.2 使用HAVING子句
統(tǒng)計(jì)訂單數(shù)量大于1的用戶的訂單總金額。
使用SQL語句，可以像下面這樣實(shí)現(xiàn)：
> select name, SUM(price) from orders GROUP BY name HAVING count(1)>1;+-----------+------------+| name | SUM(price) |+-----------+------------+| 張小三 | 230.00 || 趙小六 | 199.00 |+-----------+------------+
而使用Sequelize可以像下面這樣查詢：
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子句
WITH ROLLUP子句是MySQL 5.5+新增的特性，用于匯總統(tǒng)計(jì)結(jié)果。但本文發(fā)布時(shí)，Sequelize還不支持該特性。
增加總和統(tǒng)計(jì)列：
> 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 連接查詢與分組
為了管理方便，我們會(huì)將不同的信息保存在不同的表中。如，我們會(huì)將訂單信息放在一張表中，而將客戶信息保存在另一張表中。對(duì)于存在關(guān)聯(lián)關(guān)系的兩張表，我們會(huì)使用連接查詢來查找關(guān)聯(lián)數(shù)據(jù)，在進(jìn)行連接查詢時(shí)，同樣可以以使用聚合函數(shù)。
訂單表如下：
> 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 |+---------+-------------+--------+------------+---------------------+
客戶表結(jié)構(gòu)如下：
> 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 |+----+-----------+-----+---------------------+---------------------+
使用連接查詢并分組查詢，統(tǒng)計(jì)每個(gè)客戶的訂單總額。
使用SQL語句查詢?nèi)缦拢?/p>
> 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中進(jìn)行連接查詢時(shí)，首先需要建立模型間的關(guān)聯(lián)關(guān)系：
Order.belongsTo(Customer, {foreignKey: 'customerId'});連接查詢及分組：
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);})總結(jié)
以上就是這篇文章的全部內(nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作能帶來一定的幫助，如果有疑問大家可以留言交流。
新聞熱點(diǎn)
疑難解答
圖片精選
網(wǎng)友關(guān)注
