php - SQL(mysql)对表进行排序并获取行及其在排序表中的位置？

Question

基本上我有这张表:

表格QID、UID、系统、添加(unix 时间戳)、错误

我想做的是通过 UID 选择一行，但在此之前按“添加”对表进行排序，使其按时间戳 DESC 顺序排列，然后获取单行及其在排序表中的位置。

我目前有这个可以对行进行排名，但是我如何从中获取单行:

SET @rank=0;
SELECT @rank := @rank +1 AS rank, UID
FROM table
GROUP BY UID
ORDER BY added DESC

Answer 1

您可以执行以下操作:

SELECT COUNT(*)+1 AS rank FROM table
WHERE added < (SELECT added FROM table WHERE UID=123)

或者也许是这样:

SELECT * FROM (
    SELECT @rank := @rank+1 AS rank, UID
    FROM table
    GROUP BY UID
    ORDER BY added ASC
) tmp WHERE UID=123