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;

查询结果

参考文献

1.Create a Cumulative Sum Column in MySQL [DB|OL] https://stackoverflow.com/questions/2563918/create-a-cumulative-sum-column-in-mysql