代码之家 › 专栏 › 技术社区 › Ramji

while循环-SQL逻辑

while-loop sql-server-2008 sql

Ramji · 技术社区 · 8 年前

输入

 date        |  username |    program | workflow  | Pending_Audits  |Audits_Carry_Forward

 31-07-2018  |  ram      |   Pre pay  | Sisi      |      -1         |      -12 
 27-07-2018  |  ram      |   Pre pay  | Sisi      |      -111       |       0
 25-07-2018  |  ram      |   Pre pay  | Sisi      |      -16        |     -14

我有上表,我正在写一段时间的条件,从给定的日期更新审计结转栏。

逻辑是更新给定日期的审核结转到下一行“待定审核”列,该列具有与给定日期相同的程序和工作流。

期望输出:

 date        |  username |    program | workflow  | Pending_Audits  |Audits_Carry_Forward

 31-07-2018  |  ram      |   Pre pay  | Sisi      |      -1         |      -111 
 27-07-2018  |  ram      |   Pre pay  | Sisi      |      -111       |      -16
 25-07-2018  |  ram      |   Pre pay  | Sisi      |      -16        |      -14

因此,在这种情况下,我想将“2018年7月25日”的未决审计数据更新为高于给定日期的审计结转数据。

我尝试的SQL逻辑

DECLARE @date DATE = '2018-07-25' 
DECLARE @username VARCHAR(100)= 'ram' 
DECLARE @program VARCHAR(100)= 'Pre pay' 
DECLARE @workflow VARCHAR(100)= 'Sisi' 
DECLARE @a INT 

WHILE ( @date <= Cast(Getdate() AS DATE) ) 
  BEGIN 
      SET @a = (SELECT Count(*) 
                FROM   dbo.homehealthpp1 
                WHERE  username = @username 
                       AND program = @program 
                       AND workflow = @workflow 
                       AND date = @date) 

      IF ( @a = 1 ) 
        BEGIN 
            UPDATE dbo.homehealthpp1 
            SET    audits_carry_forward = (SELECT pending_audits 
                                           FROM   dbo.homehealthpp1 
                                           WHERE  username = @username 
                                                  AND program = @program 
                                                  AND workflow = @workflow 
                                                  AND date = @date) 
            WHERE  username = @username 
                   AND program = @program 
                   AND workflow = @workflow 
                   AND date = Cast(Dateadd(d, 1, @date) AS DATE) 
        END 

      SET @date = Cast(Dateadd(d, 1, @date) AS DATE) 
  END

上述情况在日期按顺序出现时有效。但它不适用于日期不是按顺序排列的场景,如上面的情况。

1 回复 | 直到 8 年前

Dave C 8 年前

假设 username/workflow/program 是共同的价值观,你可以这样做。

我们添加了 row_number() 分区 用户名/工作流/程序 然后我们可以更新 audits_carry_forward 匹配的值 用户名/工作流/程序 从下面的行( rn )在集合中(-1),但仅当行( 氡 不是1。

DECLARE @Test TABLE ([date] DATE, username VARCHAR(100), program VARCHAR(10), workflow VARCHAR(10), pending_audits INT, audits_carry_forward INT)

INSERT INTO @Test
VALUES ('2018-07-31','ram','Pre pay','Sisi',-1,-12),
       ('2018-07-27','ram','Pre pay','Sisi',-111,0),
       ('2018-07-25','ram','Pre pay','Sisi',-16,-14)

;WITH x AS
(
    SELECT *, ROW_NUMBER() OVER(PARTITION BY username,program,workflow ORDER BY [date]) AS RN
    FROM @Test
)

UPDATE T1 SET audits_carry_forward = (SELECT pending_audits FROM X T2 WHERE T1.username=T2.username AND T1.program=T2.program AND T1.workflow=T2.workflow AND T2.RN=T1.RN-1)
FROM X T1
WHERE T1.RN!=1

SELECT *
FROM @Test
ORDER BY [date] DESC

值得注意的是,在SQL中不建议使用循环/游标。这被称为RBAR(row by agonizing row)方法,它比基于集合的方法更昂贵,而所有数据都是同时更新的。随着时间和经验的推移,您会发现大多数(尽管不是全部)事情都可以在不使用循环/光标的情况下完成。