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