mysql - 按时间戳排序所有联合结果

Question

查询是

SELECT L.timestamp AS timestamp . . . 
FROM like AS L 
INNER JOIN users AS U 
ON U.id = L.user_id 
INNER JOIN posts AS P 
ON P.user_id = :userid 
WHERE L.post_id = P.id 
  UNION 
SELECT P.timestamp . . . 
FROM post AS P 
INNER JOIN users AS U 
ON U.id = P.user_id 
WHERE FIND_IN_SET(:userid , P.users) 
  UNION 
SELECT C.timestamp AS timestamp . . . 
FROM comment AS C 
INNER JOIN posts AS P 
ON P.user_id = :userid 
INNER JOIN users AS U 
ON U.id = C.user_id 
WHERE C.post_id = P.id 
ORDER BY timestamp DESC

我尝试在末尾添加 ORDER BY timestamp DESC 但它仍然没有首先显示最新的。它显示了一个来自; 10 天前、9 天前、1 个月前和 4 天前，依次为。

Answer 1

您需要使用查询作为子查询对整个结果执行ORDER BY:

SELECT * FROM (
    SELECT L.timestamp AS timestamp FROM like AS L 
        INNER JOIN users AS U ON U.id = L.user_id 
        INNER JOIN posts AS P ON P.user_id = :userid 
    WHERE L.post_id = P.id 
    UNION
    SELECT P.timestamp . . . FROM post AS P 
        INNER JOIN users AS U ON U.id = P.user_id 
    WHERE FIND_IN_SET(:userid , P.users) 
    UNION 
    SELECT C.timestamp AS timestamp . . . 
    FROM comment AS C 
        INNER JOIN posts AS P ON P.user_id = :userid 
        INNER JOIN users AS U ON U.id = C.user_id 
    WHERE C.post_id = P.id 
)x ORDER BY x.timestamp DESC