比如 select 出的结果为:field1
a
b
c
d
f
g
h
j
k
l
m
n用while循环 查找每一行 拼起来或者匹配操作,我知道这些用where条件可以做到但,我只是举这么个例子,最终想知道怎么循环一个结果集!!
a
b
c
d
f
g
h
j
k
l
m
n用while循环 查找每一行 拼起来或者匹配操作,我知道这些用where条件可以做到但,我只是举这么个例子,最终想知道怎么循环一个结果集!!
DECLARE @SQL VARCHAR(8000)
SELECT @SQL=ISNULL(@SQL+',','') +FIELD1 FROM TB
SELECT @SQL?
但是有些操作可以不用游标,比如拼起来只需declare @s varchar(8000)
select @s=isnull(@s,'') + field1
from tabselect @s
----------------------------------------------------
/*如何将一列中所有的值一行显示
数据源
a
b
c
d
e
结果
a,b,c,d,e
*/create table tb(col varchar(20))
insert tb values ('a')
insert tb values ('b')
insert tb values ('c')
insert tb values ('d')
insert tb values ('e')
go--方法一
declare @sql varchar(1000)
set @sql = ''
select @sql = @sql + t.col + ',' from (select col from tb) as t
set @sql='select result = ''' + left(@sql , len(@sql) - 1) + ''''
exec(@sql)
/*
result
----------
a,b,c,d,e,
*/--方法二
declare @output varchar(8000)
select @output = coalesce(@output + ',' , '') + col from tb
print @output
/*
a,b,c,d,e
*/drop table tb
如果是想遍历,然后做其他操作,用油标.参考如下:
游标的基本写法declare @id int,@name varchar(20);
declare cur cursor fast_forward for
select id,name from a;
open cur;
fetch next from cur into @id,@name;
while @@fetch_status=0
begin
--做你要做的事
fetch next from cur into @id,@name;
end
close cur;
deallocate cur;
decalre Cur_SysObjects Cursor Read_Only For
select Name from SysObjects
Declare @Name Varchar(255)
-- 打开游标
Open Cur_SysObjects
-- 提取游标数据
Fetch Next Form Cur_SysObjects Into @Name
While (@@Fetch_Status = 0)
begin
-- 执行其他语句
-- 提取游标数据
Fetch Next Form Cur_SysObjects Into @Name
end
-- 关闭游标
Close Cur_SysObjects
-- 释放游标
DealLocate Cur_SysObjects
--不喜欢游标。。
create table tb(col varchar(20))
insert tb values ('a')
insert tb values ('b')
insert tb values ('c')
insert tb values ('d')
insert tb values ('e')
go
declare @temp varchar(20)
declare @str varchar(4000)
set @str=''select top 1 @temp=col from tb
while @temp<>''
begin
set @str=@str+@temp
delete from tb where col=@temp
select top 1 @temp=col from tb
end
drop table tb
create table tb(col varchar(20))
insert tb values ('a')
insert tb values ('b')
insert tb values ('c')
insert tb values ('d')
insert tb values ('e')
go
declare @temp varchar(20)
declare @str varchar(4000)
set @str=''select top 1 @temp=col from tb
while @temp<>''
begin
set @str=@str+@temp
delete from tb where col=@temp
set @temp='' --此处原来有点小问题,更正之。
select top 1 @temp=col from tb
endselect @str