关于 表的遍历 哪种效率更高些，大家还有没有其他好的方法。

[振乾]

IF(OBJECT_ID('Student') IS NOT null)
  DROP TABLE student
CREATE TABLE Student(
	ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
	NAME VARCHAR(20),
	Age int	
)
INSERT INTO Student
SELECT 'zhangsan',12 UNION 
SELECT 'lisi',13 UNION 
SELECT 'wangwu',14 UNION 
SELECT 'zhaoliu',15 UNION 
SELECT 'wangba',16 UNION 
SELECT 'tianqi',17   


 --SELECT * FROM Student s
 
 ------------------方法一:通过临时表
 SELECT * INTO #Student FROM Student s 
 DECLARE @sindex INT,@scount INT,@sname VARCHAR(20),@sage int
 SELECT @sindex = MIN(a.ID) FROM #Student AS a
 SELECT @scount = COUNT(a.ID) FROM #Student AS a 
 WHILE(@scount>0) --分组的个数
	BEGIN
		SELECT @sname =a.name,@sage=a.age FROM #Student AS a WHERE a.ID=@sindex
		
		select '方法一:'+@sname+'：'+STR(@sage)		
		
		DELETE FROM #Student WHERE ID=@sindex
		SELECT @sindex = MIN(a.ID) FROM #Student AS a
		SELECT @scount = COUNT(a.ID) FROM #Student  AS a	
	END	
DROP TABLE tempdb..#Student

 ------------------方法二:游标
Declare myCurrsor INSENSITIVE Cursor For        
 SELECT NAME,age FROM Student s
open myCurrsor              
fetch next from myCurrsor  
into @sname,@sage                        
while @@FETCH_STATUS=0
begin
    select '方法二:'+@sname+'：'+STR(@sage)		
fetch next from myCurrsor       
into @sname,@sage                       
end    
CLOSE myCurrsor                   
DEALLOCATE myCurrsor 

关于 表的遍历 哪种效率更高些，大家还有没有其他好的方法。
都可以贴出来，
请大家赐教了。

[billpu]

一般来说 游标的效率不高

[中国风]

IF(OBJECT_ID('Student') IS NOT null)
  DROP TABLE student
CREATE TABLE Student(
    ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    NAME VARCHAR(20),
    Age int    
)
INSERT INTO Student
SELECT 'zhangsan',12 UNION 
SELECT 'lisi',13 UNION 
SELECT 'wangwu',14 UNION 
SELECT 'zhaoliu',15 UNION 
SELECT 'wangba',16 UNION 
SELECT 'tianqi',17   

SELECT *,ID AS [@sindex],(SELECT COUNT(1) FROM  Student WHERE ID>=a.ID) AS [@scount]
FROM Student AS a
	ORDER BY ID
/*
ID          NAME                 Age         @sindex     @scount
----------- -------------------- ----------- ----------- -----------
1           lisi                 13          1           6
2           tianqi               17          2           5
3           wangba               16          3           4
4           wangwu               14          4           3
5           zhangsan             12          5           2
6           zhaoliu              15          6           1

(6 個資料列受到影響)


*/

這樣?

[振乾]

[Quote=引用 2 楼 roy_88 的回复:]
SQL code
IF(OBJECT_ID('Student') IS NOT null)
DROP TABLE student
CREATE TABLE Student(
ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
NAME VARCHAR(20),
Age int
)
INSERT INTO Stud……
[/Quote]

不好意思
朋友 你稍微有点误解我的意思了，
我只是 想 遍历某个表，取其中的 字段 进行利用，
不是 为 这个表进行排序。

[jinjazz]

sql的优势是批量操作,一般来说游标都是可以抛弃的.你可以考虑自定义函数
在sql2005之后有了apply,xml这些特性语法可以把大部分需要遍历表的操作替换为批量执行

[中国风]

方法2 明顯 效率高於 方法1

方法1，多了每次查看當前的記錄賦值+生成臨時表過程

樓主可這樣用

 SET NOCOUNT ON ;

 DECLARE @sindex INT,@scount INT,@sname VARCHAR(20),@sage int
 SELECT @sindex = MIN(a.ID) FROM Student AS a

 WHILE @sindex IS NOT null
    BEGIN
        SELECT @sname =a.name,@sage=a.age FROM Student AS a WHERE a.ID=@sindex
        
        select '方法一:'+@sname+'：'+STR(@sage)        
        
		SET @sindex=(SELECT  MIN(a.ID) FROM Student AS a WHERE ID>@sindex)

    END   

--在ID列加入聚集索引