mysql 自我總結

group by  和  DISTINCT

          group by按組劃分，每組只顯示1條記錄

          distinct後接多個欄位，保證每個欄位組合的記錄唯一（distinct id,name from ）

groupby having 和 where 

         SELECT *,(SUM(win)/COUNT(*)) as present  FROM `xxtable`  GROUP BY hero HAVING present>0.1

SELECT *,(SUM(win)/COUNT(*)) as present  FROM `xxtable` where present>0.1 GROUP BY hero (語句錯誤，找不到present)

where 子句的作用是在對查詢結果進行分組前，將不符合where條件的行去掉，即在分組之前過濾資料，條件中不能包含聚組函數，使用where條件顯示特定的行

having用在彙總函式中，判斷彙總函式（sum，count，avg，）的值是否大於某個值

執行個體特訓：

1.英雄聯盟多玩魔盒

表

A:擷取勝率排名前2的英雄和對局

SELECT *,(SUM(win)/COUNT(*)) as present  FROM `aa` WHERE userid=1 GROUP BY hero ORDER BY present desc limit 2

B:擷取勝率大於0.5的英雄和對局

SELECT *,(SUM(win)/COUNT(*)) as present  FROM `aa` WHERE userid=1 GROUP BY hero HAVING present>0.5 ORDER BY present desc