如果你的name列的字段如你给出的一样规则:每行数据三个字符且都用;隔开,你可以用以下的语句: select c1,count(c1) from ( select substring_index(name,';',1)c1 from test union all select substring_index(substring_index(name,';',2),';',-1)c1 from test union all select trim(BOTH ';' FROM substring_index(name,';',-2))c1 from test ) t group by c1 order by count(c1) desc;
如果你的name列的字段如你给出的一样规则:每行数据三个字符且都用;隔开,你可以用以下的语句:
select c1,count(c1) from
(
select substring_index(name,';',1)c1 from test
union all
select substring_index(substring_index(name,';',2),';',-1)c1 from test
union all
select trim(BOTH ';' FROM substring_index(name,';',-2))c1 from test
) t
group by c1
order by count(c1) desc;