按日期返回一组连续记录的最小开始日期、最大结束日期和最新类别

SELECT 
    category,
    MIN(date) AS min_start_date,
    MAX(date) AS max_end_date,
    (
        SELECT category 
        FROM records r2 
        WHERE r2.date = (
            SELECT MAX(date) 
            FROM records r3 
            WHERE r3.category = r1.category
        )
    ) AS latest_category
FROM 
    records r1
GROUP BY 
    category;

SELECT 
    category,
    MIN(date) AS min_start_date,
    MAX(date) AS max_end_date,
    (
        SELECT DISTINCT category 
        FROM records r2 
        WHERE r2.date = (
            SELECT MAX(date) 
            FROM records r3 
            WHERE r3.category = r1.category
        )
    ) AS latest_category
FROM 
    records r1
GROUP BY 
    category;