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

来源：互联网发布：中教数据库怎么样编辑：程序博客网时间：2024/06/15 02:40

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

转载from:http://mxohy.blog.sohu.com/172181390.html

____________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________

表特征：

mysql> select * from test;

+------+------+

| a | b |

+------+------+

| 1 | 20 |

| 1 | 21 |

| 1 | 24 |

| 2 | 20 |

| 2 | 32 |

| 2 | 14 |

+------+------+

6 rows in set (0.00 sec)

现在，我们以a分组，查询b列最大的2个值。这条sql要怎么写了？

2.SQL实现

selecta,b,rownum,rank from

(select ff.a,ff.b,@rownum:=@rownum+1rownum,if(@pa=ff.a,@rank:=@rank+1,@rank:=1) as rank,@pa:=ff.a

FROM

(select a,b from sam group by a,b orderby a asc,b desc) ff,(select @rank:=0,@rownum:=0,@pa=null) tt) result

having rank <=2;

3.结果：

+------+------+--------+------+

| a | b | rownum | rank |

+------+------+--------+------+

| 1 | 25 | 1 | 1 |

| 1 | 20 | 2 | 2 |

| 2 | 45 | 5 | 1 |

| 2 | 33 | 6 | 2 |

+------+------+--------+------+

4 rows in set(0.00 sec)

0 0