我有这样一个表格:
在这种情况下,行号是1,但它应该是4。
我该怎么做才能实现这个目标?
+------+----------+------------+
| 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
在这种情况下,行号是1,但它应该是4。
我该怎么做才能实现这个目标?