mysql上排名sql的写法，类似oracle的rank和dense

来源：互联网发布：php魔术方法编辑：程序博客网时间：2024/06/02 04:15

转：http://mxohy.blog.sohu.com/172181390.html?qq-pf-to=pcqq.c2c

这几天开发提交了几个排名的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字段，甚至可以根据自己排名的方式（我这里是正序，你可以倒序），只是将"<"改成">"就行啦。

0 0