mysql - MySQL：获取逗号分隔列中的数据计数

Question

我有一个存储数据的表，例如：

userid    books
ym0001    dictionary,textbooks,notebooks
ym0002    textbooks,dictionary

我想计算每本书出现的次数。我希望我的结果采用这种格式。

books       Counts
dictionary  2
notebooks   1
textbooks   2

这是mysql。请帮忙

标签： mysql

Answer 1

以下方法构建 1000 个整数的结果，然后使用这些整数 (n) 在逗号分隔的字符串中定位段，并为每个段创建一个新行，以便派生表如下所示：

用户名 | 书      
:----- | :---------
ym0001 | 字典
ym0002 | 教科书
ym0001 | 教科书
ym0002 | 字典
ym0001 | 笔记本

一旦存在，只需按书分组即可得出计数。

select
    book, count(*) Counts
from (
    select
           t.userid
         , SUBSTRING_INDEX(SUBSTRING_INDEX(t.books, ',', numbers.n), ',', -1) book
    from (
        select @rownum:=@rownum+1 AS n
        from
        (
           select 0 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
        cross join (
           select 0 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
        cross join  (
           select 0 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
        ) c
        cross join (select @rownum:=0) r
        ) numbers
    inner join mytable t
      on CHAR_LENGTH(t.books)
        -CHAR_LENGTH(REPLACE(t.books, ',', '')) >= numbers.n-1
    ) d
group by
    book
order by
    book

书 | 计数
:--------- | -----：
字典 | 2
笔记本| 1
教科书 | 2

1. 如果您已经有一个数字表，请改用它。
2. ab 和 c 的交叉连接动态生成 1000 行，如果您需要更多，请添加类似于 cie的更多交叉连接，数字的数量应超过逗号分隔数据的最大长度

db<>在这里摆弄