MySQL 多表分组后获取每一组的时间最大的那条记录

待我称王封你为后i 2022-12-18 11:53 218阅读 0赞

*  **一、问题**

**         GROUP BY分组后取最新时间的一条记录的SQL的解决方案。**

*  **二、解决方案**

>  *  **首先对资源表数据进行排序（多表）**

SELECT 
    		a1.*,
            a2.a2id
    FROM 
    		table1 a1 
    LEFT JOIN table2 a2
    ON a1.a1id = a2.a2id 
    WHERE a1.time 
    ORDER BY a1.time desc

>  *  **然后组合以上SQL生成的临时表进行嵌套查询（多表）**

SELECT 
    	w.*
    FROM 
    (
        SELECT 
    		a1.*,
            a2.a2id
        FROM 
    		table1 a1 
        LEFT JOIN table2 a2
        ON a1.a1id = a2.a2id 
        WHERE a1.a1time 
        ORDER BY a1.a1time desc
    ) w
    GROUP BY w.a1id

> **另外在查询时，[有一篇文章][Link 1]内在临时表查询时加入了having 1作为条件，经测试，取消后得到的结果是相同的，各位可以在使用时自定义测试**

SELECT 
    	w.*
    FROM 
    (
        SELECT 
    		a1.*,
            a2.a2id
        FROM 
    		table1 a1 
        LEFT JOIN table2 a2
        ON a1.a1id = a2.a2id 
        WHERE a1.a1time 
        HAVING 1
        ORDER BY a1.a1time desc
    ) w
    GROUP BY w.a1id

**临时表内的a2.id可替换为a2.\*作为临时表w的多数据条件**

--------------------

![format_png][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTcwNjU4_size_16_color_FFFFFF_t_70][]

[Link 1]: https://blog.csdn.net/qq_36387683/article/details/107321088?utm_medium=distribute.pc_relevant.none-task-blog-title-1&spm=1001.2101.3001.4242
[format_png]: /images/20221122/c525a16390ec4b51a2e4eab3159e1112.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTcwNjU4_size_16_color_FFFFFF_t_70]: /images/20221122/58926eb0e6654cd6945a235fdb2e9cd4.png