美文网首页
mysql与pg列转行实现

mysql与pg列转行实现

作者: 瞬即逝转 | 来源:发表于2021-02-02 11:03 被阅读0次
    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;
    

    相关文章

      网友评论

          本文标题:mysql与pg列转行实现

          本文链接:https://www.haomeiwen.com/subject/lvzetltx.html