問(wèn)題描述

表設(shè)計(jì)如下

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

請(qǐng)問(wèn)要怎么一次性查詢(xún)所有的評(píng)論和回復(fù)啊目前用group by和group_concat實(shí)現(xiàn)不了額..

問(wèn)題解答

我理解：question是問(wèn)題表，羅列所有的提問(wèn)answer 是回復(fù)表，對(duì)某個(gè)具體問(wèn)題的回復(fù)，用question_id與question表中的id做關(guān)聯(lián)reply 是對(duì)某個(gè)回復(fù)的評(píng)論，用answer_id與answer表中的id做關(guān)聯(lián)以下代碼，基于以上理解

select t1.q_id as 問(wèn)題id, t1.q_user_id as 提問(wèn)者id, t1.q_content as 問(wèn)題內(nèi)容, t2.a_id as 回復(fù)id, t2.a_user_id as 回復(fù)者id, t2.a_content as 回復(fù)內(nèi)容, t3.r_id as 評(píng)論id, t3.r_user_id as 評(píng)論者id, t3.r_content as 評(píng)論內(nèi)容from (select id as q_id ,user_id as q_user_id ,content as q_contentfrom question) t1 -- 所有的問(wèn)題列表，用id做唯一性的區(qū)分left outer join(select id as a_id ,question_id ,user_id as a_user_id ,content as a_contentfrom answer) t2on t1.q_id = t2.question_id -- 每個(gè)question_id對(duì)應(yīng)的回復(fù)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 -- 每個(gè)answer_id對(duì)應(yīng)的評(píng)論回答2：

select reply.,answer.,question.* fromreply right join answer onreply.answer_id = answer.I’dright join question onanswer.question_id = question.idWhere question.id =(查找的question.id)