create t_test(xx test);
insert into t_test vlaues('1-xx;2-xx;3-xx');
insert into t_test vlaues('1-xx;3-xx;4-xx');
mysql实现
下方sql暂时只支持固定数量的分隔符
select substring_index(xx,'-',1),count(*) from (
select substring_index(xx,';',1) a from t_test
union all
select substring_index(substring_index(xx,';',2),';',-1) a from t_test
union all
select substring_index(substring_index(xx,';',3),';',-1) a from t_test) t
group by substring_index(xx,'-',1)
order by count(*) desc;
gp/pg实现
select split_part(unnest(string_to_array(xx,';')),'-',1),count(*) from t_test
group by split_part(unnest(string_to_array(xx,';')),'-',1)
order by count(*) desc;
