MySQL列分割成多行的方法

建表：
create table user_tag
(
    userid varchar(32),
    tag    varchar(32)
);


insert into user_tag
values ('1', '1,2'),
       ('10', '1,2,10'),
       ('11', '4,2,3'),
       ('12', '8,7,3'),
       ('13', '9,2,3'),
       ('14', '6,2,3'),
       ('3', '1,4,3'),
       ('4', '1,5,3'),
       ('5', '1,2,6'),
       ('6', '1,2,7'),
       ('7', '8,2,3'),
       ('8', '1,9,3'),
       ('9', '1,2,3')
       ;

起步想法：# help_topic 是从0开始的
select a.userid, substring_index(substring_index(a.tag, ',', b.help_topic_id + 1), ',', -1) as sub_tag
from user_tags a
         join mysql.help_topic b on b.help_topic_id < (length(a.tag) - length(replace(a.tag, ',', '')) + 1);

答案：

# 谁拥有的标签最多
select a.userid, a.str_count
from (select *, dense_rank() over (order by t.str_count desc ) as t_rank
      from (select *,
                   (length(tag) - length(replace(tag, ',', '')) + 1) as str_count
            from user_tags) as t) as a
where a.t_rank = 1;



# 每个标签的用户数
select distinct t.sub_tag,
                count(*) over (partition by t.sub_tag) as t_count
from (select a.userid, substring_index(substring_index(a.tag, ',', b.help_topic_id + 1), ',', -1) as sub_tag
      from user_tags a
               join mysql.help_topic b on b.help_topic_id < (length(a.tag) - length(replace(a.tag, ',', '')) + 1)) as t;


# 哪个标签拥有的用户最多
select b.sub_tag, b.t_count
from (select *,
             dense_rank() over (order by a.t_count desc ) as t_rank
      from (select distinct t.sub_tag,
                            count(*) over (partition by t.sub_tag) as t_count
            from (select a.userid, substring_index(substring_index(a.tag, ',', b.help_topic_id + 1), ',', -1) as sub_tag
                  from user_tags a
                           join mysql.help_topic b
                                on b.help_topic_id <
                                   (length(a.tag) - length(replace(a.tag, ',', '')) + 1)) as t) as a) as b
where b.t_rank = 1;