php - 从3个表中按时间对mysql结果进行排序

Question

我有 3 个表:questions、articles 和 pictures

每个表中的行包含一个 current_timestamp 列 posted，并链接到一个 id。我想按时间戳对所有三个行的结果进行排序，并且只回显三个中最新的(例如:如果问题是 ID 中最新的，则只显示它)

if(count($interests) != 0){ foreach($interests as $interests_following){
    $interestid = mysql_result(mysql_query("SELECT `id` FROM `interests` WHERE `name` = '$interests_following'"),0);
    $interestquestions = @mysql_result(mysql_query("SELECT `question_text`, `posted` FROM `questions` WHERE `interest` = '$interests_following'"),0);
    $interestarticles = @mysql_result(mysql_query("SELECT `article_title`, `posted` FROM `articles` WHERE `interest_id` = '$interestid'"),0);
    $interestpictures = @mysql_result(mysql_query("SELECT `interest_pic_title`, `posted` FROM `interest_pictures` WHERE `interest_id` = '$interestid'"),0);

echo '.$interests_following.': //<Only display 1 newest item (article/picture/question here>

Answer 1

使用UNION ALL:

SELECT posted 
FROM
(
    SELECT `question_text`, `posted` FROM `questions` 
    WHERE `interest` = '$interests_following'
    UNION ALL
    SELECT `article_title`, `posted` FROM `articles` 
    WHERE `interest_id` = '$interestid'
    UNION ALL
    SELECT `interest_pic_title`, `posted` FROM `interest_pictures` 
    WHERE `interest_id` = '$interestid'
) t
ORDER BY posted DESC LIMIT 1