如何将所有类别逗号与此查询分开

I have two tables Posts, categories. Here in the posts table I stored the category values as comma separated string like this 5,8,23,7. While displaying the posts, I just want to show the post categories as comma separated like this Flower, Birds, Animals. So I tried some queries nothing helped me to get it. The Posts Table Example.

我有两个表帖子，类别。在posts表中，我将类别值存储为逗号分隔的字符串，如5,8,23,7。在显示帖子时，我只想将帖子类别显示为逗号分隔，如花，鸟，动物。所以我尝试了一些查询，没有任何帮助我得到它。帖子表示例。

ID  Post title      categories
3   Example Post     5,7,23,8

And the Categories Table will be like this

而Categories表就是这样的

ID   name  
5    Flowers
7    Animals
8    Birds
23   Naturals

And I want result like this

我想要这样的结果

ID  Post Tile        Category
3   Example Post     Flowers, Animals, Birds

For that I tried this query but didn't help me to get it .

为此，我尝试了这个查询，但没有帮助我得到它。

SELECT post.ID, post.Post_title, (SELECT cat.name FROM Categories as cat WHERE cat.ID IN (post.category)) AS Categories FROM Posts as post

And it returns only one category, it retrieves the first category name only.

它只返回一个类别，它只检索第一个类别名称。

3 个解决方案

#1

EDIT: Updated to consider the fact that Posts.categories is a CSV value.

编辑：更新以考虑Posts.categories是CSV值的事实。

You need to use the GROUP_CONCAT() function, and also the trick posted in SQL split comma separated row in order to split the JOIN CSV and then create the output CSV:

您需要使用GROUP_CONCAT（）函数，以及在SQL拆分逗号分隔行中发布的技巧，以便拆分JOIN CSV，然后创建输出CSV：

SELECT
    Posts.ID,
    Posts.Post_title,
    GROUP_CONCAT(Categories.name SEPARATOR ',') AS `Category`
FROM Posts
INNER JOIN Categories
    ON Categories.ID IN (
        SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(Posts.categories, ',', n.n), ',', -1) value
        FROM (
            SELECT a.N + b.N * 10 + 1 n
            FROM
            (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
            ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
            ORDER BY n
        ) n
        WHERE n.n <= 1 + (LENGTH(Posts.categories) - LENGTH(REPLACE(Posts.categories, ',', '')))
        ORDER BY value
    )
GROUP BY
    Posts.ID,
    Posts.Post_title

Fiddle: http://sqlfiddle.com/#!9/b1ddc9/4

小提琴：http：//sqlfiddle.com/#！9/b1ddc9/4

#2

If you simply must use that schema, you could try something like this:

如果您只是必须使用该架构，您可以尝试这样的事情：

select P.ID, P.Title, (
       select group_concat(C.name SEPARATOR ', ')
       from Categories C
       where LOCATE(CONCAT(C.ID, ','), P.categories) > 0
          or LOCATE(CONCAT(', ', C.ID), P.categories) > 0
) as categories
from Post P;

It's hacky because in a comma separated list either a value occurs before a comma or after a comma, taking into account values at the beginning or end of the list. You can't just do a straight substring, because otherwise you'll get a category ID of 5 matched to a 'categories' value of '1, 2, 555'.

这很麻烦，因为在逗号分隔的列表中，值可以在逗号之前发生，也可以在逗号之后，考虑列表开头或结尾的值。你不能只做一个直子串，因为否则你会得到一个类别ID为5的'类别'值'1,2,555'。

#3

I believe you can use group_concat? Just join the Categories table and group_concat the name group_concat(name)

我相信你可以使用group_concat？只需加入Categories表和group_concat名称group_concat（name）

as for the JOIN try to use find_in_set(Categories.ID, Post.ID) > 0

至于JOIN尝试使用find_in_set（Categories.ID，Post.ID）> 0

Thou this approach may not work if the comma delimited Category ID has space i.e. 1, 2 etc...But if you are saving it accurately this may work.

如果以逗号分隔的类别ID具有空格，即1,2等，则此方法可能无效。但如果您准确保存它，则可能有效。

#1