mysql 记录表列_使用MySQL，如何生成包含表中记录索引的列？

小编典典

您可能需要尝试以下方法：

SELECT l.position,

l.username,

l.score,

@curRow := @curRow + 1 AS row_number

FROM league_girl l

JOIN (SELECT @curRow := 0) r;

该JOIN (SELECT @curRow := 0)部分允许变量初始化，而无需单独的SET命令。

测试用例：

CREATE TABLE league_girl (position int, username varchar(10), score int);

INSERT INTO league_girl VALUES (1, 'a', 10);

INSERT INTO league_girl VALUES (2, 'b', 25);

INSERT INTO league_girl VALUES (3, 'c', 75);

INSERT INTO league_girl VALUES (4, 'd', 25);

INSERT INTO league_girl VALUES (5, 'e', 55);

INSERT INTO league_girl VALUES (6, 'f', 80);

INSERT INTO league_girl VALUES (7, 'g', 15);

测试查询：

SELECT l.position,

l.username,

l.score,

@curRow := @curRow + 1 AS row_number

FROM league_girl l

JOIN (SELECT @curRow := 0) r

WHERE l.score > 50;

结果：

+----------+----------+-------+------------+

| position | username | score | row_number |

+----------+----------+-------+------------+

| 3 | c | 75 | 1 |

| 5 | e | 55 | 2 |

| 6 | f | 80 | 3 |

+----------+----------+-------+------------+

3 rows in set (0.00 sec)

2020-05-17