我想从北风数据库中获得如下结果:(注意,是一行)
姓名:Davolio,职位:Sales Representative 姓名:Fuller,职位:Vice President, Sales 姓名:Leverling,职位:Sales Representative 姓名:Peacock,职位:Sales Representative 姓名:Buchanan,职位:Sales Manager 姓名:Suyama,职位:Sales Representative 姓名:King,职位:Sales Representative 我写的一个游标如下,您们看看,感觉重复了啊,很重复啊,重复就意味失败的发生几率加大,DECLARE name_cursor CURSOR
FOR SELECT [LastName] FROM [Employees] ---a
DECLARE title_cursor CURSOR
FOR SELECT [Title ] FROM [Employees] --a sameDECLARE @sum VARCHAR( 1000),@s VARCHAR( 1000),@s1 VARCHAR( 1000)
SET @s =''
SET @s1 =''
SET @sum =''OPEN name_cursor ---b
OPEN title_cursor ---b same
FETCH name_cursor INTO @s
FETCH title_cursor INTO @s1
WHILE (@@FETCH_STATUS =0)
BEGIN
SET @sum=@sum+'姓名:' +@s+','+'职位:'+@s1+' '
FETCH name_cursor INTO @s --- c
FETCH title_cursor INTO @s1 --- c same
END
CLOSE name_cursor
CLOSE title_cursor
deallocate name_cursor
deallocate title_cursor
SELECT @sum
姓名:Davolio,职位:Sales Representative 姓名:Fuller,职位:Vice President, Sales 姓名:Leverling,职位:Sales Representative 姓名:Peacock,职位:Sales Representative 姓名:Buchanan,职位:Sales Manager 姓名:Suyama,职位:Sales Representative 姓名:King,职位:Sales Representative 我写的一个游标如下,您们看看,感觉重复了啊,很重复啊,重复就意味失败的发生几率加大,DECLARE name_cursor CURSOR
FOR SELECT [LastName] FROM [Employees] ---a
DECLARE title_cursor CURSOR
FOR SELECT [Title ] FROM [Employees] --a sameDECLARE @sum VARCHAR( 1000),@s VARCHAR( 1000),@s1 VARCHAR( 1000)
SET @s =''
SET @s1 =''
SET @sum =''OPEN name_cursor ---b
OPEN title_cursor ---b same
FETCH name_cursor INTO @s
FETCH title_cursor INTO @s1
WHILE (@@FETCH_STATUS =0)
BEGIN
SET @sum=@sum+'姓名:' +@s+','+'职位:'+@s1+' '
FETCH name_cursor INTO @s --- c
FETCH title_cursor INTO @s1 --- c same
END
CLOSE name_cursor
CLOSE title_cursor
deallocate name_cursor
deallocate title_cursor
SELECT @sum
--这样就可以了。。
declare @sql varchar(8000)
select @sql=isnull(@sql+' ','')+'姓名:'+[LastName]+',职位:'+title from Employees
print @sql
set @str=N''select @str=@str+' '+N'姓名:'+lastname+' '+N'职位:'+title
from Employeesselect @str
--這樣就OK
declare @s varchar(8000)
set @s=''select @s=@s+' 姓名:'+Rtrim([LastName])+',职位:'+Rtrim([Title])
From [Employees] select @s
用游标效率极其低,为什么要舍易求难呢?
如果是用游标,也不需要两个,比如:DECLARE @sum VARCHAR( 1000),@s VARCHAR( 1000),@s1 VARCHAR( 1000)
DECLARE mycursor CURSOR FOR SELECT [LastName],Title FROM [Employees]
OPEN mycursor
FETCH mycursor INTO @s,@s1
WHILE (@@FETCH_STATUS =0)
BEGIN
SET @sum=isnull(@sum+' ','')+'姓名:' +@s+','+'职位:'+@s1+' '
FETCH mycursor INTO @s,@s1
END
CLOSE mycursor
deallocate mycursor
SELECT @sum
declare @sql varchar(8000)
select @sql=isnull(@sql+' ','')+'姓名:'+[spmch]+',职位:'+shpchd from spkfk
print @sql--姓名:野生山葛粉,职位:江西三清制药有限公司 姓名:鼎龙红花油这样的结果
declare @name varchar(100), @title varchar(200)
declare @sum varchar(8000)
set @sum=''declare c1 cursor for
select LastName,Title from Employeesopen c1
fetch next from c1 into @name,@title
while @@fetch_status=0
begin
select @sum=@sum+' 姓名:'+@name+',职位:'+@title
fetch next from c1 into @name,@title
endclose c1
deallocate c1