求助：使用WHILE循环读取行值并实现特定状态输出的存储过程

Hey there! Let's work through this problem step by step. First, we'll break down why your current WHILE loop is stuck in an infinite loop, then jump to a cleaner, more efficient solution using SQL window functions (since loops are rarely the best fit for this kind of data grouping task).

What's Causing the Infinite Loop? #

Your existing code has two critical flaws that lead to the endless loop:

• SET @rc = @@ROWCOUNT runs right after declaring the variable. At this point, @@ROWCOUNT equals 0 (the DECLARE statement doesn't affect row counts), so your loop condition WHILE (@rc <= 1) will always evaluate to true—creating an infinite loop.
• The logic inside the loop doesn't address your actual requirement: it just queries all rows with Status='A' and returns MAX(Status) (which is just 'A'), with no handling of continuous groups of the same status.

The Better Approach: Window Functions for Consecutive Groups #

Instead of forcing a WHILE loop, we can use SQL window functions to identify groups of consecutive identical Status values, then filter each group to get the exact row you need (first A in a sequence, last B in a sequence). Here's how to do it:

Step 1: Define Sample Data #

First, let's formalize your sample table for clarity:

CREATE TABLE #SampleData (
    ID INT,
    [Time] DATETIME,
    Status VARCHAR(1)
);

INSERT INTO #SampleData VALUES
(12, '2018-05-04 08:00:00', 'A'),
(12, '2018-05-04 09:00:00', 'A'),
(12, '2018-05-04 11:00:00', 'B'),
(12, '2018-05-04 13:00:00', 'A'),
(12, '2018-05-04 15:00:00', 'B'),
(12, '2018-05-04 18:00:00', 'B');

Step 2: Query to Get Desired Output #

This query uses LAG() to detect when the Status changes (to create groups of consecutive identical values), then picks the right row for each group:

WITH StatusGroups AS (
    SELECT 
        ID,
        [Time],
        Status,
        -- Create a unique group ID for each consecutive set of the same Status
        SUM(CASE WHEN LAG(Status) OVER (ORDER BY [Time]) != Status THEN 1 ELSE 0 END) 
            OVER (ORDER BY [Time]) AS GroupID
    FROM #SampleData
),
GroupedTargets AS (
    SELECT
        ID,
        [Time],
        Status,
        GroupID,
        -- For A groups, get the earliest time; for B groups, get the latest time
        CASE 
            WHEN Status = 'A' THEN MIN([Time]) OVER (PARTITION BY GroupID)
            WHEN Status = 'B' THEN MAX([Time]) OVER (PARTITION BY GroupID)
        END AS TargetTime
    FROM StatusGroups
)
-- Filter to only keep rows that match the target time for their group
SELECT ID, [Time], Status
FROM GroupedTargets
WHERE [Time] = TargetTime
ORDER BY [Time];

Step 3: Verify Results #

Running this query will give you exactly your desired output:

ID  Time                    Status
12  2018-05-04 08:00:00.000 A
12  2018-05-04 11:00:00.000 B
12  2018-05-04 13:00:00.000 A
12  2018-05-04 18:00:00.000 B

Why This Is Better Than a Loop #

• Efficiency: Window functions are optimized for row-by-row comparisons and grouping—loops in SQL are slow for large datasets because they process rows one at a time.
• Maintainability: The logic is declarative (you describe what you want, not how to loop through it), making it easier to read and modify later.

If you absolutely need to use a WHILE loop (though I strongly advise against it), you'd need to track the previous Status, iterate through rows in time order, and collect the desired rows manually—but this is far more complex and error-prone than the window function approach.

内容的提问来源于stack exchange，提问作者Mina