81,091
社区成员
发帖
与我相关
我的任务
分享
drop table if exists `tbA`;
create table `tbA` (
`id` int primary key,
`name` varchar(10),
`bids` varchar(100)
);
insert into `tbA` values(1, '张三', '1,2');
insert into `tbA` values(2, '李四', '1,2,3');
drop table if exists `tbB`;
create table `tbB` (
`id` int primary key,
`qx` varchar(10)
);
insert into `tbB` values(1, '上传管理员');
insert into `tbB` values(2, '订单管理员');
insert into `tbB` values(3, '后台管理员');
drop procedure if exists `fnGetName`;
drop function if exists `fnGetName`;
delimiter ;;
create function `fnGetName`(
p_ids varchar(10)
) returns varchar(100)
begin
declare p int;
declare v_result varchar(100);
declare v_ids varchar(10);
declare v_id int UNSIGNED;
declare v_qx varchar(10);
declare v_split varchar(2);
set v_result = '';
set v_ids = p_ids;
set v_split = '';
repeat
set p = INSTR(v_ids, ',');
if p > 0 then
set v_id = cast(SUBSTR(v_ids FROM 1 for p-1) as unsigned);
set v_ids = SUBSTR(v_ids, p + 1);
else
set v_id = cast(v_ids as unsigned);
set v_ids = '';
end if;
select `qx` into v_qx from `tbB` where `id` = v_id;
set v_result = concat(v_result, v_split, v_qx);
set v_split = ', ';
until LENGTH(v_ids) <= 0 end repeat;
return v_result;
end;;
delimiter ;
select `id`, `name`, `fnGetName`(`bids`) from `tbA`;