MySQL 分组数据 GROUP BY的使用

﹏ヽ暗。殇╰゛Y 2022-01-26 16:19 272阅读 0赞

用来将返回进行分组

*  **GROUP BY**  
    通俗的说就是对SELECT 出来的结果进行分组，不会出现相同的，相同的会进行合并

SELECT vend FROM products GROUP BY vend

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzZGZ5bQ_size_16_color_FFFFFF_t_70]

*  **GROUP BY 与 COUNT(\*)**  
    GROUP BY 与 COUNT(\*)一起用来统计vend一样的共有多少条记录

SELECT vend,COUNT(*) AS num_prods 
    FROM products 
    GROUP BY vend

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzZGZ5bQ_size_16_color_FFFFFF_t_70 1]

*  **HAVIHG**  
    HAVIHG与GROUP BY连用，HAVIHG与WHERE相似，区别就是WHERE过滤行，而HAVIHG过滤组  
    过滤掉COUNT(\*)小于2的分组

SELECT vend,COUNT(*) AS num_prods 
    FROM products 
    GROUP BY vend
    HAVING COUNT(*) >=2

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzZGZ5bQ_size_16_color_FFFFFF_t_70 2]  
可以先进行where行过滤再进行分组，再进行组过滤

SELECT vend,COUNT(*) AS num_prods 
    FROM products 
    where price >=5
    GROUP BY vend
    HAVING COUNT(*) >=2

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzZGZ5bQ_size_16_color_FFFFFF_t_70 3]  
分组和顺序，永远不要相信默认的顺序，不要忘记用order by  
先对num\_prods进行排序，再对vend排序

SELECT vend,COUNT(*) AS num_prods 
    FROM products 
    GROUP BY vend
    HAVING COUNT(*) >=2
    ORDER BY num_prods,vend

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzZGZ5bQ_size_16_color_FFFFFF_t_70 4]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzZGZ5bQ_size_16_color_FFFFFF_t_70]: /images/20220126/6c8ed1a64fe54a2fad1c79fb2568f5cd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzZGZ5bQ_size_16_color_FFFFFF_t_70 1]: /images/20220126/2986f7ec176242cf8af051c9988fd949.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzZGZ5bQ_size_16_color_FFFFFF_t_70 2]: /images/20220126/ecbf6d85531f49cb9ab27b8c443b184e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzZGZ5bQ_size_16_color_FFFFFF_t_70 3]: /images/20220126/73fbd640917c49498b682d5e70179775.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzZGZ5bQ_size_16_color_FFFFFF_t_70 4]: /images/20220126/8cddbd0944894d4da7ce1a7ad0b74de6.png