tsql - 如何在 SQL 中使用循环？

Question

在 TSQL 中，我想将以下代码从必须使用硬编码 dhome 更改为使用循环进行优化。我尝试添加循环失败的尝试也包括在内。

Declare  @dhome Tinyint, @bp smallint, @lr smallint, @q smallint

    // Set @dhome = 1
  While(@dhome <= 3) // My attempt to add a loop

  SELECT @lr = MAX(NQdDate), @q = NQd
  FROM NQdHistory
  WHERE dhomeId = @dhome 
  GROUP BY NQdDate, NQd

  SELECT @bd = COUNT(*)
  FROM bdhome
  WHERE NQdDate= @lr AND dhomeID= @dhome 

  DELETE FROM ND1 WITH(XLOCK)
  WHERE dhomeID= @dhome  AND NQdDate= @lr

  UPDATE NQdHistory
  SET Nbd = @q - @@RowCount - @bp, NBd = @bp
  WHERE NQdDate= @lr AND dhomeID= @dhome 

  Set @dhome = @dhome +1 //My attempt to end a loop

Answer 1

你走在正确的道路上。你错过了开始和结束。另外，请务必为 @dhome 指定一个值。看来您开始在第三行注释掉它:

Declare  @dhome Tinyint, @bp smallint, @lr smallint, @q smallint

    // Set @dhome = 1
While(@dhome <= 3) // My attempt to add a loop
begin
  SELECT @lr = MAX(NQdDate), @q = NQd
  FROM NQdHistory
  WHERE dhomeId = @dhome 
  GROUP BY NQdDate, NQd

  SELECT @bd = COUNT(*)
  FROM bdhome
  WHERE NQdDate= @lr AND dhomeID= @dhome 

  DELETE FROM ND1 WITH(XLOCK)
  WHERE dhomeID= @dhome  AND NQdDate= @lr

  UPDATE NQdHistory
  SET Nbd = @q - @@RowCount - @bp, NBd = @bp
  WHERE NQdDate= @lr AND dhomeID= @dhome 

  Set @dhome = @dhome +1 //My attempt to end a loop
end  

如果您熟悉 C/C#/C++，可以将 T-SQL 的 Begin 和 End 想象为大括号 { 和 }(如果您更熟悉)使用 VB Then 和 End If。或者更像 pascals Begin 和 End。你明白了:)