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