group by
语句:SELECT vend_id, COUNT(*) AS num_prods FROM Products GROUP BY vend_id;
输出结果:
vend_id num_prods
BRS01 3
DLL01 4
FNG01 2
可以看到上述语句中,vend_id是表中现有的字段,num_prods为计算字段(用COUNT(*)函数建立)。GROUP BY子句实现按vend_id排序并按照每个vend_id分组数据。这就会对每个vend_id而不是整个表计算num_prods一次。从输出中可以看到,BRS01有3个,DLL01有4个,FNG01有2个。
因为使用了GROUP BY,就不必指定要计算和估值的每个组了。系统会自动完成。GROUP BY子句分组数据,然后对每个组而不是整个结果集进行聚集。
使用分组可以将数据分为多个逻辑组,对每个组进行聚集计算。
注:
- GROUP BY子句可以包含任意数目的列,因而可以对分组进行嵌套,更细致地进行数据分组。
- 如果在GROUPBY子句中嵌套了分组,数据将在最后指定的分组上进行汇总。换句话说,在建立分组时,指定的所有列都一起计算(所以不 能从个别的列取回数据)。
- GROUP BY子句中列出的每一列都必须是检索列或有效的表达式(但不能是聚集函数)。如果在SELECT中使用表达式,则必须在GROUP BY子 句中指定相同的表达式。不能使用别名。
- 大多数SQL实现不允许GROUP BY列带有长度可变的数据类型(如文本或备注型字段)。
- 除聚集计算语句外,SELECT语句中的每一列都必须在GROUP BY子句中给出。(这句话我真没懂,后面再研究一下
) - 如果分组列中包含具有NULL值的行,则NULL将作为一个分组返回。如果列中有多行NULL值,它们将分为一组。
- GROUP BY子句必须出现在WHERE子句之后,ORDER BY子句之前。
group by分组后的每组内部数据的排序:
“GROUP BY pcInfoId DESC” 是在分组后组内进行排序
“GROUP BY pcInfoId ORDER BY TIME DESC”是在分组后对产生的结果进行排序,是组外的
having过滤
语句:SELECT cust_id, COUNT() AS orders FROM Orders
GROUP BY cust_id HAVING COUNT() >= 2;
输出结果:
cust_id orders
1000000001 2
在这个例子中是不能使用WHERE的,因为WHERE过滤指定的是行而不是分组,它是作用在表中的原始行的。所以对非原始数据进行过滤,就要用到HAVING子句。HAVING非常类似于WHERE。
那什么时候可以用having,什么时候可以用where呢?(此处转自https://blog.csdn.net/shang_bo_liang/article/details/105927721)
下面以一个例子来具体的讲解:
- where和having都可以使用的场景
select goods_price,goods_name from sw_goods where goods_price > 100
select goods_price,goods_name from sw_goods having goods_price > 100
解释:上面的having可以用的前提是我已经筛选出了goods_price字段,在这种情况下和where的效果是等效的,但是如果我没有select goods_price 就会报错!!因为having是从前筛选的字段再筛选,而where是从数据表中的字段直接进行的筛选的。
- 只可以用where,不可以用having的情况
select goods_name,goods_number from sw_goods where goods_price > 100
select goods_name,goods_number from sw_goods having goods_price > 100 //报错!!!因为前面并没有筛选出goods_price 字段
- 只可以用having,不可以用where情况
查询每种goods_category_id商品的价格平均值,获取平均价格大于1000元的商品信息
select goods_category_id , avg(goods_price) as ag from sw_goods group by goods_category having ag > 1000
select goods_category_id , avg(goods_price) as ag from sw_goods where ag>1000 group by goods_category //报错!!因为from sw_goods 这张数据表里面没有ag这个字段
注意:where 后面要跟的是数据表里的字段,如果我把ag换成avg(goods_price)也是错误的!因为表里没有该字段。而having只是根据前面查询出来的是什么就可以后面接什么。
所以,where 子句的作用是在对查询结果进行分组前,将不符合where条件的行去掉,也就是在分组之前过滤数据,条件中不能包含聚和函数,使用where条件限制特定的行。
having 子句的作用是筛选满足条件的组,即在分组之后过滤数据,条件中经常包含聚合函数,使用having 条件过滤特定的组,也可以使用多个分组标准进行分组。
总之一条sql中有where having group by的时候,顺序是 where group by having。
select子句的顺序
子 句 | 说 明 | 是否必须使用 |
---|---|---|
SELECT | 要返回的列或表达式 | 是 |
FROM | 从中检索数据的表 | 仅在从表选择数据时使用 |
WHERE | 行级过滤 | 否 |
GROUP BY | 分组说明 | 仅在按组计算聚集时使用 |
HAVING | 组级过滤 | 否 |
ORDER BY | 输出排序顺序 | 否 |