MYSQL联合订单 [英] MYSQL UNION ORDER BY

问题描述

我目前有这个

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对其进行排序.

And this works great, but now i want it to display the most recent results thats have happend. For example, User A uploaded a image-> information goes into images with a timestamp. User A then likes someone else's image -> Information goes into image_likes with a timestamp. Now how would i make the two timestamp column's merge together so i can sort them by DESC.

Breif运行查询的内容. 从images中选择用户喜欢的image_likes中的图像的图像信息，然后获取用户从images中上传的所有上载内容，并将其合并为查询.我需要的是能够使用两个表中的timestamp对其进行排序.

Breif run over of what the query does. Selects the image information from images where the user has liked a image in image_likes and then it grabs all of the uploads the user has uploaded from images and merges them into a query. What i need is to be able to sort them using the timestamp from both tables.

推荐答案

如何做到这一点?

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

chk this http://dev.mysql.com/doc/refman/5.5/en/union.html

chk this 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

更新

根据您的要求的最终查询

Final query as per your requirement

 (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

这篇关于MYSQL联合订单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！