如何修改SQL查询以获取存在于多个分类中的问题？

原SQL查询语句如下：

SELECT faq_categories.id AS category_id, faq_categories.categories_name, faq_questions.id, 
 faq_questions.question, faq_questions.answer
 FROM faq_questions
   LEFT JOIN users ON users.certID = faq_questions.certID
   LEFT JOIN faq_categories ON faq_categories.id = faq_questions.categories 
   WHERE faq_questions.certID LIKE '%0%'
   and faq_questions.categories IN (1,2,3,4)
   GROUP BY faq_questions.question;

当前问题：语句中的GROUP BY faq_questions.question会将同一问题的多条记录合并，导致同一个问题即使属于多个分类，结果里也仅显示一次。

需求：若一个问题属于多个分类，每个分类下都要展示该问题（比如问题q1同时属于分类1和2，结果里需同时出现「分类1-q1」和「分类2-q1」）。

修改后的SQL语句 #

直接移除不必要的GROUP BY faq_questions.question即可：

SELECT faq_categories.id AS category_id, faq_categories.categories_name, faq_questions.id, 
 faq_questions.question, faq_questions.answer
 FROM faq_questions
   LEFT JOIN users ON users.certID = faq_questions.certID
   LEFT JOIN faq_categories ON faq_categories.id = faq_questions.categories 
 WHERE faq_questions.certID LIKE '%0%'
   AND faq_questions.categories IN (1,2,3,4);

结果对比 #

• 原查询结果：

category_name   question
Business          q1
Business          q2

• 修改后期望结果：

Category_name   question
Business          q1
Business          q2
new               q1
sample            q2

内容的提问来源于stack exchange，提问作者Jayasri S.V