两个字段:
a 1,2,3,4
b 3,5,6,7
现在我需要按照 (a求和/b求和) 来进行ORDER BY排序,请问MYSQL能否实现
写个自定义函数。 假设,a b 严格用逗号(,)隔开
delimiter $$
DROP FUNCTION IF EXISTS `my_add` $$
create function my_add(_str varchar(250))returns DECIMAL
begin
declare pos DECIMAL;
declare num DECIMAL;
declare re DECIMAL;
set num = 0;
set re = 0;
set pos = locate(',',_str);
loop1: while pos > 0 do
set num = cast(left(_str,pos-1) as DECIMAL);
set re = re + num;
set _str = SUBSTRING(_str,pos+1);
set pos = locate(',',_str);
end while loop1;
set num = cast(_str as DECIMAL);
set re = re + num;
return(re);
end$$
select my_add('1,2,3,4,5')$$
# output 15