学步园

来源  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)