MySQL5.7中如何使用開窗函數

2020-12-14 程序猿集錦

關注我，獲的更多分享。

背景準備實驗環境準備建表語句初始化數據分組排序分組統計分組求最大值背景

在SQL中我們經常遇到一種需求：分組排序，分組求和等各種需求。像這樣的需求，如果在Oracle、SQLserver、postgresql等資料庫中很容易實現，一個開窗函數row_nubmer() over(partition by xxx,yyy order by zzz)就可以解決。

但是在MySQL8.0版本之前，是沒有這樣的開窗函數的。好在8.0之後的版本已經內置了開窗函數。不必自己寫實現邏輯了。但是我們目前還有很多人在使用5.7版本。那麼在5.7版本中，如何實現開窗函數的功能呢？

準備實驗環境

準備建表語句

CREATETABLE`emp`(`id`int(11)NOTNULL,`emp_name`varchar(255)DEFAULTNULL,`dept_no`varchar(255)DEFAULTNULL,`emp_salary`int(10)DEFAULTNULL,`emp_hire_date`dateDEFAULTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;

初始化數據

INSERTINTO`mysql_db`.`emp`(`id`,`emp_name`,`dept_no`,`emp_salary`,`emp_hire_date`)VALUES(1,'張三','0001',5000,'2017-01-11');INSERTINTO`mysql_db`.`emp`(`id`,`emp_name`,`dept_no`,`emp_salary`,`emp_hire_date`)VALUES(2,'李四','0002',1000,'2018-10-10');INSERTINTO`mysql_db`.`emp`(`id`,`emp_name`,`dept_no`,`emp_salary`,`emp_hire_date`)VALUES(3,'王五','0003',2000,'2018-12-19');INSERTINTO`mysql_db`.`emp`(`id`,`emp_name`,`dept_no`,`emp_salary`,`emp_hire_date`)VALUES(4,'趙六','0002',4000,'2019-09-11');INSERTINTO`mysql_db`.`emp`(`id`,`emp_name`,`dept_no`,`emp_salary`,`emp_hire_date`)VALUES(5,'王強強','0001',3000,'2019-03-14');INSERTINTO`mysql_db`.`emp`(`id`,`emp_name`,`dept_no`,`emp_salary`,`emp_hire_date`)VALUES(6,'劉陽','0002',6000,'2019-08-08');INSERTINTO`mysql_db`.`emp`(`id`,`emp_name`,`dept_no`,`emp_salary`,`emp_hire_date`)VALUES(7,'周心怡','0003',500,'2015-06-10');INSERTINTO`mysql_db`.`emp`(`id`,`emp_name`,`dept_no`,`emp_salary`,`emp_hire_date`)VALUES(8,'毛志宇','0004',4500,'2016-09-20');INSERTINTO`mysql_db`.`emp`(`id`,`emp_name`,`dept_no`,`emp_salary`,`emp_hire_date`)VALUES(9,'劉德仁','0002',3500,'2016-02-25');INSERTINTO`mysql_db`.`emp`(`id`,`emp_name`,`dept_no`,`emp_salary`,`emp_hire_date`)VALUES(10,'範德武','0001',3000,'2020-02-12');INSERTINTO`mysql_db`.`emp`(`id`,`emp_name`,`dept_no`,`emp_salary`,`emp_hire_date`)VALUES(11,'梅婷婷','0005',8000,'2013-07-07');INSERTINTO`mysql_db`.`emp`(`id`,`emp_name`,`dept_no`,`emp_salary`,`emp_hire_date`)VALUES(12,'鄭冰','0005',1000,'2014-11-17');

最後的環境如下：

分組排序

需求描述：獲取每一個部門薪水最高的員工的信息。

需求分析：

按照部門分組，每一部門的員工放在一組，然後基於這個組中的數據，按照工資降序排列。然後再根據排序後的結果集，獲取排序為1的數據行即為結果。步驟1的SQL如下：定義兩個變量，row_num_t用於存儲每一個組中的排序結果。dept_no_t用於存儲判斷數據行是否是同一組。同事給他們分別初始化數據。然後再查詢的欄位當中，先判斷一下當前是否和上一行的部門編號是同一個部門編號。如果是同一個部門編號，則將排序結果欄位加1；如果不是同個部門編號，表示要切換為新的組了，這個時候，把排序結果欄位重置為1。selectcasewhen@dept_no_t!= x.dept_no then@row_num_t :=1else@row_num_t :=@row_num_t+1endas sort_result, x.id, x.emp_name,-- x.dept_no,@dept_no_t := x.dept_no as dept_no, x.emp_salary, x.emp_hire_datefrom emp as x, (select@dept_no_t :='')as t1,(select@row_num_t :=0)as t2orderby dept_no, emp_salary desc;

步驟1的示例結果如下：

步驟2的SQL語句如下：在步驟1的SQL基礎之上，在外出包裹一層查詢，然後時候用where條件獲取上面內層查詢結果中排序為1的數據行。select*from(selectcasewhen@dept_no_t!= x.dept_no then@row_num_t :=1else@row_num_t :=@row_num_t+1endas sort_result, x.id, x.emp_name,-- x.dept_no,@dept_no_t := x.dept_no as dept_no, x.emp_salary, x.emp_hire_datefrom emp as x, (select@dept_no_t :='')as t1,(select@row_num_t :=0)as t2orderby dept_no, emp_salary desc)as y where y.sort_result =1;

步驟2的示例結果如下：

分組統計

需求描述：累計統計每一個部門下所有員工的工資之和。

分析：按照部門分組，每一部門的員工放在一組，然後基於這個組中的數據，逐行累加該部門下所有員工的工資。

SQL如下：定義一個用於存儲最後每組員工工資之和的變量emp_salary_sum_t，然後再每一行數據是否為同一組數據，如果是同一組數據，則將這行數據的工資，累加到工資之和的變量中；如果不是同一組數據，把當前行的工資賦值給每組工資之和的變量。selectcasewhen@dept_no_t!= x.dept_no then@row_num_t :=1else@row_num_t :=@row_num_t+1endas sort_result,casewhen@dept_no_t!= x.dept_no then@emp_salary_sum_t := x.emp_salary when@dept_no_t= x.dept_no then@emp_salary_sum_t :=@emp_salary_sum_t+ x.emp_salary endas emp_salary_sum, x.id, x.emp_name,-- x.dept_no,@dept_no_t := x.dept_no as dept_no, x.emp_salary, x.emp_hire_datefrom emp as x,(select@dept_no_t :='')as t1,(select@row_num_t :=0)as t2,(select@emp_salary_sum_t :=0)as t3orderby dept_no, emp_salary desc;

最後示例結果如下：

分組求最大值

需求描述：計算每個員工和部門中工資最高員工的工資差。

需求分析：

根據員工的部門分組，然後判斷得到每組數據中，工資最高的員工的工資。把這個作為一個新列查詢出出來。基於步驟1的結果集中的新列，和員工的工資列做減法得到差值。步驟1SQL語句如下：select case when@dept_no_t!= x.dept_no then @emp_salary_max_t := x.emp_salarywhen@dept_no_t= x.dept_noand x.emp_salary >@emp_salary_max_tthen @emp_salary_max_t := x.emp_salaryelse@emp_salary_max_tendas emp_salary_max, x.id, x.emp_name,@dept_no_t := x.dept_no as dept_no, x.emp_salary, x.emp_hire_date from emp as x,(select@dept_no_t :='')as t1, (select@emp_salary_max_t :=0)as t4 orderby dept_no, emp_salary desc

步驟1實驗結果如下：

步驟2SQL語句如下：select y.emp_salary_max, y.emp_salary_max - y.emp_salary as cha, y.id, y.emp_name, y.dept_no, y.emp_salary, y.emp_hire_datefrom(selectcasewhen@dept_no_t!= x.dept_no then@emp_salary_max_t := x.emp_salarywhen@dept_no_t= x.dept_no and x.emp_salary >@emp_salary_max_tthen@emp_salary_max_t := x.emp_salary else@emp_salary_max_tendas emp_salary_max, x.id, x.emp_name,@dept_no_t := x.dept_no as dept_no, x.emp_salary, x.emp_hire_date from emp as x,(select@dept_no_t :='')as t1, (select@emp_salary_max_t :=0)as t4 orderby dept_no, emp_salary desc)as y;

步驟2實驗結果如下：

以上就是MySQL5.7版本中，如何使用開窗函數的示例。希望能這篇文章能夠幫到你，也還原你把文章分享給更多的朋友。如果有什麼問題請留言，我看到後會第一時間恢復你。

相關焦點

  • mysql實現php函數explode功能mysql_explode
    我article表中的記錄如下,因為多個關鍵詞存放在一個欄位上,不利於做排序統計操作,例如我想要統計哪個關鍵詞的數量最多就是個大問題了:id keywords1 九陽神功,萬川歸海,橫掃千軍,乾坤大挪移2 殺破狼,落日十三劍
  • Excel中if函數如何使用
    Excel中如果涉及到邏輯的東西，那麼if函數就不得不提了，這個也是其他邏輯函數的一個基礎。先看一下if函數的語法。#excel看不懂英文沒關係，來解釋下。例如，=IF(C2=」Yes」,1,0) 表示如果C2單元格中是Yes值，那麼則返回 1, 否則返回0。在實際工作中，單個判斷條件使用的情況比較少，更多情況是進行嵌套使用。
  • 主庫n -> 從庫s - MySQL5.7多主一從(多源複製)同步配置 - 計算機...
    多主一從，也稱為多源複製，數據流向：主庫1 -> 從庫s主庫2 -> 從庫s主庫n -> 從庫s應用場景數據匯總，可將多個主資料庫同步匯總到一個從資料庫中，方便數據統計分析。讀寫分離，從庫只用於查詢，提高資料庫整體性能。部署環境註：使用docker部署mysql實例，方便快速搭建演示環境。
  • 在excel表格中如何使用LOG函數
    LOG函數是根據給定底數返回數字的對數的函數，那如何在EXCEL表格中使用該函數呢?下面就為大家詳細介紹一下，不會的朋友可以參考本文，來看看吧! 　　在彈出窗口中選擇數學與三角函數，如圖　　 　　選擇LOG函數，如圖　　 　　在彈出對話框中輸入數值，點確定按鈕
  • MySQL如何計算統計redo log大小
    在MySQL中如何計算、統計重做日誌（redo log)的生成情況呢？ 例如10分鐘內，生成了多少M的redo log呢?30分鐘內又生成了多少M的redo log.....。MySQL沒有像Oracle中那樣的系統視圖統計這些數據，但是我們可以通過一些方法曲線的統計二進位日誌的生成量。
  • 使用NodeJS Lambda函數查詢RDS MySQL資料庫
    在這篇文章中，我將分享我的經驗。第一次嘗試：使用內聯代碼編輯器自從我使用NodeJS以來，第一件事就是找出一個好的MySQL節點包。幸運的是，我找到了這個MySQL庫，並且它也有不錯的文檔，編寫代碼很簡單。
  • MySQL-mysqldump備份資料庫
    mysqldump備份1、備份命令格式：mysqldump -h主機名 -P埠 -u用戶名 -p密碼 --database 資料庫名 > 文件名.sql 例如： mysqldump -h 192.168.1.100 -p 3306 -uroot -ppassword --database
  • 如何在Excel中使用INDEX和MATCH函數
    首先我們來看一下INDEX函數，=INDEX(array, row_num,[column_num])。這裡的array可以是單行，單列或者多行多列的單元格區域，row_num是對應在該列的所要返回的值的參數，column_num是對應在該行的所要返回的值的參數。 2. 如圖，我們通過公式「=INDEX(F2:F17,5)」找到了學號為202005的英語成績。
  • CentOS7.X 掛載磁碟 與Mysql 自動備份
    注意以下教程是在測試伺服器基礎上編寫 如數據盤含有數據切勿使用此教程，如對命令行不熟悉的 不建議搞哈 。本文中的磁碟/dev/sdb為筆者測試伺服器上的命名，在您的伺服器中可能是/dev/xdb、/dev/vdb、/dev/xvdb等等，請根據實際情況進行修改 2、創建掛載目錄 www mkdir -p /www 3、確認是否沒有分區的磁碟 fdisk -l
  • Excel Replace函數與ReplaceB函數的使用方法,含7個替換實例
    以下是 Excel Replace函數與ReplaceB函數的使用方法，總共有7個實例，分別為把單槓替換為雙槓，替換姓名中間一個字，把部分數字替換為星號*，替換某個字後的所有字符，替換一段字符中間指定個字符、替換數字、字母和特殊字符和替換單字節與雙字節，實例操作所用版本均為 Excel 2016。
  • 實例,PHP+MySql 實現簡單的分頁功能
    1、mysql limit 用法SELECT * FROM table limit [offset,] count;參數：（1）開始位置計算方法：(頁數-1) x 每頁顯示個數（2）limit語句：limit 開始位置，每頁顯示個數3、實現代碼上述代碼使用 mysqli函數連接資料庫和查詢數據。
  • Python零基礎入門教程,如何使用函數?
    大綱函數語法格式及調用參數：默認值、元組和字典可變參數的使用全局變量和局部變量作用域，局部變量如何升級為全局變量函數是可重複使用的，實現單一功能的代碼塊。可以把項目中某一功能想像成積木模型，函數是組成模型的大大小小積木塊。
  • 箭頭函數=> 的使用與局限 - ES6中JavaScript新特性之函數
    （4）不可以使用`yield`命令，因此箭頭函數不能用作 Generator 函數。上面四點中，第一點尤其值得注意。`this`對象的指向是可變的，但是在箭頭函數中，它是固定的。`Timer`函數內部設置了兩個定時器，分別使用了箭頭函數和普通函數。
  • Mysql常用關鍵字指令和參數總結
    最近在學習極客時間上的mysql課程，對mysql資料庫有了更多了解，本篇文章是想總結一些mysql的基礎知識。目的是加深自己的記憶，也可以提升對mysql設計原理的了解。mysql表數據文件髒頁：內存數據和磁碟不一致的數據頁刷髒頁：就是把內存中的數據刷到磁碟上MVCC：資料庫的多版本並發控制mysqldump
  • 詳細講解求和函數和平均值函數在Excel 2010中使用方法
    Excel 2010中SUM求和函數的使用1. SUM函數的功能是對Excel 2010中指定範圍內的數值數據求和。使用方法選中要使用函數的單元格，在編輯欄單擊「插入函數」按鈕，在「插入函數」對方話框中的常用函數列表框中選擇SUM確定後，進入「函數參數」編輯框中，輸入相應的參數後，單擊確定按鈕，即可完成。見下圖5.
  • IF函數搭配OR、AND函數使用的基本操作技巧講解 - 李先生的職場Excel
    ---[宋]張元幹《浣溪沙》在Excel中，基礎遇到的是多個函數搭配使用的操作，之前給大家介紹了IF函數的基本使用方法，這次接著給大家分享下IF函數和OR、AND函數搭配使用的基本操作技巧。比如我們看下面的案例：我們想根據以下人員的銷售數據，銷售額＞80萬，完成項目數＞5個的人員，每人獎勵1000元。這個要如何操作呢？
  • MySQL一直自動重啟解決辦法
    If you see no messages after this， something wentterribly wrong...stack_bottom = 7f4846172820 thread_stack 0x80000/usr/local/mysql5.7/bin/mysqld(my_print_stacktrace+0x2c)[0xed481c]/usr/local/mysql5.7/bin
  • 詳解if函數的使用方法以及使用技巧
    這一章，我們來學習下IF函數的使用方法， IF函數也是一個我們工作中經常用到的函數，我個人認為if函數是唯一一個能與vlookup函數並駕齊驅的函數，在日常的工作中它解決大部分我們工作中遇到的邏輯判斷問題，而且if函數還可以嵌套使用，進行多次邏輯判斷，十分的強大，下面就讓我們來學習下它是如何使用的
  • MySQL中show status和show variables的區別
    在MySQL中，我們經常使用如下兩個命令來查看MySQL伺服器的一些參數配置信息。一開始的時候，我是兩個命令都執行一次，哪個命令有結果輸出那麼就證明應該使用哪個命令來查看這個參數的值。但是，今天我仔細查了一下規律：凡是參數的開頭字母是大寫的參數，都需要使用 show status命令來查看該參數的具體值是多少，用另外一個命令查看該參數輸出內為空。
  • Loadrunner測試mysql資料庫
    loadrunner可以利用mysql lib庫，通過引用外部DLL，模擬mysql客戶端連接資料庫進行增刪改查的操作進行測試。下面主要介紹如何利用mysql lib庫連接mysql資料庫進行性能測試。一、準備工作1.