Sql 游标处理报表分析
1. 有上万数据的表格如下截图所示
2. 其中数字1表示正常,其他数字为不正常。每一具有"name"属性的行如果一直正常,则该信息合理;如若每一"name"的属性在出现正常情况下
有不正常情况出现后又有正常情况出现(亦即正常与不正常现象交替出现),则只是从最开始持续是正常的情况为合理信息,其后出现的正常视不合理
信息。
3. 实现如下:
DECLARE dealInfo_cursor CURSOR FOR SELECT distinct banid,MIN(sn)OVER(PARTITION BY banid ) AS cc FROM tabBooks OPEN dealInfo_cursor DECLARE @banid VARCHAR(30),@cc FLOAT FETCH NEXT FROM dealInfo_cursor INTO @banid,@cc WHILE @@FETCH_STATUS=0 BEGIN DECLARE mm_cur CURSOR FOR SELECT * FROM tabBooks WHERE banid=@banid OPEN mm_cur DECLARE @sn FLOAT,@ID FLOAT,@deptSend VARCHAR(30),@deptReceive VARCHAR(50),@ifCancel VARCHAR(50) ,@banid2 VARCHAR(30),@mbtype VARCHAR(30),@scandate DATETIME,@isOk VARCHAR(30) FETCH NEXT FROM mm_cur INTO @sn,@id,@deptSend,@deptReceive,@ifCancel,@banid2,@mbtype,@scandate,@isOk WHILE(@@FETCH_STATUS=0) BEGIN SET @cc=@cc+1 IF( @sn=@cc-1) INSERT INTO tmp_tbBooks_b VALUES(@sn,@id,@deptSend,@deptReceive,@ifCancel,@banid2,@mbtype,@scandate,@isOk) FETCH NEXT FROM mm_cur INTO @sn,@id,@deptSend,@deptReceive,@ifCancel,@banid2,@mbtype,@scandate,@isOk END CLOSE mm_cur DEALLOCATE mm_cur FETCH NEXT FROM dealInfo_cursor INTO @banid,@cc END CLOSE dealInfo_cursor DEALLOCATE dealInfo_cursor