MySQL 分组数据 GROUP BY的使用

用来将返回进行分组

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

SELECT vend FROM products GROUP BY vend

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

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

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

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

可以先进行where行过滤再进行分组，再进行组过滤

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

分组和顺序，永远不要相信默认的顺序，不要忘记用order by
先对num_prods进行排序，再对vend排序

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