我想用FUNCTION 得到一个12345的字符串返回。
表结构和记录
userID,userName,userPwd
1 admin admin
2 a a
3 b b
4 c c
5 d d
1.SELECT userID form 表名2.把每一条的记录的userID 连接在一起,在FUNCTION 里返回一个字符串..我写了一个函数 不知道对不对。。请帮我看一看谢谢各位 CREATE FUNCTION `strSum`()
returns varchar(255)
BEGIN
/*局部变量的定义 declare*/
declare tmpName varchar(20) default '';
declare allName varchar(255) default '';
declare cur1 CURSOR FOR select userID from userTable ;
/*开游标*/
OPEN cur1;
/*游标向下走一步*/
FETCH cur1 INTO tmpName; WHILE ( tmpname is not null) DO
set tmpName = CONCAT(tmpName ,",");
set allName = CONCAT(allName ,tmpName);
/*游标向下走一步*/
FETCH cur1 INTO tmpName;
END WHILE;
CLOSE cur1;
RETURN allName;
表结构和记录
userID,userName,userPwd
1 admin admin
2 a a
3 b b
4 c c
5 d d
1.SELECT userID form 表名2.把每一条的记录的userID 连接在一起,在FUNCTION 里返回一个字符串..我写了一个函数 不知道对不对。。请帮我看一看谢谢各位 CREATE FUNCTION `strSum`()
returns varchar(255)
BEGIN
/*局部变量的定义 declare*/
declare tmpName varchar(20) default '';
declare allName varchar(255) default '';
declare cur1 CURSOR FOR select userID from userTable ;
/*开游标*/
OPEN cur1;
/*游标向下走一步*/
FETCH cur1 INTO tmpName; WHILE ( tmpname is not null) DO
set tmpName = CONCAT(tmpName ,",");
set allName = CONCAT(allName ,tmpName);
/*游标向下走一步*/
FETCH cur1 INTO tmpName;
END WHILE;
CLOSE cur1;
RETURN allName;
select replace(group_concat(userID),',','') from tb_name
select replace(concat(userID),',','') from 表名;
drop function if exists `strSum`;
CREATE FUNCTION `strSum`()
returns varchar(255)
BEGIN
/*局部变量的定义 declare*/
declare tmpName varchar(20) default '';
declare allName varchar(255) default '';
declare v_IsEnd tinyint default 0;
declare cur1 CURSOR FOR select userID from userTable;
declare continue handler for not found set v_IsEnd=1;
/*开游标*/
OPEN cur1;
/*游标向下走一步*/
FETCH cur1 INTO tmpName;
while v_IsEnd !=1 do
/*游标向下走一步*/
set allName = CONCAT(allName ,',',tmpName);
FETCH cur1 INTO tmpName;
end while;
CLOSE cur1;
if length(allName) > 0 then
set allName = replace (allName,',','');
end if;
return allName;
END;