mysql 添加行号字段_MySQL-给一列赋予行号

现有一张表： Greatersts

建表结构：

CREATE TABLE `Greatersts` (

`key` varchar(32) DEFAULT NULL,

`x` int(11) DEFAULT NULL,

`y` int(11) DEFAULT NULL,

`z` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

插入数据：

INSERT INTO `Greatersts` VALUES ('A', 1, 2, 3);

INSERT INTO `Greatersts` VALUES ('B', 5, 5, 2);

INSERT INTO `Greatersts` VALUES ('C', 4, 7, 1);

INSERT INTO `Greatersts` VALUES ('D', 3, 3, 6);

实现语句

使用自定义变量

解法一 使用SET定义变量

SET @row_num:=0;

SELECT (@row_num:=@row_num+1) AS row_num, a.*

FROM Greatersts AS a;

解法二 INNER JOIN

SELECT (@row_num:=@row_num+1) AS row_num, a.*

FROM Greatersts AS a

INNER JOIN (SELECT @row_num:=0) b;

解法三

SELECT (@row_num:=@row_num+1) AS row_num, a.*

FROM Greatersts AS a, (SELECT @row_num:=0) b;

不使用自定义变量

解法四 使用子查询

SELECT (SELECT COUNT()+1 FROM Greatersts AS b WHERE b.key < a.key) AS row_num , a.

FROM Greatersts AS a;

查询结果