mysql全部加两岁_也谈MySQL中实现ROWNUM

在工作中碰到这样的问题，在生成报表时第一列要输出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了。

参考材料：