mysql与pg列转行实现

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;