Sequelize中用group by進行分組彙總查詢_node.js-阿里雲

相关文章推荐

不拘小节的酸菜鱼 · python ...· 2 月前 ·

骑白马的包子 · VSCode修改扩展和用户文件夹目录位置(W ...· 6 月前 ·

慷慨的肉夹馍 · 前端在edge中切换ie版本，调试代码_ed ...· 7 月前 ·

内向的脸盆 · Unity基础篇：Unity实现双击事件（奔 ...· 8 月前 ·

低调的机器猫 · 第十章微生物组数据的成分分析 - 简书· 1 年前 ·

**1.1 SQL中的分組查詢

SQL查詢中，通 GROUP BY 語名實現分組查詢。 GROUP BY 子句要和彙總函式配合使用才能完成分組查詢，在 SELECT 查詢的欄位中，如果沒有使用彙總函式就必須出現在 ORDER BY 子句中。分組查詢後，查詢結果為一個或多個列分組後的結果集。

GROUP BY 文法

SELECT 列名, 彙總函式(列名)FROM 表名WHERE 列名 operator valueGROUP BY 列名 [HAVING 條件運算式] [WITH ROLLUP]
在以上語句中：
彙總函式 - 分組查詢通常要與彙總函式一起使用，彙總函式包括：\
     COUNT()-用於統計記錄條數
     SUM()-用於計算欄位的值的總和
     AVG()-用於計算欄位的值的平均值
     MAX-用於尋找查詢欄位的最大值
     MIX-用於尋找查詢欄位的最小值\
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);})
產生的SQL語句如下：
SELECT `name`, sum(`price`) AS `sum` FROM `orders` AS `Orders` GROUP BY name HAVING COUNT('name')>1;
使用彙總參數
除直接使用彙總函式外，也可以在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);})
產生的SQL語句如下：
SELECT `name`, sum(`price`) AS `sum` FROM `orders` AS `Orders` GROUP BY name HAVING COUNT('name')>1;
二、使用樣本
現在訂單表，資料如下：
> 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 簡單使用

使用分組查詢，統計每個客戶的訂單總額。
使用SQL語句，可以像下面這樣查詢：
> select name, SUM(price) from orders GROUP BY name;+-----------+------------+| name  | SUM(price) |+-----------+------------+| 張小三 |  230.00 || 王小五 |  99.00 || 趙小六 |  199.00 |+-----------+------------+
而在Sequelize中可以像下面這樣實現：
Order.findAll({attributes:['sum', [sequelize.fn('SUM', sequelize.col('name')), 'sum']], group:'name', raw:true}).then(function(result){ console.log(result);})
2.2 使用HAVING子句\
統計訂單數量大於1的使用者的訂單總金額。
使用SQL語句，可以像下面這樣實現：
> 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+新增的特性，用於匯總統計結果。但本文發布時，Sequelize還不支援該特性。
增加總和統計列：
> 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 |+----+-----------+-----+---------------------+---------------------+
使用串連查詢並分組查詢，統計每個客戶的訂單總額。
使用SQL語句查詢如下：
> 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中進行串連查詢時，首先需要建立模型間的關聯關係：
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);})
以上就是這篇文章的全部內容了，希望本文的內容對大家的學習或者工作能帶來一定的協助，如果有疑問大家可以留言交流。
相關文章： IIS 7.0探索用於 Windows Vista 的 Web 服務器和更多內容
  分类：




    
 代码人生
 标签：
 数据库
 
  
      
    AlibabaCloud