第十课 分组数据创建分组过滤分组分组和排序

创建分组

代码语言：javascript复制

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

group by 语句的规定：

• 可以包含任意数目的列，因而可以对分组进行嵌套
• 必须出现在where语句之后，having语句之前 等等

过滤分组

过滤掉不符合条件的分组，使用having而不是where

** having和where的区别 **： ** where在数据分组前进行过滤，having在数据分组后进行过滤，where过滤的是行，having过滤的是分组 **

代码语言：javascript复制

select cust_id, count(*) as orders
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;

分组和排序

Paste_Image.png

代码语言：javascript复制

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

Paste_Image.png

having image paste png 排序

0 人点赞