mysql 統計函數和group by

五個統計函數（單獨使用，意義不大，經常和分組group by組合使用）
max 最大select max(shop_price) from goods;
min 最小select min(shop_price) from goods;
sum 求和select sum( shop_price) from goods;
avg 求平均值select avg(shop_price) from goods;
count 所有值得行數有多少行 
count（*）絕對行數null也計算在內
除此之外count（列名） ，計算這一列非null的行數

count使用 

mysql> select * from test8;
+------+------+
| id | name |
+------+------+
| 1 | lisi | 
| 2 | NULL | 
+------+------+
mysql> select count(*) from test8;
+----------+
| count(*) |
+----------+
| 2 | 
+----------+
mysql> select count(name) from test8;
+-------------+
| count(name) |
+-------------+
| 1 | 
+-------------+

查詢類型為4的庫存

select sum(goods_number) from goods where cat_id=4;

group by
統計一下每個類型分組下的庫存

mysql> select cat_id,sum(goods_number) from goods group by cat_id;
+--------+-------------------+
| cat_id | sum(goods_number) |
+--------+-------------------+
| 2 | 0 | 
| 3 | 203 | 
| 4 | 4 | 
| 5 | 8 | 
| 8 | 61 | 
| 11 | 23 | 
| 13 | 4 | 
| 14 | 9 | 
| 15 | 2 | 
+--------+-------------------+

group by

統計一下每個類型分組下的庫存
語上解釋不通（每個類別cat_id裡面有很多goods_name）
不推薦select goods_name ,sum(goods_number) from goods group by cat_id; 
解釋：在select a/b中必須在group by a/b/c語意上才沒有問題

技巧：查詢語句理解上從後面的條件過濾開始，先理解過濾條件，再看前面的執行

以上就是mysql 統計函數和group by的內容，更多相關內容請關注PHP中文網（www.php.cn）！

🎜