如何用SQL删除用户项目状态变更后的旧记录？

Got it, let's work through how to clean up your table so only the latest status record remains for each (userId, projectId) pair. Here are a few reliable methods depending on your database system:

1. Generic Subquery Approach (Works Across Most Databases) #

This method uses a subquery to identify the most recent date for each user-project combination, then deletes any rows that don't match those latest entries:

DELETE FROM your_table
WHERE (userId, projectId, date) NOT IN (
    SELECT userId, projectId, MAX(date)
    FROM your_table
    GROUP BY userId, projectId
);

How it works: #

• The inner SELECT groups rows by userId and projectId, grabbing the latest date for each group.
• The outer DELETE removes all rows that aren't part of those latest (userId, projectId, date) tuples. This will specifically target and delete the older statusId=1 entry for userId=1, projectId=1 while keeping the newer one.

2. Window Function Method (For Modern Databases: PostgreSQL, MySQL 8+, SQL Server) #

If your database supports window functions, using ROW_NUMBER() gives you more control (like handling ties if multiple records share the latest date):

WITH ranked_records AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY userId, projectId 
            ORDER BY date DESC
        ) AS row_rank
    FROM your_table
)
DELETE FROM ranked_records
WHERE row_rank > 1;

How it works: #

• PARTITION BY userId, projectId splits the data into groups for each user-project pair.
• ORDER BY date DESC sorts each group so the newest record gets row_rank = 1.
• We delete all rows where row_rank > 1—these are the older, outdated status entries.

Note: If you want to keep multiple records that share the exact latest date (instead of just one), replace ROW_NUMBER() with RANK()—this will assign row_rank = 1 to all tied latest entries.

3. MySQL-Specific DELETE JOIN #

For older MySQL versions that don't support CTEs, you can use a self-join to target older rows:

DELETE t1
FROM your_table t1
JOIN your_table t2
    ON t1.userId = t2.userId 
    AND t1.projectId = t2.projectId
    AND t1.date < t2.date;

How it works: #

• We join the table to itself (t1 and t2) on matching userId and projectId.
• The condition t1.date < t2.date identifies rows in t1 that are older than another record for the same user-project pair. We delete those older t1 rows.

All these methods will leave you with the desired final dataset:

userId, projectId, statusId, date
1 1 2 2020-05-28
2 5 1 2020-06-01
3 7 2 2020-05-17

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