通常业务系统不推荐使用游标,但是又要实现游标逐行处理功能,在mssql中实现方法如下:
1.导入临时表,建立行ID逐行搜索或者每次TOP(1)查找
2.导入表变量,建立行ID逐行搜索
至于临时表和表变量主要区别在于大数量搜索时的查找时间长短,临时表是存贮在磁盘,表变量是在内存
临时表导入数据时需要两次IO读写,表变量只有一次,临时表可以建立索引,表变量不能,所以在大数据复杂操作时,临时表利用索引查找的速度能提供不少
实现脚本如下:
--准备数据 IF OBJECT_ID('tb_a') IS NOT NULL DROP TABLE tb_a CREATE TABLE tb_a ( ph VARCHAR(20), tdate VARCHAR(6), qty NUMERIC(18,0) ) INSERT INTO tb_a select 'aaaaaaaaa','201101','100' union all select 'aaaaaaaaa','201102','200' union all select 'aaaaaaaaa','201103','0' union all select 'aaaaaaaaa','201104','100' union all select 'bbbbbbb','201101','100' union all select 'bbbbbbb','201102','0' union all select 'bbbbbbb','201103','200' union all select 'bbbbbbb','201104','100' union all select 'bbbbbbb','201105','400' SELECT * FROM tb_a GO --方法一:表变量 DECLARE @t TABLE ( ph VARCHAR(20), tdate VARCHAR(6), qty NUMERIC(18), rownum INT ) DECLARE @i INT,@rownum int INSERT INTO @t SELECT ph,tdate,qty,ROW_NUMBER()OVER(ORDER BY ph) FROM tb_a SELECT @rownum=@@ROWCOUNT,@i=1 WHILE @i<=@rownum BEGIN SELECT * FROM @t WHERE rownum=@i SET @i=@i+1 END --方法一:临时表 IF OBJECT_ID('tempdb..#tb_a') IS NOT NULL DROP TABLE #tb_a CREATE TABLE #tb_a ( ph VARCHAR(20), tdate VARCHAR(6), qty NUMERIC(18) ) INSERT INTO #tb_a SELECT * FROM tb_a --表变量与临时区别之一 CREATE NONCLUSTERED INDEX ix_ph ON #tb_a(ph) DECLARE @j INT SELECT @j=COUNT(1) FROM #tb_a WHILE @j>0 BEGIN SELECT TOP 1 * FROM #tb_a --逻辑处理... DELETE TOP(1) FROM #tb_a SET @j=@j-1 END