表结构和记录
userNO userName userPwd 1 admin admin
2 a a
3 b b
4 c c
5 d d
6 e e 我想在FUNCTION 返回一个123456的一个字符串。。请问在FUNCTION 里怎么写。。
userNO userName userPwd 1 admin admin
2 a a
3 b b
4 c c
5 d d
6 e e 我想在FUNCTION 返回一个123456的一个字符串。。请问在FUNCTION 里怎么写。。
调试欢乐多
SELECT @STR=ISNULL(@STR,'')+USERNO FROM TB
SELECT @STR
returns string
begin
SET @a=' ';
select @a := @a +userNO from ta
return @a;
end
returns string
begin
SET @a=' ';
select @a := @a +userNO from ta
return @a;
end
---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-07-29 20:27:06
---------------------------------
--> 生成测试数据表:tbIf not object_id('[tb]') is null
Drop table [tb]
Go
Create table [tb]([userNO] int,[userName] nvarchar(5),[userPwd] nvarchar(5))
Insert tb
Select 1,'admin','admin' union all
Select 2,'a','a' union all
Select 3,'b','b' union all
Select 4,'c','c' union all
Select 5,'d','d' union all
Select 6,'e','e'
Go
--Select * from tb-->SQL查询如下:
If not object_id('[getstr]') is null
Drop function [getstr]
Go
create function [getstr]()
returns varchar(500)
begin
declare @s varchar(500)
select @s = isnull(@s+',','') + ltrim(userNO) from tb
return @s;
end
goselect distinct dbo.getstr() as userno from tb
/*
userno
------------------------
1,2,3,4,5,6(1 行受影响)*/