mysql - sql语句问题？

问题描述

现在有文章表 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