时间戳

首页 > 解决方案 > 为什么 SQL Continue 语句会创建一个 Endless 循环而不是 Continuing？

sql - 为什么 SQL Continue 语句会创建一个 Endless 循环而不是 Continuing？

问题描述

试图在一个简单的循环中使用 CONTINUE 语句。

该循环在没有 IF=16 和 CONTINUE 语句的情况下工作，但不适用于它们

 DECLARE @counter INT
 DECLARE @maxLocations INT
 DECLARE @numLocations INT

 SET @maxLocations = (SELECT MAX(calculated_host_listings_count) FROM [PracticeDB].[dbo].[AB_NYC_2019$]) 
 SET @counter = 0

WHILE @counter<=@maxLocations
    BEGIN
        SET @numLocations = 
            (SELECT COUNT(*) FROM [PracticeDB].[dbo].[AB_NYC_2019$] WHERE calculated_host_listings_count = @counter)

        IF(@numLocations=16)
        BEGIN
            CONTINUE
        END 

        ELSE
        BEGIN
            PRINT CAST(@numLocations AS VARCHAR(6)) + ' rentals with ' + 
                CAST(@counter as VARCHAR(6)) + ' host listings'
        END

        SET @counter += 1
    END

结果没有IF(@numlocations=16) 继续

0 rentals with 0 host listings
32303 rentals with 1 host listings
6658 rentals with 2 host listings
2853 rentals with 3 host listings
1440 rentals with 4 host listings
845 rentals with 5 host listings
570 rentals with 6 host listings
399 rentals with 7 host listings
416 rentals with 8 host listings
234 rentals with 9 host listings
210 rentals with 10 host listings
110 rentals with 11 host listings
180 rentals with 12 host listings
130 rentals with 13 host listings
70 rentals with 14 host listings
75 rentals with 15 host listings
16 rentals with 16 host listings
68 rentals with 17 host listings

IF & Continue 的结果（上面的原始代码）进入一个无限循环，但是当我停止时，我得到结果直到 IF 结果之前（16）

0 rentals with 0 host listings
32303 rentals with 1 host listings
6658 rentals with 2 host listings
2853 rentals with 3 host listings
1440 rentals with 4 host listings
845 rentals with 5 host listings
570 rentals with 6 host listings
399 rentals with 7 host listings
416 rentals with 8 host listings
234 rentals with 9 host listings
210 rentals with 10 host listings
110 rentals with 11 host listings
180 rentals with 12 host listings
130 rentals with 13 host listings
70 rentals with 14 host listings
75 rentals with 15 host listings

标签： sqlsql-servertsql

解决方案

在 SQL WHILE 循环中使用 CONTINUE 语句，以便在某些条件发生时停止循环的当前迭代，然后从循环的开头开始新的迭代 SQL WHILE 循环与简单示例

根据您的问题，您需要在 CONTINUE 语句之后或之前迭代计数器变量。恕我直言，您的查询将是这样的；

DECLARE @counter INT
 DECLARE @maxLocations INT
 DECLARE @numLocations INT

 SET @maxLocations = (SELECT MAX(calculated_host_listings_count) FROM [PracticeDB].[dbo].[AB_NYC_2019$]) 
 SET @counter = 0

WHILE @counter<=@maxLocations
    BEGIN
        SET @numLocations = 
            (SELECT COUNT(*) FROM [PracticeDB].[dbo].[AB_NYC_2019$] WHERE calculated_host_listings_count = @counter)

        IF(@numLocations=16)
        BEGIN
            SET @counter += 1 --Changed part
            CONTINUE
        END 

        ELSE
        BEGIN
            PRINT CAST(@numLocations AS VARCHAR(6)) + ' rentals with ' + 
                CAST(@counter as VARCHAR(6)) + ' host listings'
        END

        SET @counter += 1
    END

推荐阅读