MySQL 数字辅助表去重、排序、行转列

-- 建立数字辅助表  
create table nums (  
    a int not null primary key  
);  
delimiter $$  
create procedure pFastCreateNums(cnt int)  
begin  
    declare s int default 1;  
    truncate table nums;  
    insert into nums select s;  
    while s<=cnt do  
        insert into nums select a+s from nums where a+s <= cnt;  
        set s=s*2;  
    end while;  
    commit;  
end $$  
delimiter ;  
call pFastCreateNums(1000000);  
  
-- 建立测试表  
create table t1 (  
    a varchar(100)  
);  
insert into t1 values('1,2,4'),('1,4,5'),('2,3'),('23,56,67'),('3,4');  
commit;  
  
-- 查询  
select   
    group_concat(a)  
from  
    (select   
        a  
    from  
        (select   
        cast(substring_index(substring_index(t1.a, ',', nums.a), ',', - 1)  
                as unsigned) a  
    from  
        t1, nums  
    where  
        nums.a <= length(t1.a) - length(replace(t1.a, ',', '')) + 1) t  
    group by a) t1;