脚本如下:
--------------------------------
create table TestUser (
UserID int not null, /* ID,主键 */
UserName varchar(50) not null, /* 规则编号 */
constraint PK_TestUser primary key (UserID)
)
insert TestUser values(1,'张三')create table UserInfo(
InfoID int not null,
infoTitle varchar(50) not null,
UserID int not null,
constraint PK_UserInfo primary key (InfoID)
)
insert UserInfo values(1,'test1',1)
insert UserInfo values(2,'test2',1)
insert UserInfo values(3,'test3',1)
---------------------------------------要求结果为:
UerID UserName InfoTitle
1 张山 test1,test2,test3麻烦各位帮忙看看。谢谢
--------------------------------
create table TestUser (
UserID int not null, /* ID,主键 */
UserName varchar(50) not null, /* 规则编号 */
constraint PK_TestUser primary key (UserID)
)
insert TestUser values(1,'张三')create table UserInfo(
InfoID int not null,
infoTitle varchar(50) not null,
UserID int not null,
constraint PK_UserInfo primary key (InfoID)
)
insert UserInfo values(1,'test1',1)
insert UserInfo values(2,'test2',1)
insert UserInfo values(3,'test3',1)
---------------------------------------要求结果为:
UerID UserName InfoTitle
1 张山 test1,test2,test3麻烦各位帮忙看看。谢谢
(@id int)
returns varchar(1000)
as
begin
declare @s varchar(1000)
select @s = isnull(@s+',','')+ infoTitle from TestUser a,UserInfo b where a.UserID =b.UserID and a.UserID = @id
return @s
end
go
Select UserID ,UserName ,dbo.f_f(UserID )
from TestUser
--函数
create function dbo.gettitle(@userid int)
returns varchar(8000)
as
begin
declare @ret varchar(8000)
select @ret=isnull(@ret+',','')+infoTitle
from UserInfo
where UserID=@userid
return @ret
end
--查询
select UserID,UserName,dbo.gettitle(UserID) as InfoTitle
from TestUser