mysql - MySQL: ORDER BY column DESC LIMIT (SELECT COUNT(*) FROM table);

Question

如果这个问题很愚蠢，我很抱歉。代码很容易理解。

最后一行出现语法错误。我怎样才能克服它？

    SELECT id
    FROM m_users
    ORDER BY id DESC
    LIMIT (SELECT COUNT(*) FROM tmp_students);

标签： mysqlsqldatabase

Answer 1

您可以使用窗口函数：

SELECT u.id
FROM (SELECT u.*, ROW_NUMBER() OVER (ORDER BY id DESC) as seqnum
      FROM m_users u
     ) u
WHERE seqnum <= (SELECT COUNT(*) FROM tmp_students)
ORDER BY id DESC