相信我,天才无法与我媲美

mysql结果集加序号问题?

2017-11-13

mysql结果集加序号问题?
如何在mysql查询后的结果集前加序列,类似oracle的rownum?

首先给出解决方案:
–通过一个初始值为0的变量@rownum,依次递增1来实现行号

SELECT
    @rownum:=@rownum+1 AS rownum,name
FROM temp,
    (SELECT @rownum:=0) temp
WHERE @rownum<4;

给出一般性的语句:

SELECT
    @rownum:=@rownum+1 AS rownum,所需字段
FROM 表名,
    (SELECT @rownum:=0) 表名
WHERE @rownum<N(获取多少行);

然后给出测试案例:
第一步,创建测试表;

mysql> CREATE TABLE temp(id INT(4),name VARCHAR(20));
Query OK, 0 rows affected (0.13 sec)

第二步,插入测试数据;

mysql> INSERT INTO temp VAlUES(1,'robin');
Query OK, 1 row affected (0.10 sec)

mysql> INSERT INTO temp VAlUES(2,'wentasy');
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO temp VAlUES(3,'justdb');
Query OK, 1 row affected (0.08 sec)

mysql> INSERT INTO temp VAlUES(4,'wen');
Query OK, 1 row affected (0.08 sec)

mysql> INSERT INTO temp SELECT * FROM temp;
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> INSERT INTO temp SELECT * FROM temp;
Query OK, 8 rows affected (0.02 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> INSERT INTO temp SELECT * FROM temp;
Query OK, 16 rows affected (0.02 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql> INSERT INTO temp SELECT * FROM temp;
Query OK, 32 rows affected (0.03 sec)
Records: 32  Duplicates: 0  Warnings: 0

mysql> INSERT INTO temp SELECT * FROM temp;
Query OK, 64 rows affected (0.04 sec)
Records: 64  Duplicates: 0  Warnings: 0

mysql> INSERT INTO temp SELECT * FROM temp;
Query OK, 128 rows affected (0.03 sec)
Records: 128  Duplicates: 0  Warnings: 0

mysql> INSERT INTO temp SELECT * FROM temp;
Query OK, 256 rows affected (0.04 sec)
Records: 256  Duplicates: 0  Warnings: 0

mysql> INSERT INTO temp SELECT * FROM temp;
Query OK, 512 rows affected (0.05 sec)
Records: 512  Duplicates: 0  Warnings: 0

mysql> INSERT INTO temp SELECT * FROM temp;
Query OK, 1024 rows affected (0.05 sec)
Records: 1024  Duplicates: 0  Warnings: 0

第三步,实现类似Oracle中的rownum效果;
mysql> SELECT
    ->     @rownum:=@rownum+1 AS rownum,name
    -> FROM temp,
    ->     (SELECT @rownum:=0) temp
    -> WHERE @rownum<4;
+--------+---------+
| rownum | name    |
+--------+---------+
|      1 | robin   |
|      2 | wentasy |
|      3 | justdb  |
|      4 | wen     |
+--------+---------+
4 rows in set (0.01 sec)

作者:lrxin | Categories:MySQL | Tags:

发表评论