也谈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了。

参考材料:

4条回复

  1. Roc说到:

    又写技术文章...

    • 轶侠说到:

      沦落到只能写技术笔记的地步,充分说明了我的生活多么的缺乏激情

  2. Leo说到:

    为什么不用limit关键字?

    • 轶侠说到:

      其实我主要就是为了要把两种不同的top 10在同一个表输出结果,左右并排。因此才找此下策。基本上是order by col1 limit 10 join order by col2 limit 10

发表评论

您的电子邮箱地址永远不会被公开。 标记为 * 的区域必须填写