mysql中count(),group by使用

標籤：

count()統計表中或數組中記錄

count(*)返回檢索行的數目,且不論其值中是否包含NULL

count(column_name)返回的是對列中column_name不為NULL的行的統計

例如,查詢某活動的某個菜譜的使用者評論數:

SELECT COUNT(id) FROM uchome_comment WHERE id=530787 AND idtype=‘paiid‘

 現在想要查詢每個使用者評論的次數,並且按照評論次數倒序顯示:

SELECT COUNT(authorid) AS c, authorid,author FROM uchome_comment WHERE id=530787 AND idtype=‘paiid‘ GROUP BY authorid ORDER BY c DESC

當然也可以查詢評論次數大於5的:

#在group by 後面使用having子句可以限定分組的條件
SELECT COUNT(authorid) AS c, authorid,author FROM uchome_comment WHERE id=530787 AND idtype=‘paiid‘ GROUP BY authorid HAVING c > 5 ORDER BY c DESC

17:20:54持續更新

mysql中count(),group by使用