在将我的问题标记为重复之前,请注意我的情况非常不同。
SELECT
c.id
c.date_added AS date_added,
@INDEX := @INDEX + 1 AS row_index
FROM
cases c
JOIN (SELECT @INDEX := 0) indexer
LEFT JOIN users u ON c.owner_id = u.id
INNER JOIN case_profiles cases_id ON c.id = cases_id.cases_id
WHERE
cases_id.`name` LIKE '%case%'
ORDER BY
c.date_added DESC
它向后生成我的row_index 3,2,1 而不是预期的 1,2,3,仅当我在 WHERE 中添加 JOIN 表列中的任何条件(如 cases_id.{COLUMN}(时,才会发生这种情况。
请帮我克服这个问题提前谢谢你!
由于排序,您的查询就是这样发生的;数据在排序之前先联接。试试这个:
SELECT A.*, @INDEX := @INDEX + 1 AS row_index
FROM
(SELECT
c.id
c.date_added AS date_added
FROM
cases c
LEFT JOIN users u ON c.owner_id = u.id
INNER JOIN case_profiles cases_id ON c.id = cases_id.cases_id
WHERE
cases_id.`name` LIKE '%case%'
ORDER BY
c.date_added DESC) A JOIN (SELECT @INDEX := 0) indexer;
请参阅 MySQL row_number,这就是你模拟它的方式