需求:一个字段有多行记录,查询结果为去重排序的一行记录,例如记录值为
查询结果为:
1,2,3,4,5,23,56,67
使用数字辅助表实现 -- 建立数字辅助表
createtable nums (
a intnotnullprimarykey
);
delimiter $$
createprocedure pFastCreateNums(cnt int)
begin
declare s intdefault 1;
truncatetable nums;
insertinto nums select s;
while s<=cnt do
insertinto nums select a+s from nums where a+s <= cnt;
set s=s*2;
end while;
commit;
end $$
delimiter ;
call pFastCreateNums(1000000);
-- 建立测试表
createtable t1 (
a varchar(100)
);
insertinto 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
groupby a) t1;
--转自