最好在程序中处理,循环输出的时候顺便加上行号就行,如果要在SQL中做也可以,实现成本要高些,比如经常碰到的按成绩排名的问题,就可以这样做:
SELECT (
SELECT count(*)+1 as 行号
FROM 成绩表 as a where a.实际成绩>b.实际成绩) AS bb, b.实际成绩
FROM 成绩表 as b order by b.实际成绩 desc
SELECT (
SELECT count(*)+1 as 行号
FROM 成绩表 as a where a.实际成绩>b.实际成绩) AS bb, b.实际成绩
FROM 成绩表 as b order by b.实际成绩 desc
+------+
| s1 |
+------+
| 6 |
| 6 |
| 0 |
| 19 |
| 19 |
| 1 |
| 2 |
| 3 |
| 4 |
| 0 |
| 1 |
| 2 |
| 4 |
+------+
13 rows in set (0.44 sec)mysql> set @rownum=0;
Query OK, 0 rows affected (0.00 sec)mysql> select @rownum:=@rownum+1 as rownum,s1 from t;
+--------+------+
| rownum | s1 |
+--------+------+
| 1 | 6 |
| 2 | 6 |
| 3 | 0 |
| 4 | 19 |
| 5 | 19 |
| 6 | 1 |
| 7 | 2 |
| 8 | 3 |
| 9 | 4 |
| 10 | 0 |
| 11 | 1 |
| 12 | 2 |
| 13 | 4 |
+--------+------+
13 rows in set (0.04 sec)
RETURNS INT
BEGIN
SET @rno = @rno + 1;
RETURN @rno;
END;通过rno()方法的SELECT我们获得了行数。下面是调用程序的结果:mysql> SET @rno = 0;//
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT rno(),s1,s2 FROM t;//
+-------+------+------+
| rno() | s1 | s2 |
+-------+------+------+
| 1 | 1 | a |
| 2 | 2 | b |
| 3 | 3 | c |
| 4 | 4 | d |
| 5 | 5 | e |
+-------+------+------+
5 rows in set (0.00 sec)在SELECT中将@rno置零的技巧是使用了WHERE的求值功能,而这个特性在今后的MySQL中可能丢失。CREATE FUNCTION rno_reset ()
RETURNS INTEGER
BEGIN
SET @rno = 0;
RETURN 1;
END;
SELECT rno(),s1,s2 FROM t WHERE rno_reset()=1;//
实例如下:
mysql> use db5
Database changed
mysql> delimiter //
mysql> CREATE FUNCTION rno ()
-> RETURNS INT
-> BEGIN
-> SET @rno = @rno + 1;
-> RETURN @rno;
-> END;//
Query OK, 0 rows affected (0.42 sec)mysql> set @rno = 0;
-> //
Query OK, 0 rows affected (0.00 sec)mysql> select rno(),s1 from t;//
+-------+------+
| rno() | s1 |
+-------+------+
| 1 | 6 |
| 2 | 6 |
| 3 | 0 |
| 4 | 19 |
| 5 | 19 |
| 6 | 1 |
| 7 | 2 |
| 8 | 3 |
| 9 | 4 |
| 10 | 0 |
| 11 | 1 |
| 12 | 2 |
| 13 | 4 |
+-------+------+
13 rows in set (0.06 sec)mysql> CREATE FUNCTION rno_reset ()
-> RETURNS INTEGER
-> BEGIN
-> SET @rno = 0;
-> RETURN 1;
-> END;//
Query OK, 0 rows affected (0.01 sec)mysql> select rno(),s1 from t where rno_reset()=1;//
+-------+------+
| rno() | s1 |
+-------+------+
| 1 | 6 |
| 2 | 6 |
| 3 | 0 |
| 4 | 19 |
| 5 | 19 |
| 6 | 1 |
| 7 | 2 |
| 8 | 3 |
| 9 | 4 |
| 10 | 0 |
| 11 | 1 |
| 12 | 2 |
| 13 | 4 |
+-------+------+
13 rows in set (0.13 sec)