mysql列转行 行转列

 

列转行

SELECT 
     flag
    ,substring_index(substring_index(t.context,',', b.help_topic_id + 1), ',', -1) as result
FROM (
    select 'aa' as flag,'1,2,3,4,5,6,7' as context union all
    select 'bb' as flag,'1,2,3,4,5,6' as context union all
    select 'cc' as flag,'1,2,3,4,5' as context union all
    select 'dd' as flag,'1,2,3,4' as context union all
    select 'ee' as flag,'1,2,3' as context 
) t 
join (
    select 0 as help_topic_id union all
    select 1 as help_topic_id union all
    select 2 as help_topic_id union all
    select 3 as help_topic_id union all
    select 4 as help_topic_id union all
    select 5 as help_topic_id union all
    select 6 as help_topic_id union all
    select 7 as help_topic_id union all
    select 8 as help_topic_id union all
    select 9 as help_topic_id
) b 
    ON b.help_topic_id < (LENGTH(t.context) - LENGTH(REPLACE(t.context, ',', '')) + 1)
order by flag,result
;

 

行转列使用group_concat函数即可

select
     name
    ,group_concat(age) as ages
from (
    select 'a' as name, 0 as age union all
    select 'b' as name, 1 as age union all
    select 'b' as name, 2 as age union all
    select 'c' as name, 3 as age union all
    select 'c' as name, 4 as age union all
    select 'c' as name, 5 as age union all
    select 'd' as name, 6 as age union all
    select 'd' as name, 7 as age union all
    select 'd' as name, 8 as age union all
    select 'd' as name, 9 as age
) b 
group by name
;

 

 

set @fild='},{';
select count(1) as cnt
from (
    select
         id
        ,num_rn
        ,id2
        ,substring_index(substring_index(result,',',1),':',-1) as val
        ,replace(substring_index(substring_index(result,',',2),':',-1),'"','') as area
    from (
        SELECT
             t1.id
            ,t2.num_rn
            ,t1.id2
            ,substring_index(substring_index(t1.image_file,@fild, t2.num_rn + 1), @fild, -1) as result -- 列转行
        FROM (
            SELECT
                 measure_problem_id as id
                ,id as id2
                ,((LENGTH(problem_values) - LENGTH(REPLACE(problem_values, @fild, '')))/length(@fild) + 1) as val_lng
                ,problem_values as image_file
            from table_name
            where id='xxxxxxxxxxxxxxxxxxxxxxxx'
        ) t1
        join (
            select 
                @s:= @s + 1 as num_rn
            from (select 1 from table_name limit 300) t1
            inner join ( SELECT @s:= -1 ) temp
            -- WHERE @s <= 300
        ) t2
            ON t2.num_rn <  t1.val_lng
        order by t2.num_rn
    ) t3
) t4
;