如何在SQL中循环某一列记录？除了用游标之外？

比如"select ID from Users"得到了多行记录，现在我想在SQL中或者存储过程中循环 ID，而不是在SQL SERVER把得到的记录集发送到ASP.NET程序中中再循环。该怎么做？如何书写SQL？就是想在SQL中循环记录.

select ID,0 as levl into #tmp from Users where ID=@ID_
这句话的意思是选两列 id和 levl 存入临时表tmp 条件是等于id_的
levl列是全零

比如"select ID from Users"得到了多行记录，现在我想在SQL中或者存储过程中循环 ID，而不是在SQL SERVER把得到的记录集发送到ASP.NET中再循环。该怎么做？如何书写SQL
--------
感觉还是放在程序中处理好！~另外请教into的用法。如下句
“select ID,0 as levl into #tmp from Users where ID=@ID_”
--------
新建临时表#tmp并从users表中复制部分字段到新表#tmp中。into和as有什么区别吗
----------
as子句可用来更改结果集列的名称或为派生列分配名称。
创建新表并将来自查询的结果行插入新表中。
这两个没什么关系吧
这里的#tep 为何可以用“#”开头声明？变量不是不能以特殊字符开头吗，这里的#tmp不是变量吗？求#的用法？
--------
#是临时表的开头，临时表在数据库中看不到，但它是存在的。@是变量的开头。

在sql 中循环遍历应该离不开游标# 后加表名表示此表为虚表在存储过程执行完会自动释放
as 在select 语句中一般用来给字段表名起别名

建议放到asp.net代码中循环，用游标的效率非常低，我在项目中遇到很多超时问题导致的项目bug或事故，都是因为使用游标引起的。
或许你迫于一些特殊处理的原因，想在数据库中循环处理某一列数据，这个是有办法可以做到的，不一定要用循环，用sql的一些自带函数，或者临时表缓存数据，再进行一个update的处理，都可以实现。在这里，into的作用是将一张二维表放到#tmp的临时表中，使用into的话，就不用自己写建表语句，它会根据二维表格的数据自动定义这张临时表。
而这个临时表的生命周期也就存在于存储过程中，调用过程中存在，当过程处理结束，程序释放连接时，这个临时表会被自动清理。into一般是用在"select * into #temp from ……"这种场合，用于将结果集放置到临时表中。
as一般是在取别名的时候使用，例如：select a.Name as fName from tableA as a。
并且as是可以省略的。
表名前，添加“#”,则示意该表是一张临时表，它会被创建到系统数据库“tempdb”中，当超过它的作用域时，它会被自动清理，这是它与正式表最大的差别。
另，数据库声明变量的标识是“@”和关键字“declare”，例如：
declare @name int
这就是一个完善的声明临时变量的例子。

into补充一点：
select [ID], [Name], [Gender] into #tep from Users这句话相当于：
if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#tep') and type='U')
begin
    drop table #tep
end
create table #tep(
    [ID] int,
    [Name] nvarchar(30),
    [Gender] bit
)
insert into #tep select [ID], [Name], [Gender] from Users

游标是逐行逐行循环，当行数到几千行，执行起来会很慢。
其实，理由很简单，虽然我不知道数据库处理临时表的底层逻辑是如何实现的，但要明白，用游标来循环，循环体是你来写的，你要调用sql命令，这必然要做一些解析和命令的转化。
而临时表的读写操作是数据库内部实现的，它的性能问题，已经被某些专家优化过了，毕竟sql server是一个成熟的产品。
不信的话，我给你一个例子，你做个测试，例子在接下来的回帖中。

如果不用游标，那么我如何循环取得 SELECT 的记录呢？比如数据库的一个表代表的是实际的一个树,比如tableTree，里面共有ID ,parnetID,Name,sortID四个字段。现在需要得到某一个节点的所有子节点，比如要得到ID 为3的所有子节点：则执行"Select ID from tableTree where parnetID =3"这样会得到ID为3的一级子节点，要得到2级,3级,.....n级的子节点，则必须要循环上面的记录集中的ID。那么如何循环ID？

对比一下吧，先执行创建表格与初始化数据的脚本，然后再依次执行游标操作和普通sql的操作的执行效率。-- 创建表格与初始化数据
create table testTB(
    tid int not null identity,
    tname varchar(36)
)declare @flag int
set @flag = 10000while @flag > 0
begin
    insert into testTB(tname) values(newid())
    set @flag = @flag -1
end--========= 游标循环操作 =========--declare @fl_field nvarchar(36)
declare @id int-- 定义游标
declare Temp_Table cursor for
        select tid, tname from testTB -- 查询结果    -- 打开游标取第一行记录赋给@fl_field
    open Temp_Table
    fetch next from Temp_Table into @id, @fl_field    -- 循环开始
    while @@fetch_status = 0
    begin
        set @fl_field = newid()
        update testTB set tname=@fl_field where tid=@id

        -- 取下一条记录
        fetch next from Temp_Table into @id, @fl_field
    end
    -- 循环结束    -- 关闭游标删除游标引用
    close Temp_Table
    deallocate Temp_Table--========= 游标循环操作 =========--
--========= 临时表的操作 =========--
-- 其实这里也不用放到临时表中，一句话就搞定了，可能这个例子过于简单，不过你可以测试上面游标操作的效率
update testTB set tname=newid()
--========= 临时表的操作 =========--

获得一棵树，用游标也不好实现，一般的做法，是在存储过程中，递归调用一个函数来实现，不过这个也有效率问题。
现在流行有一种解决办法，效率较高，而且查询语句也超级简单。这需要一个约定：
你需要在表格中添加一个字段，fIndexNote varchar(50)，这个字段来保存上下级的特征。
假设你有这样一棵树：
中国
     上海市
          浦东新区
          徐汇区
          黄浦区
     浙江省
          杭州市
          宁波市
它们对应的note分别是
1000
     10002000
          100020003000
          100020003001
          100020003002
     10002001
          100020013100
          100030013101
设置fIndexNote有一些约定的规则：
1. 节点越深fIndexNote越长
2. 下级会包含上级的特征
3. 层次之间字符数的差异是规定好的，比如，我用4个字符表示层次之间的差异
4. 节点之间的取值要唯一，要考虑到，当节点足够深时，可能出现重复的编码的情况，要避免以此类推，这样的话，如果你要找浙江省的所有子节点，你写一句sql就可以查询出来：
select * from tableTB where fIndexNote like '%10002000%'

这是典型的递归查询问题。sql server 2005以上提供了一个叫做Common Table Expression(CTE)的技术，你可以检索一下。
另外游标是把每条记录拷出来，再操作，所以效率很低，只在一些复杂的情形下使用，而且很多情况下可以用其他方法代替。
临时表其实也有两种，一种是完全在内存里的，用declare @mytable table (id int, value varchar(10)) 的方式建立，另一种建在tempdb数据库里，用create table #mytable (id int, value varchar)来定义。

这个是个不错的办法。正如你所说的，做一个递归函数，我现在是在ASP.NET代码中实现这个递归函数的，我感觉效率太低，如果在存储过程中实现，那么必须用游标来实现递归吗？但他们说游标效率低，那么游标和客户端的asp.net代码，到底那个效率高？同时，循环数据，除了用游标，还有其他办法吗还有如果用游标，如何把得到的ID数都保存到一个临时表呢，求代码讲解下。

用递归实现的方法，我也提供一个思路给你。
函数的作用根据当前ID，查询它的子节点的ID，用“,”分隔开，例如，如果父节点Id=1，它包含两个子节点：3与4，那么函数得到的结果是“3,4”然后，重点就是存储过程了：
1. 存储过程新建一个临时表，用于存储查询结果
2. 调用函数查当前传递进来的id，获得它的子节点，通过函数返回值作为条件，获得表格数据，插入临时表中，然后找一个split()的方法，循环遍历它的子节点，然后，在这个循环中，递归调用函数
3. 最终临时表的结果集，就是你想要的结果集
4. 如果节点层次足够深，节点足够多，这个效率也就足够低

我想过用字符串相加，但是我要做的这个数相当的大(目前大概快10万条记录，以后可能会增加到100万左右)所以不得不考虑效率的问题，所以用字符串相加，用逗号分隔开的做法，必须要text类型，而且也可能容易出错。所以，必须放到一个临时的表中。而且

--生成测试数据
create table tb(id varchar(3) , pid varchar(3) , name varchar(10))
insert into tb values('001' , null  , '广东省')
insert into tb values('002' , '001' , '广州市')
insert into tb values('003' , '001' , '深圳市')
insert into tb values('004' , '002' , '天河区')
insert into tb values('005' , '003' , '罗湖区')
insert into tb values('006' , '003' , '福田区')
insert into tb values('007' , '003' , '宝安区')
insert into tb values('008' , '007' , '西乡镇')
insert into tb values('009' , '007' , '龙华镇')
insert into tb values('010' , '007' , '松岗镇')
go--创建用户定义函数
create function f_cid(@id varchar(10)) returns varchar(8000)
as
begin
  declare @i int , @ret varchar(8000)
  declare @t table(id varchar(10) , pid varchar(10) , level int)
  set @i = 1
  insert into @t select id , pid , @i from tb where id = @id
  while @@rowcount <> 0
  begin
    set @i = @i + 1
    insert into @t select a.id , a.pid , @i from tb a , @t b where a.pid = b.id and b.level = @i - 1
  end
  select @ret = isnull(@ret , '') + id + ',' from @t
  return left(@ret , len(@ret) - 1)
end
go --执行查询
select id , children = isnull(dbo.f_cid(id) , '') from tb group by iddrop table tb
drop function f_cid/*
id   children
---- ---------------------------------------
001  001,002,003,004,005,006,007,008,009,010
002  002,004
003  003,005,006,007,008,009,010
004  004
005  005
006  006
007  007,008,009,010
008  008
009  009
010  010（所影响的行数为 10 行）
create table tb(id varchar(3) , pid varchar(3) , name nvarchar(10))
insert into tb values('001' , null  , N'广东省')
insert into tb values('002' , '001' , N'广州市')
insert into tb values('003' , '001' , N'深圳市')
insert into tb values('004' , '002' , N'天河区')
insert into tb values('005' , '003' , N'罗湖区')
insert into tb values('006' , '003' , N'福田区')
insert into tb values('007' , '003' , N'宝安区')
insert into tb values('008' , '007' , N'西乡镇')
insert into tb values('009' , '007' , N'龙华镇')
insert into tb values('010' , '007' , N'松岗镇')
go;with t as
(
    select id , cid = id from tb
    union all
    select t.id , cid = tb.id
    from t join tb on tb.pid = t.cid
)
select id , cid = STUFF((SELECT ',' + rtrim(cid) FROM t WHERE id = tb.id FOR XML PATH('')) , 1 , 1 , '')
from tb
group by id
order by id
/*
id   cid
---- ---------------------------------------
001  001,002,003,005,006,007,008,009,010,004
002  002,004
003  003,005,006,007,008,009,010
004  004
005  005
006  006
007  007,008,009,010
008  008
009  009
010  010(10 行受影响)
*/;with t as
(
    select id , name , cid = id , path = cast(name as nvarchar(100)) from tb
    union all
    select t.id , t.name , cid = tb.id , path = cast(tb.name as nvarchar(100))
    from t join tb on tb.pid = t.cid
)
select id , name ,
       cid = STUFF((SELECT ',' + rtrim(cid) FROM t WHERE id = tb.id FOR XML PATH('')) , 1 , 1 , ''),
       path = STUFF((SELECT ',' + path FROM t WHERE id = tb.id FOR XML PATH('')) , 1 , 1 , '')
from tb
group by id , name
order by id
/*
id   name       cid                                         path
---- ---------- ------------------------------------------- ---------------------------------------------------------------------
001  广东省     001,002,003,005,006,007,008,009,010,004     广东省,广州市,深圳市,罗湖区,福田区,宝安区,西乡镇,龙华镇,松岗镇,天河区
002  广州市     002,004                                     广州市,天河区
003  深圳市     003,005,006,007,008,009,010                 深圳市,罗湖区,福田区,宝安区,西乡镇,龙华镇,松岗镇
004  天河区     004                                         天河区
005  罗湖区     005                                         罗湖区
006  福田区     006                                         福田区
007  宝安区     007,008,009,010                             宝安区,西乡镇,龙华镇,松岗镇
008  西乡镇     008                                         西乡镇
009  龙华镇     009                                         龙华镇
010  松岗镇     010                                         松岗镇(10 行受影响)
*/drop table tb

with t as
(
    select id , name , cid = id , path = cast(name as nvarchar(100)) from tb
    union all
    select t.id , t.name , cid = tb.id , path = cast(tb.name as nvarchar(100))
    from t join tb on tb.pid = t.cid
)
select id , name ,
       cid = STUFF((SELECT ',' + rtrim(cid) FROM t WHERE id = tb.id FOR XML PATH('')) , 1 , 1 , ''),
       path = STUFF((SELECT ',' + path FROM t WHERE id = tb.id FOR XML PATH('')) , 1 , 1 , '')
from tb
group by id , name
order by id
这个就用了所谓的CTE

- - 这个方法是我老大教我的，我们现在项目表现父子关系的表格，除了会使用parentid之外，还会添加这样一个字段，目的就是为了方便查询，只要规则制定没有问题，查询结果是不可能有问题的。就怕你在设置唯一编码的时候出现偏差，如果怕数字不够用，可以把abcd英文字母用进来。
比如说，顶级菜单使用：A000、A001、A002……，次级使用B000、B001……，三级使用C000、C001……单纯说数字ID索引，必然比like是要快的，但关键是你存储过程的性能损耗，是在递归调用的过程中，而不是纯粹按ID索引就完事了。

可以使用临时表加一个自增列CREATE TABLE #ForUserName(
[ForID] [int] IDENTITY(1,1) NOT NULL,
[ID] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL
)
INSERT INTO #ForUserName([UserName])
SELECT ID from UsersDECLARE @MaxForID int
DECLARE @id INT
SELECT @MaxForID=Max([ForID]) FROM #ForUserName
WHILE(@ID<=@MaxForID)
BEGIN
UPDATE #ForUserName ...WHERE @id=ForID
END

嗯目前还没遇到问题。
你也可以研究下CTE的实现方法，既然是sql server 2005自带的功能，那性能必然不低，而且它的优势在于，你不需要修改数据库，新增一列，也不需要为这个初始化数据而头疼。

declare @CurrentID int
Set @CurrentID=0
while(exists(select top 1 * from Users where UserID>@CurrentID order by UserID))
begin
select top 1 @CurrentID=UserID from Users where UserID>@CurrentID order by UserID
end

begin tran
declare @sid int
insert into assetApplicationInfo(sTitle,sContent,sReason,userId,fId,sTime) values (@sTitle,@sContent,@sReason,@userId,@fId,getdate())
select @sid=@@IDENTITY
insert into disposeOpinionInfo(sId,userId,typeId) values(@sid,@userId,@typeId)
declare @number int
declare @i int
declare @fuserId nvarchar(50)
set @i=1
select @number=count(userId) from flowDetilInfo where fId=1while(@i<=@number)
begin
    select top(1) @fuserId=userId from flowDetilInfo where userId not in(select top(@i-1) userId from flowDetilInfo order by dId asc)
    insert into disposeOpinionInfo(sId,userId) values(@sid,@fuserId)
    set @i=@i+1
end
commit tran
项目里的一个遍历不知道效率怎么样~~

调试易

如何在SQL中循环某一列记录？除了用游标之外？

解决方案 »