求助：生成SQL代码获取同一policyid下首个记录之后的第二个IncurredAmount值

It looks like your current approach with GROUP BY isn’t the right fit here—GROUP BY is designed for aggregating data (like sums or counts) rather than ordering and selecting specific rows per group. Let’s fix this with window functions, which are perfect for this kind of per-group row selection task.

The Modern SQL Approach: Window Functions #

Most modern databases (MySQL 8+, PostgreSQL, SQL Server, etc.) support ROW_NUMBER(), a window function that assigns a unique sequential number to each row within a group. Here’s how to use it to get the second record per policyid:

SELECT claimid, policyid, IncurredAmount
FROM (
    SELECT 
        claimid,
        policyid,
        IncurredAmount,
        -- Assign row numbers per policyid, ordered to define "first/second" records
        ROW_NUMBER() OVER (
            PARTITION BY policyid 
            ORDER BY claimid  -- Replace with your actual ordering field (e.g., created_date)
        ) AS row_num
    FROM claims
) AS ranked_claims
-- Filter to only keep the second row in each policyid group
WHERE row_num = 2;

Key Details to Understand: #

• PARTITION BY policyid: Splits the entire dataset into groups where each group contains rows with the same policyid.
• ORDER BY claimid: This defines the sequence of rows within each group. If your "first" record isn’t based on claimid, swap this with a field like created_date (a timestamp for when the claim was added) to ensure you’re getting the correct chronological order.
• The outer query simply filters for rows where row_num = 2—this gives you the second record for every policyid.

For Older Databases (e.g., MySQL < 8.0) #

If you’re using a database version that doesn’t support window functions, you can use user-defined variables to mimic the row numbering behavior:

SELECT claimid, policyid, IncurredAmount
FROM (
    SELECT 
        claimid,
        policyid,
        IncurredAmount,
        -- Reset row number when policyid changes, increment otherwise
        @row_num := IF(@current_policy = policyid, @row_num + 1, 1) AS row_num,
        @current_policy := policyid
    FROM claims,
         -- Initialize variables for row tracking
         (SELECT @current_policy := 0, @row_num := 0) AS vars
    -- Order to ensure consistent grouping and sequencing
    ORDER BY policyid, claimid
) AS ranked_claims
WHERE row_num = 2;

Note on Your Original Query #

Your original GROUP BY and HAVING clauses aren’t necessary for this task. GROUP BY would collapse rows with identical values (which isn’t what you want when selecting individual records), and HAVING is meant to filter aggregated results. If you ever need to test with a single policyid (like 62), just add a WHERE policyid = 62 clause inside the subquery instead.

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