SQL：如何显示行号

• 方法一：[不推荐]

mysql>  use employees ;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> set @rn:=0;  -- 产生 SESSION(会话)级别的变量
Query OK, 0 rows affected (0.00 sec)

mysql> select @rn:=@rn+1 as rownumber, emp_no, gender from employees limit 10;  -- ：= 是赋值的意思
+-----------+--------+--------+
| rownumber | emp_no | gender |
+-----------+--------+--------+
|         1 |  10001 | M      |
|         2 |  10002 | F      |
|         3 |  10003 | M      |
|         4 |  10004 | M      |
|         5 |  10005 | M      |
|         6 |  10006 | F      |
|         7 |  10007 | F      |
|         8 |  10008 | M      |
|         9 |  10009 | F      |
|        10 |  10010 | F      |
+-----------+--------+--------+
10 rows in set, 1 warning (0.03 sec)

mysql> select @rn:=@rn+1 as rownumber, emp_no, gender from employees limit 10;  -- ：= 是赋值的意思
+-----------+--------+--------+
| rownumber | emp_no | gender |
+-----------+--------+--------+
|        11 |  10001 | M      |
|        12 |  10002 | F      |
|        13 |  10003 | M      |
|        14 |  10004 | M      |
|        15 |  10005 | M      |
|        16 |  10006 | F      |
|        17 |  10007 | F      |
|        18 |  10008 | M      |
|        19 |  10009 | F      |
|        20 |  10010 | F      |
+-----------+--------+--------+

• 方法二

mysql>  select @rn1:=@rn1+1 as rownumber, emp_no, gender from employees, (select @rn1:=0) as a limit 10;
+-----------+--------+--------+
| rownumber | emp_no | gender |
+-----------+--------+--------+
|         1 |  10001 | M      |
|         2 |  10002 | F      |
|         3 |  10003 | M      |
|         4 |  10004 | M      |
|         5 |  10005 | M      |
|         6 |  10006 | F      |
|         7 |  10007 | F      |
|         8 |  10008 | M      |
|         9 |  10009 | F      |
|        10 |  10010 | F      |
+-----------+--------+--------+
10 rows in set, 2 warnings (0.00 sec)

mysql>  select @rn1:=@rn1+1 as rownumber, emp_no, gender from employees, (select @rn1:=0) as a limit 10;
+-----------+--------+--------+
| rownumber | emp_no | gender |
+-----------+--------+--------+
|         1 |  10001 | M      |
|         2 |  10002 | F      |
|         3 |  10003 | M      |
|         4 |  10004 | M      |
|         5 |  10005 | M      |
|         6 |  10006 | F      |
|         7 |  10007 | F      |
|         8 |  10008 | M      |
|         9 |  10009 | F      |
|        10 |  10010 | F      |
+-----------+--------+--------+

mysql> show warnings;
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                               |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | Setting user variables within expressions is deprecated and will be removed in a future release. Please set variables in separate statements instead. |
| Warning | 1287 | Setting user variables within expressions is deprecated and will be removed in a future release. Please set variables in separate statements instead. |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------+

备注

• “:=” 和 "="的区别

mysql> set @a:=1;  -- 赋值为1
Query OK, 0 rows affected (0.00 sec)

mysql> select @a;
+------+
| @a   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> set @a:=10;  -- 赋值为10
Query OK, 0 rows affected (0.00 sec)

mysql> select @a;
+------+
| @a   |
+------+
|   10 |
+------+
1 row in set (0.00 sec)

mysql> select @a=9;  -- 进行比较
+------+
| @a=9 |
+------+
|    0 |  -- 返回为False
+------+