如何在SQL中从指定起点（ID 33）开始提取各成员的记录

Alright, let's break down how to solve this problem. From your description, you need a view that, for each member, includes all their records starting from (and including) the one with ID=33—after sorting each member's entries by date. Here are a few practical solutions depending on your SQL database:

Using Window Functions (Modern SQL Databases: PostgreSQL, SQL Server, MySQL 8+, etc.) #

Window functions are the cleanest, most maintainable way to handle this. We’ll create a flag that switches to "included" once we hit the ID=33 record for a member, and stays that way for all subsequent entries.

CREATE VIEW member_records_post_33 AS
WITH member_flagged AS (
    SELECT
        *,
        -- This sum becomes 1 once we hit ID=33, and stays 1 for all later records in the member's group
        SUM(CASE WHEN ID = 33 THEN 1 ELSE 0 END) OVER (
            PARTITION BY Member
            ORDER BY Date
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS should_include
    FROM your_original_table
)
SELECT Member, ID, Date -- Add any other columns you need here
FROM member_flagged
WHERE should_include = 1;

How this works: #

• PARTITION BY Member groups all records by individual members, so we handle each one separately.
• ORDER BY Date ensures we process records in chronological order for each member.
• The SUM() window function starts at 0. When it hits a record with ID=33, it adds 1 to the running total—all records after that will keep this 1 value, so filtering for should_include = 1 gives exactly the records you want.

For Older MySQL Versions (No Window Function Support) #

If you’re stuck on MySQL 5.x (which doesn’t have window functions), a correlated subquery will get the job done. We first find the earliest date where a member has an ID=33 record, then select all of that member’s records from that date onward.

CREATE VIEW member_records_post_33 AS
SELECT t1.*
FROM your_original_table t1
INNER JOIN (
    -- Get the first date each member had an ID=33 record
    SELECT Member, MIN(Date) AS start_date
    FROM your_original_table
    WHERE ID = 33
    GROUP BY Member
) t2 ON t1.Member = t2.Member
WHERE t1.Date >= t2.start_date;

Note: #

• This assumes each member has at most one ID=33 record. If there are multiple, it uses the earliest date as the starting point.
• Members without any ID=33 records won’t appear in the view at all, which aligns with your requirement of starting at ID=33.

Alternative: Using Row Numbers #

Another window function approach is to assign row numbers to each member’s sorted records, then pick all rows from the row number of the ID=33 entry onward.

CREATE VIEW member_records_post_33 AS
WITH numbered_records AS (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY Member ORDER BY Date) AS row_num,
        -- Capture the row number of the ID=33 record for each member
        MAX(CASE WHEN ID = 33 THEN ROW_NUMBER() OVER (PARTITION BY Member ORDER BY Date) END) OVER (PARTITION BY Member) AS start_row
    FROM your_original_table
)
SELECT Member, ID, Date -- Add other columns as needed
FROM numbered_records
WHERE row_num >= start_row;

Edge Cases to Keep in Mind: #

• If a member has no ID=33 records, none of their entries will show up in the view (which is probably what you want).
• If a member has multiple ID=33 records, the first method (with the sum flag) will include all records from the first ID=33 onward, while the subquery method uses the earliest date. Pick the one that matches your exact needs.

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