問題描述

現在有文章表 article和 文章回復表reply

id

title

user_id

create_time

id

user_id

article_id

create_time

那么如何才能獲取 用戶最近10條文章 和 回復的文章 (加起來10條,而不是分別10條)能否用一條語句完成，分開查詢我會

(SELECT id,title,create_time,’article’ as type FROM fb_articles WHERE user_id = 4)UNION(SELECT a.id,a.title,b.create_time,’reply’ as type FROM fb_articles as a,fb_replys as b WHERE a.id = b.article_id AND b.user_id = 4)ORDER BYcreate_timeDESCLIMIT 10

問題解答

SELECT * FROM(SELECT user_id,title,create_time FROM article UNION ALL SELECT user_id,article_id,create_time FROM reply) ar WHERE ar.user_id=xxx ORDER BY create_time desc LIMIT 10

你是想要找最近回復的10篇文章還是？

union 一下根據日期排序取前10不就完了。

SELECT article.id, article.title, article.user_id, article.create_time, reply.user_id, reply.article_id, reply.create_timeFROM article,replyWHERE article.id = reply.idORDER BY article.create_time desc LIMIT 10