我有一张这样的 table
+------+----------+------------+
| id | 1_value | 2_value |
+------+----------+------------+
| 3 | foo1 | other |
| 10 | fooX | stuff |
| 13 | fooJ | here |
| 22 | foo7 | and |
| 31 | foou | here |
+------+----------+------------+
我想得到的是行号
我试过做这样的事情
SELECT id, @curRow := @curRow + 1 AS row_number
FROM table
JOIN (SELECT @curRow := 0) r
它确实有效......
+------+--------------+
| id | row_number |
+------+--------------+
| 3 | 1 |
| 10 | 2 |
| 13 | 3 |
| 22 | 4 |
| 31 | 5 |
+------+--------------+
但是如果我尝试选择特定行怎么办?
SELECT id, @curRow := @curRow + 1 AS row_number
FROM srwk_esp_registration
JOIN (SELECT @curRow := 0) r
WHERE ID = 22
在这种情况下,row_number 是 1,但它应该是 4。
我怎样才能做到这一点?
最佳答案
尝试使用子查询:
SELECT *
FROM (
SELECT id,
@curRow := @curRow + 1 AS row_number
FROM srwk_esp_registration
JOIN (
SELECT @curRow := 0
) r
) sub
WHERE sub.ID = 22
关于Mysql获取行号,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/47677063/