mysql上排名sql的寫法,類似oracle的rank和dense

dawn009發表於2015-05-28
這幾天開發提交了幾個排名的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

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29119536/viewspace-1674867/,如需轉載,請註明出處,否則將追究法律責任。

相關文章