mysql 查询所有评论以及回复

Question

表设计如下

question(id, user_id,content)
answer (id,question_id,user_id,content)
reply (id,answer_id, user_id,content)

请问要怎么一次性查询所有的评论和回复啊
目前用group by和group_concat实现不了额..

Answer 1

我理解：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对应的评论

Answer 2

選擇回覆。 ，答案。 ，問題。 * 來自
回覆
右加入答案
reply.answer_id = 答案。我
右邊加入問題
answer.question_id = Question.id
Where Question.id =(查找的question.id)