也谈MySQL中实现ROWNUM

来源  http://e-xia.com/2009/06/rownum-in-mysql/

 

在工作中碰到这样的问题，在生成报表时第一列要输出top 1, top 2, ... , top 10。而mysql并不自带这样的功能。假设我们有这样的一个表：

mysql> create table tbl (
    ->  id      int primary key,
    ->  col     int
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql> insert into tbl values
    -> (1,26),
    -> (2,46),
    -> (3,35),
    -> (4,68),
    -> (5,93),
    -> (6,92);
Query OK, 6 rows affected (0.05 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from tbl order by col;
+----+------+
| id | col  |
+----+------+
|  1 |   26 |
|  3 |   35 |
|  2 |   46 |
|  4 |   68 |
|  6 |   92 |
|  5 |   93 |
+----+------+
6 rows in set (0.00 sec)

中规中矩的做法是：

SET @x=0;
SELECT @x:=@x AS rownum,id,col
FROM tbl
ORDER BY col;

但是这样就变成了两个query，在java里面用executeQuery会有问题。

当时自己想到的是这样：

SELECT @x := IFNULL(@x,0) + 1 AS rownum,id,col
FROM tbl
ORDER BY col;

但是第一次运行的时候rownum都是1，第二次运行rownum变成2-11，第三次为12-21。

后来又看到一些很悬，而且看上去很没有效率的方法，例如：

使用联接查询（笛卡尔积）

SELECT a.id, a.col, COUNT(*) AS rownum
FROM tbl a,tbl b
WHERE a.col>=b.col
GROUP BY a.id,a.col;

子查询

SELECT a.*,
(SELECT count(*) FROM tbl WHERE col<=a.col) AS rownum
FROM tbl a;

这些都不是我要的！！

最后我找到了第一种方法的改良思路，做了一些改动，虽然还是绕了一个小弯，但是已经很好用了：

SELECT @rownum:=@rownum+1 rownum, id, col 
FROM 
(SELECT @rownum:=0, * 
FROM tbl 
ORDER BY col DESC
) t;

更进一步的应用是，当有两个table要并排放在一起，例如一个order by id，另一个order by col，原先的做法是写两个query，然后在页面里并排放，不过有了rownum以后就可以用join直接输出完全符合要求的table了。

参考材料：

• MySQL中的ROWNUM的实现
• How to number rows in MySQL

看看还有啥

• 再写“最近评论”widget (6)
• Windows下安装mysqldb (0)