SQL 必知必会第十课 分组数据

SQL 必知必会第十课 分组数据

select count(*) as num_prods from Products where vend_id = 'DLL01';

## 创建分组
select vend_id,count(*) as num_prods from products group by vend_id;

## 过滤分组
select cust_id,count(*) from orders group by cust_id having count(*) >=2 ;
select vend_id, count(*) as num_prods from products where prod_price >= 4 group by vend_id having count(*) >= 2;
select vend_id, count(*) as num_prods from products group by vend_id having count(*) >=2;

select order_num, count(*) as items from orderitems group by order_num having count(*) >=3; 
select order_num, count(*) as items from orderitems group by order_num having count(*) >=3 order by items, order_num;

## challenges
select order_num, count(*) from orderitems group by order_num order by count(*); -- 1

select prod_name, prod_price as cheapest_items from products order by prod_price; -- 2

select order_num, quantity from orderitems having quantity >=100; -- 3

select order_num, quantity * item_price as sum_price from orderitems having sum_price >=1000 order by sum_price; -- 4

注意点

1. where 在数据分组前进行过滤，having 在数据分组后进行过滤

2. select 子句的顺序
   

group by 函数规定
2. 它可以包含任意数目的列，也可以对分组进行嵌套，更细致的分组。

3. 若group by 子句中嵌套了分组，数据将在最后指定的分组上进行汇总。

4. select 和 group by句子中的表达式要相同

5. null 也会被当作一组

6. 除聚集语句之外， selelct中每一列都要在group by 字句中给出

7. group by 要在where 子句后，order by 子句前