标准SQL-无法在主循环上循环

Question

在bigquery中使用嵌套的LOOP或WHILE循环时，我似乎无法在外部循环上再次迭代。使用以下代码可以重现此问题。

DECLARE i int64 DEFAULT 0;
DECLARE j int64 DEFAULT 0;
DECLARE k int64 DEFAULT 0;
WHILE i < 3 DO
SET i = i + 1;
WHILE j < 2 DO
SET j = j + 1;
IF j = 2 THEN
SET k = k+7;
END IF;
EXECUTE IMMEDIATE  """
WITH test AS(SELECT @i2 AS i, @j2 AS j, @k2 AS k)
SELECT * FROM test
"""
USING i AS i2, j AS j2, k AS k2;
END WHILE;
END WHILE;

作为输出，Bigquery给了我两次迭代(内部循环(：

行	i	j	k
1	1	1	0

我希望，当我们结束内部while循环时，我们会转到外部while循环，并从开始

它实际上和你预期的一样执行-检查一下-在下面用额外的行运行，这样你就会看到的证明

DECLARE i int64 DEFAULT 0;
DECLARE j int64 DEFAULT 0;
DECLARE k int64 DEFAULT 0;
WHILE i < 3 DO
SET i = i + 1;
SELECT i; # insert this line to check correctness
WHILE j < 2 DO
SET j = j + 1;
IF j = 2 THEN
SET k = k+7;
END IF;
EXECUTE IMMEDIATE  """
WITH test AS(SELECT @i2 AS i, @j2 AS j, @k2 AS k)
SELECT * FROM test
"""
USING i AS i2, j AS j2, k AS k2;
END WHILE;
END WHILE;  

因此，显然对于i = 2-WHILE j < 2 DO被评估为假，因此跳过

这样做的正确方法是什么？

这取决于您试图实现的目标，但通常这是通过在第一个循环内重置j来完成的，如下面的示例所示

DECLARE i int64 DEFAULT 0;
DECLARE j int64 DEFAULT 0;
DECLARE k int64 DEFAULT 0;
WHILE i < 3 DO
SET i = i + 1;
SET j = 0; # reset j
WHILE j < 2 DO
SET j = j + 1;
IF j = 2 THEN
SET k = k+7;
END IF;
EXECUTE IMMEDIATE  """
WITH test AS(SELECT @i2 AS i, @j2 AS j, @k2 AS k)
SELECT * FROM test
"""
USING i AS i2, j AS j2, k AS k2;
END WHILE;
END WHILE;