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 ...; 

Answer 2

尝试也许

SELECT DISTINCT userid FROM...