這幾天開發提交了幾個排名的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字段，甚至可以根據自己排名的方式（我這裡是正序，你可以倒序），只是將"<"改成">"就行啦。
