mysql评论与回复一起查_mysql 查询所有评论以及回复

怪我咯2017-04-17 15:32:302楼

我理解：question是问题表，罗列所有的提问

answer 是回复表，对某个具体问题的回复，用question_id与question表中的id做关联

reply 是对某个回复的评论，用answer_id与answer表中的id做关联

以下代码，基于以上理解

select t1.q_id as 问题id

, t1.q_user_id as 提问者id

, t1.q_content as 问题内容

, t2.a_id as 回复id

, t2.a_user_id as 回复者id

, t2.a_content as 回复内容

, t3.r_id as 评论id

, t3.r_user_id as 评论者id

, t3.r_content as 评论内容

from

(

select id as q_id ,user_id as q_user_id ,content as q_content

from question

) t1 -- 所有的问题列表，用id做唯一性的区分

left outer join

(

select id as a_id ,question_id ,user_id as a_user_id ,content as a_content

from answer

) t2

on t1.q_id = t2.question_id -- 每个question_id对应的回复

left outer join

(

select id as r_id ,answer_id ,user_id as r_user_id ,content as r_content

) t3

on t2.a_id = t3.answer_id -- 每个answer_id对应的评论