MySQL--分組資料

標籤：順序   use   技術   現在   ble   層級   串連資料庫   ima   count   

1、資料分組

1 #串連資料庫2 use newschema;3 #查看錶中資料4 select *from products；5 #返回供應商1003提供的產品數目6 select count(*) as num_prods from products where vend_id=1003;

2、建立分組

select vend_id,count(*) as num_prods from products group by vend_id;

**Group By 子句必須出現在where自居之後，order by 子句之前。

#使用with rollupselect vend_id,count(*) as num_prods from products group by vend_id with rollup;#使用with rollup關鍵字，可以得到每個分組以及每個分組匯總層級（針對每個分組）的值。

 

3、過濾分組

所有類型的where子句都可以用having來替代。唯一差別師where過濾行，而having過濾分組。

select cust_id,count(*) as orders from orders group by cust_id having count(*)>=2;

having和where的差別：where在資料分組前進行過濾，having在資料分組後進行過濾。

select vend_id,count(*) as num_prods from products where prod_price>=10 group by vend_id having count(*)>=2;

分析：where子句過濾所有prod_price至少為10 的行，然後按照cend_id 分組，having子句過濾計數為2或2以上的分組。

 

select vend_id,count(*) as num_prods from products group by vend_id having count(*)>=2;

 4、分組和排序

select order_num,sum(quantity*item_price) as ordertotal from orderitems group by order_num having sum(quantity*item_price)>=50;

 

select order_num,sum(quantity*item_price) as ordertotal from orderitems group by order_num having sum(quantity*item_price)>=50 order by ordertotal;

 

5、select子句順序

下列表是在使用select語句時必須遵循的次序

select

from

where

group by

having

order by

limit

 

MySQL--分組資料