php - MYSQL 联合排序

Question

我现在有这个

SELECT  *
FROM    images
WHERE   images.id IN (SELECT image_id FROM image_likes WHERE user_id = '1')
    UNION
SELECT * 
FROM images
WHERE images.user_id = '1' AND upload_type in (4,3) ORDER BY id DESC

这很好用，但现在我希望它显示最近发生的结果。例如，用户 A 上传了一张图片 -> 信息进入带有时间戳的 images。然后用户 A 喜欢其他人的图像 -> 信息进入 image_likes 并带有时间戳。现在我如何将两个时间戳列合并在一起，以便我可以按 DESC 对它们进行排序。

简要介绍一下查询的作用。从images中选择用户在image_likes中点赞过的图片信息，然后抓取用户在images中上传的所有图片并将它们合并到一个查询中。我需要的是能够使用两个表中的 timestamp 对它们进行排序。

Answer 1

这样做怎么样？？

SELECT * FROM (
    SELECT  *
    FROM    images
    WHERE   images.id IN (SELECT image_id FROM image_likes WHERE user_id = '1')
        UNION
    SELECT * 
    FROM images
    WHERE images.user_id = '1' AND upload_type in (4,3) ORDER BY id DESC
) AS a ORDER BY a.timestamp DESC;

或者更好

 (SELECT  *
    FROM images
    WHERE images.id IN (SELECT image_id FROM image_likes WHERE user_id = '1'))
        UNION
 (SELECT * 
    FROM images
    WHERE images.user_id = '1' AND upload_type in (4,3) ORDER BY id DESC)
 ORDER BY timestamp DESC

检查这个http://dev.mysql.com/doc/refman/5.5/en/union.html

更新

试试这个

 (SELECT images.id, images_likes.timestamp as timestamp FROM images JOIN images_likes 
    ON images.id=image_likes.image_id WHERE user_id = '1')
        UNION
 (SELECT images.id, images.timestamp as timestamp
    FROM images
    WHERE images.user_id = '1' AND upload_type in (4,3) ORDER BY id DESC)
 ORDER BY timestamp DESC

更新

根据您的要求进行最终查询

 (SELECT images.id, images.user_id, images.ext, images.upload_type, images_likes.timestamp as timestamp FROM images JOIN images_likes 
    ON images.id=image_likes.image_id WHERE images_likes.user_id = '1')
        UNION
 (SELECT images.id, images.user_id, images.ext, images.upload_type, images.timestamp as timestamp
    FROM images
    WHERE images.user_id = '1' AND upload_type in (4,3) ORDER BY id DESC)
 ORDER BY timestamp DESC