我有3张桌子

Blogposts表,category表,joins表。

SELECT post_id,
       post_title,
       post_text,
       post_poster,
       post_date,
       post_slug,
       category_name,
       user_name

FROM blogposts_categories_join

INNER JOIN blogposts ON post_id = bcj_post_id
INNER JOIN categories ON bcj_category_id = category_id
INNER JOIN users ON post_user = user_id
GROUP BY post_id


这是我的查询,我想获取主页的帖子。一切正常,但是获取的数据只有一个类别,我想做这样的事情:

发表于:一般,新闻,健康

如何更改查询以获取所有类别的帖子?

最佳答案

由于您已经按帖子分组,因此您可以使用GROUP_CONCAT来获取所有类别,以逗号分隔,例如(未经测试);

SELECT post_id,
       post_title,
       post_text,
       post_poster,
       post_date,
       post_slug,
       GROUP_CONCAT(category_name) category_names,
       user_name
FROM blogposts_categories_join
INNER JOIN blogposts ON post_id = bcj_post_id
INNER JOIN categories ON bcj_category_id = category_id
INNER JOIN users ON post_user = user_id
GROUP BY post_id

10-08 02:30