備注:測(cè)試數(shù)據(jù)庫(kù)版本為MySQL 8.0
如需要scott用戶下建表及錄入數(shù)據(jù)語(yǔ)句冯袍，可參考:
scott建表及錄入數(shù)據(jù)sql腳本
一.需求
給表EMP中的工資分等級(jí)冒晰，并允許捆綁，返回下列結(jié)果集:
+-----+---------+
| rnk | sal |
+-----+---------+
| 1 | 800.00 |
| 2 | 950.00 |
| 3 | 1100.00 |
| 4 | 1250.00 |
| 4 | 1250.00 |
| 5 | 1300.00 |
| 6 | 1500.00 |
| 7 | 1600.00 |
| 8 | 2450.00 |
| 9 | 2850.00 |
| 10 | 2975.00 |
| 11 | 3000.00 |
| 11 | 3000.00 |
| 12 | 5000.00 |
+-----+---------+
二.解決方案
窗口函數(shù)會(huì)使等級(jí)查詢簡(jiǎn)單。如果暫不支持窗口函數(shù)，可以使用標(biāo)量子查詢
2.1 子查詢方法
select (select count(distinct b.sal)
from emp b
where b.sal <= a.sal) as rnk,
a.sal
from emp a
測(cè)試記錄
mysql> select (select count( b.sal)
-> from emp b
-> where b.sal <= a.sal) as rnk,
-> a.sal
-> from emp a;
+------+---------+
| rnk | sal |
+------+---------+
| 1 | 800.00 |
| 8 | 1600.00 |
| 5 | 1250.00 |
| 11 | 2975.00 |
| 5 | 1250.00 |
| 10 | 2850.00 |
| 9 | 2450.00 |
| 13 | 3000.00 |
| 14 | 5000.00 |
| 7 | 1500.00 |
| 3 | 1100.00 |
| 2 | 950.00 |
| 13 | 3000.00 |
| 6 | 1300.00 |
+------+---------+
14 rows in set (0.00 sec)
2.2 MySQL 8.0 窗口函數(shù)方法
select dense_rank() over w as 'rnk', sal
from emp
window w as (order by sal)
;
測(cè)試記錄
mysql> select dense_rank() over w as 'rnk', sal
-> from emp
-> window w as (order by sal)
-> ;
+-----+---------+
| rnk | sal |
+-----+---------+
| 1 | 800.00 |
| 2 | 950.00 |
| 3 | 1100.00 |
| 4 | 1250.00 |
| 4 | 1250.00 |
| 5 | 1300.00 |
| 6 | 1500.00 |
| 7 | 1600.00 |
| 8 | 2450.00 |
| 9 | 2850.00 |
| 10 | 2975.00 |
| 11 | 3000.00 |
| 11 | 3000.00 |
| 12 | 5000.00 |
+-----+---------+
14 rows in set (0.00 sec)
