mysql上排名sql的寫法,類似oracle的rank和dense
這幾天開發提交了幾個排名的sql,oracle環境下這類問題就很好解決了,row_number(),rank()或者dense()函式就能搞定,但mysql環境下沒有這類函式,那就自己搞:
測試如下:
mysql> select * from animals_inno;
+--------+----+------------+---------------------+----------+
| grp | id | name | created | modified |
+--------+----+------------+---------------------+----------+
| mammal | 1 | dog | 0000-00-00 00:00:00 | NULL |
| mammal | 2 | cat | 0000-00-00 00:00:00 | NULL |
| bird | 3 | penguin | 0000-00-00 00:00:00 | NULL |
| fish | 4 | lax | 0000-00-00 00:00:00 | NULL |
| mammal | 5 | whale | 0000-00-00 00:00:00 | NULL |
| bird | 6 | ?????????? | 2011-04-13 14:52:48 | NULL |
| bird | 7 | ostrich | 0000-00-00 00:00:00 | NULL |
| fish | 8 | | 0000-00-00 00:00:00 | NULL |
| fish | 9 | NULL | 0000-00-00 00:00:00 | NULL |
+--------+----+------------+---------------------+----------+
9 rows in set (0.00 sec)
我想要按照grp進行排序,grp相同的情況下。我要佔位處理:
SELECT grp,
name,
id,
(SELECT COUNT(*) FROM animals_inno where grp < a.grp) + 1 place
FROM animals_inno a
ORDER BY place;
+--------+------------+----+-------+
| grp | name | id | place |
+--------+------------+----+-------+
| bird | penguin | 3 | 1 |
| bird | ?????????? | 6 | 1 |
| bird | ostrich | 7 | 1 |
| fish | lax | 4 | 4 |
| fish | | 8 | 4 |
| fish | NULL | 9 | 4 |
| mammal | dog | 1 | 7 |
| mammal | cat | 2 | 7 |
| mammal | whale | 5 | 7 |
+--------+------------+----+-------+
9 rows in set (0.00 sec)
如果grp相同時我不需要佔位,則可以:
select grp,
name,
id,
(select count(distinct grp) from animals_inno where grp < a.grp) + 1 place
from animals_inno a
order by place;
+--------+------------+----+-------+
| grp | name | id | place |
+--------+------------+----+-------+
| bird | penguin | 3 | 1 |
| bird | ?????????? | 6 | 1 |
| bird | ostrich | 7 | 1 |
| fish | lax | 4 | 2 |
| fish | | 8 | 2 |
| fish | NULL | 9 | 2 |
| mammal | dog | 1 | 3 |
| mammal | cat | 2 | 3 |
| mammal | whale | 5 | 3 |
+--------+------------+----+-------+
9 rows in set (0.00 sec)
更多情況下我需要按照grp分組,然後按照id排序後給出每行的排名,
同樣,當grp相同需要佔位時,可以:
SELECT grp,
name,
id,
(SELECT COUNT(*) FROM animals_inno where grp =a.grp and id< a.id) + 1 place
FROM animals_inno a
ORDER BY grp,place;
+--------+------------+----+-------+
| grp | name | id | place |
+--------+------------+----+-------+
| fish | lax | 4 | 1 |
| fish | | 8 | 2 |
| fish | NULL | 9 | 3 |
| mammal | dog | 1 | 1 |
| mammal | cat | 2 | 2 |
| mammal | whale | 5 | 3 |
| bird | penguin | 3 | 1 |
| bird | ?????????? | 6 | 2 |
| bird | ostrich | 7 | 3 |
+--------+------------+----+-------+
9 rows in set (0.00 sec)
當grp相同不需要佔位時,可以:
SELECT grp,
name,
id,
(SELECT COUNT(distinct id) FROM animals_inno where grp =a.grp and id< a.id) + 1 place
FROM animals_inno a
ORDER BY grp,place;
+--------+------------+----+-------+
| grp | name | id | place |
+--------+------------+----+-------+
| fish | lax | 4 | 1 |
| fish | | 8 | 2 |
| fish | NULL | 9 | 3 |
| mammal | dog | 1 | 1 |
| mammal | cat | 2 | 2 |
| mammal | whale | 5 | 3 |
| bird | penguin | 3 | 1 |
| bird | ?????????? | 6 | 2 |
| bird | ostrich | 7 | 3 |
+--------+------------+----+-------+
9 rows in set (0.00 sec)
當然,你可以根據你的需求替換grp和id欄位,甚至可以根據自己排名的方式(我這裡是正序,你可以倒序),只是將""就行啦。
出處:http://mxohy.blog.sohu.com/172181390.html?qq-pf-to=pcqq.c2c
測試如下:
mysql> select * from animals_inno;
+--------+----+------------+---------------------+----------+
| grp | id | name | created | modified |
+--------+----+------------+---------------------+----------+
| mammal | 1 | dog | 0000-00-00 00:00:00 | NULL |
| mammal | 2 | cat | 0000-00-00 00:00:00 | NULL |
| bird | 3 | penguin | 0000-00-00 00:00:00 | NULL |
| fish | 4 | lax | 0000-00-00 00:00:00 | NULL |
| mammal | 5 | whale | 0000-00-00 00:00:00 | NULL |
| bird | 6 | ?????????? | 2011-04-13 14:52:48 | NULL |
| bird | 7 | ostrich | 0000-00-00 00:00:00 | NULL |
| fish | 8 | | 0000-00-00 00:00:00 | NULL |
| fish | 9 | NULL | 0000-00-00 00:00:00 | NULL |
+--------+----+------------+---------------------+----------+
9 rows in set (0.00 sec)
我想要按照grp進行排序,grp相同的情況下。我要佔位處理:
SELECT grp,
name,
id,
(SELECT COUNT(*) FROM animals_inno where grp < a.grp) + 1 place
FROM animals_inno a
ORDER BY place;
+--------+------------+----+-------+
| grp | name | id | place |
+--------+------------+----+-------+
| bird | penguin | 3 | 1 |
| bird | ?????????? | 6 | 1 |
| bird | ostrich | 7 | 1 |
| fish | lax | 4 | 4 |
| fish | | 8 | 4 |
| fish | NULL | 9 | 4 |
| mammal | dog | 1 | 7 |
| mammal | cat | 2 | 7 |
| mammal | whale | 5 | 7 |
+--------+------------+----+-------+
9 rows in set (0.00 sec)
如果grp相同時我不需要佔位,則可以:
select grp,
name,
id,
(select count(distinct grp) from animals_inno where grp < a.grp) + 1 place
from animals_inno a
order by place;
+--------+------------+----+-------+
| grp | name | id | place |
+--------+------------+----+-------+
| bird | penguin | 3 | 1 |
| bird | ?????????? | 6 | 1 |
| bird | ostrich | 7 | 1 |
| fish | lax | 4 | 2 |
| fish | | 8 | 2 |
| fish | NULL | 9 | 2 |
| mammal | dog | 1 | 3 |
| mammal | cat | 2 | 3 |
| mammal | whale | 5 | 3 |
+--------+------------+----+-------+
9 rows in set (0.00 sec)
更多情況下我需要按照grp分組,然後按照id排序後給出每行的排名,
同樣,當grp相同需要佔位時,可以:
SELECT grp,
name,
id,
(SELECT COUNT(*) FROM animals_inno where grp =a.grp and id< a.id) + 1 place
FROM animals_inno a
ORDER BY grp,place;
+--------+------------+----+-------+
| grp | name | id | place |
+--------+------------+----+-------+
| fish | lax | 4 | 1 |
| fish | | 8 | 2 |
| fish | NULL | 9 | 3 |
| mammal | dog | 1 | 1 |
| mammal | cat | 2 | 2 |
| mammal | whale | 5 | 3 |
| bird | penguin | 3 | 1 |
| bird | ?????????? | 6 | 2 |
| bird | ostrich | 7 | 3 |
+--------+------------+----+-------+
9 rows in set (0.00 sec)
當grp相同不需要佔位時,可以:
SELECT grp,
name,
id,
(SELECT COUNT(distinct id) FROM animals_inno where grp =a.grp and id< a.id) + 1 place
FROM animals_inno a
ORDER BY grp,place;
+--------+------------+----+-------+
| grp | name | id | place |
+--------+------------+----+-------+
| fish | lax | 4 | 1 |
| fish | | 8 | 2 |
| fish | NULL | 9 | 3 |
| mammal | dog | 1 | 1 |
| mammal | cat | 2 | 2 |
| mammal | whale | 5 | 3 |
| bird | penguin | 3 | 1 |
| bird | ?????????? | 6 | 2 |
| bird | ostrich | 7 | 3 |
+--------+------------+----+-------+
9 rows in set (0.00 sec)
當然,你可以根據你的需求替換grp和id欄位,甚至可以根據自己排名的方式(我這裡是正序,你可以倒序),只是將""就行啦。
出處:http://mxohy.blog.sohu.com/172181390.html?qq-pf-to=pcqq.c2c
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29119536/viewspace-1674867/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- oracle中聚合函式RANK和dense_rank的使用(轉)Oracle函式
- 有關oracle中聚合函式rank和dense_rank的使用Oracle函式
- 分析函式DENSE_RANK 和 RANK函式
- SQL語句中的rank () over , row_number() over ,rank_dense ()SQL
- 【SQL 學習】分析函式之RANK() DENSE_RANK ()SQL函式
- Oracle:Rank,Dense_Rank,Row_Number比較Oracle
- rank() 與dense_rank()分析
- oracle分析函式Rank, Dense_rank, row_numberOracle函式
- SQL SERVER 排序函式ROW_NUMBER、RANK、DENSE_RANK、NTILESQLServer排序函式
- oracle 分析函式之(rank()/dense_rank()/row_number())Oracle函式
- Spark2 Dataset分析函式--排名函式row_number,rank,dense_rank,percent_rankSpark函式
- Oracle 中分析函式用法之--rank(),dense_rank(),partition,over()Oracle函式
- Hive中row_number()、dense_rank()、rank()的區別Hive
- row_number() over,rank() over,dense_rank() over的區別
- 分析函式rank,dense_rank,row_number使用和區別 .函式
- mysql自動排序函式dense_rank() over()、rank() over()、row_num() over()用法和區別MySql排序函式
- rank,dense_rank,row_number 分析函式函式
- 分析函式中rank(),row_number(),dense_rank()的區別函式
- ElasticSearch類似Mysql的not in 和 in 查詢ElasticsearchMySql
- MySQL的字首索引及Oracle的類似實現MySql索引Oracle
- Oracle vs PostgreSQL Develop(14) - 分析函式KEEP DENSE_RANKOracleSQLdev函式
- 分析函式——排序排列(rank、dense_rank、row_number)函式排序
- MySQL 06 mysql 如何實現類似 oracle 的 merge intoMySqlOracle
- mysql 定時指令碼(event),類似oracle的jobMySql指令碼Oracle
- Mysql 分組排序的sql寫法MySql排序
- 分析函式——keep(dense_rank first/last)函式AST
- Oracle SQL寫法OracleSQL
- mysql類似merge的操作MySql
- [原創] 小議rank(),dense_rank(),row_number()使用與區別
- MySQL與SQL的觸發器的不同寫法MySql觸發器
- 轉:類似SQL中的split函式SQL函式
- 【Analytic】分析函式之DENSE_RANK函式函式
- lead lag rank()over()dense_rank()row_number()over()rollupcube幾大分析函式函式
- SQL Server中類似Oracle中before觸發器SQLServerOracle觸發器
- oracle,db2,mysql類比之一常用類似知識點OracleDB2MySql
- SQL常用的特殊寫法SQL
- mysql 效果類似split函式MySql函式
- MySql/Oracle和SQL Server的分頁查MySqlOracleServer