存储过程问题

[lgzly]

我想写一个存储过程:查询某个表中第5行到第N行的记录,但表中没有ID这么一个字段

[achongsky]

主键有吗?
select * from table where 主键 not in (select top 5 主键 from table)

[Eric1006]

--创建测试数据表
CREATE TABLE TB_TEST
(
FCOUNT INT NOT NULL
);

--初始化数据
DECLARE @I INT
SET @I=1
WHILE(@I<500)
BEGIN
INSERT INTO TB_TEST VALUES (@I)
SET @I=@I+1
END

--获取从第5至第20记录
SELECT FCOUNT,IDENTITY(INT,1,1) AS FID INTO #TB_TEST FROM TB_TEST

SELECT TOP 16 FCOUNT FROM #TB_TEST WHERE FID IN (SELECT TOP 20 FID FROM #TB_TEST ORDER BY FID ASC) ORDER BY FID DESC

DROP TABLE #TB_TEST

[enterala]

用CURSOR是正道:P

[wisdomone]

先对你的表某一属性建一个索引，
然后用
ALTER TABLE TABLENAME ADD INDEX IX ON（FIELD）
SELECT TOP N-5 *
FROM TABLENAME AS A
WHERE FIELD NOT IN
（SELECT TOP 4 FIELD
FROM TABLENAME
WHERE FIELD=A.FIELD）
ORDER BY FIELD

[guan0616]

借下楼上的数据
CREATE TABLE TB_TEST
(
FCOUNT INT NOT NULL
);

--初始化数据
DECLARE @I INT
SET @I=1
WHILE(@I<500)
BEGIN
INSERT INTO TB_TEST VALUES (@I)
SET @I=@I+1
END
select * from tb_test

create proc n_5

@a int
as
select identity(int,1,1) a, tb_test.* into #t from tb_test
select * from #T where a between 5 and @a
select * from #t

exec n_5 20

[fxf66]

可以定义一个游标，定义一个计数器
打开游标，每fetch一次,计数器加1
到了你想要更新的记录时, update table set ... where current of 游标名字

如果你要从5万条数据之后才开始更新,那效率就...哈哈