[[421055]]

本文轉載自微信公眾號「SQL數據庫開發」，作者丶平凡世界。轉載本文請聯系SQL數據庫開發公眾號。

最近幫業務部門梳理業務報表，其中有個需求是就算某指標等待時間最長的前百分之十，其實就是對等待時長進行倒序排序后，取結果集的前百分之十。

這個需求在SQL Server和Oracle上都很容易實現，甚至是在MySQL 8.0也很容易實現，只是恰好我們業務數據庫是MySQL 5.7

先給大家介紹下不同數據庫平臺的實現方法。

SQL Server實現方法

SQL Server上有個TOP Percent的方法可以直接取結果的前(或后)百分之N

例如有如下一張City表

我們取前10%的數據記錄可以這樣寫：

SELECT  
TOP 10 PERCENT   
*  
FROM City  
ORDER BY ID DESC 

結果如下：

Oracle實現方法

Oracle有個ROWNUM偽列可以用來幫助我們計算前百分之N。

ROWNUM偽列的特點：

• ROWNUM是按照記錄插入時的順序排序的
• ROWNUM并不實際存在，是對篩選后的結果集的一個排序，如果不存在結果集就不會有ROWNUM
• ROWNUM不能用基表名作為前綴
• 在使用ROWNUM進行查詢時，請注意：

1)若使用大于號(>),則只能使用(>0),其他均不可以

2)若使用小于號(<)，同一般情況

3)若使用等于號(=)，則只能使用等于1(=1)

我們可以先計算出整個表的記錄行數量

SELECT COUNT(*) CNT FROM City 

然后根據count聚合查詢總條數乘以百分比，來確定要查詢的條數

SELECT 0.1*COUNT(*) CNT FROM City 

最后取出偽列小于共有數據的百分比的數據

SELECT * FROM CITY  
WHERE ID IN 
( 
SELECT ID FROM  
( 
SELECT ID FROM CITY ORDER BY ID DESC 
) 
WHERE ROWNUM < (SELECT COUNT(*)*0.1 FROM CITY) 
) 

注意：Oracle不支持子查詢內ORDER BY，需要在外面再嵌套一層。

MySQL 8.0的實現方法

MySQL 8.0的實現方法主要是借助窗口函數ROW_NUMBER() OVER()。

其實就是給排好序的集合添加一個自增長列，與Oracle的ROWNUM有點類似

SELECT * FROM 
( 
SELECT *, 
ROW_NUMBER() OVER(ORDER BY ID DESC) rn  
FROM  City  
ORDER BY ID DESC 
) a 
WHERE a.rn<=(SELECT 0.1*COUNT(*) FROM City) 

MySQL 5.X的實現方法

我們知道MySQL 5.X是沒有開窗函數ROW_NUMBER() OVER()的，那該如何實現呢?

這里我們需要借助變量來實現，其實思路還是創建一個自增長列，只是方法不同。

SELECT  
  A.*, 
  @row_num:=@row_num+1 AS ROW_NUM  
FROM  
  City A , (SELECT @row_num:=0) B  
ORDER BY ID DESC 

這樣我們就可以得到一張有自增長列的結果集了，接下來還是按照上面類似的方法，取前10%即可。

SELECT * FROM 
( 
SELECT  
  A.*, 
  @row_num:=@row_num+1 AS ROW_NUM  
FROM  
  City A , (SELECT @row_num:=0) B  
ORDER BY ID DESC 
) C 
WHERE C.ROW_NUM<=(@row_num*0.1) 

其實MySQL 5.X也挺簡單的，只是當時不怎么想用變量，想看看有沒有其他辦法，最后發現還是得用變量圖片

以上就是不同平臺的數據庫求前百分之N的方法了，代碼可以驗證一下收藏起來留著下次直接套用。

總結

其中有涉及一些知識點，需要小伙伴們自己去進一步了解： 

• SQL Server的TOP PERCENT
• Oracle的ROWNUM，子查詢排序
• ROW_NUMBER() OVER()
• MySQL的變量