MySQL - 如果列值包含某些内容则计算行数

Question

今天，当我意识到我需要计算数据库中的标签数时，我遇到了麻烦。我有一个带有标签列的表格，例如

tags_column
tag1
tag1,tag2

tag1,somethingElse1
somethingElse1, somethingElse2
tag2
tag3,tag1

tag2

我需要计算有多少行包含，例如tag1、tag2 和 tag3 以及没有这些标签的行数。所以我需要类似的东西

$qry = "SELECT COUNT(tags_Column) AS `tag1Count` WHERE `tags_Column` CONTAINS 'tag1', 
        (SELECT COUNT(tags_Column) FROM `TableContainsTags`) AS `tag2Count` WHERE `tags_Column` CONTAINS 'tag2',
        (SELECT COUNT(tags_Column) FROM `TableContainsTags`) AS `tag3Count` WHERE `tags_Column` CONTAINS 'tag3',
        (SELECT COUNT(tags_Column) FROM `TableContainsTags`) AS `notagCount` WHERE `tags_Column` DOESN'T_CONTAINS 'tag1' OR 'tag2' OR 'tag3'
        FROM `TableContainsTags`";

请问你有什么想法吗？

Answer 1

尝试:

SELECT sum( case when `tags_Column` CONTAINS 'tag2' then 1 else 0 end ) as tag2,
       sum( case when `tags_Column` CONTAINS 'tag3' then 1 else 0 end ) as tag3,
       .....
       sum( case when `tags_Column` CONTAINS 'tag256' then 1 else 0 end ) as tag256,
       sum( case when `tags_Column` CONTAINS `tag2` OR 
                      `tags_Column` CONTAINS 'tag3`
            then 0 else 1 end ) as doesnt_contains_tags_2_3
FROM `TableContainsTags`