mysql - ORDER BY 和 LIMIT 在使用 UNION 时不起作用

Question

这是我的查询:

mysql_query("SELECT userid FROM messages WHERE userid<>'$myid' AND friendid='$myid' AND status=1 UNION SELECT friendid FROM messages WHERE userid='$myid' AND friendid<>'$myid' AND status=1");

我想添加分页功能，所以我需要限制发送消息的好友数量。但是每当我添加 LIMIT 它都会显示错误。此外，我想首先显示最新消息 ORDER BY time DESC 但这也不起作用。帮助？

Answer 1

将该查询包含在子查询中，并在外部查询中包含 ORDER BY ... LIMIT...:

SELECT *
FROM
(
    SELECT userid 
    FROM messages 
    WHERE userid   <> '$myid' 
      AND friendid = '$myid' 
      AND status = 1 
    UNION 
    SELECT friendid 
    FROM messages
    WHERE userid = '$myid' 
      AND friendid <> '$myid' 
      AND status = 1
) AS sub
ORDER BY ...
LIMIT ...;