SQL：如何显示行号

柔情只为你懂 2022-03-31 13:48 439阅读 0赞

*  方法一：\[不推荐\]

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
    +------+