分组查询
分组查询是指使用 group by
字句对查询信息进行分组。
格式: SELECT 字段1,字段2… FROM 表名 GROUP BY分组字段 HAVING 分组条件;
分组操作中的 having
子语句,是用于在分组后对数据进行过滤的,作用类似于 where
条 件。
-
having与where的区别:
-
having是在分组操作执行后,对分组后的数据进行过滤.。
where是在分组操作执行前,对分组前表的原始列进行过滤
-
having后面可以使用聚合函数。where后面不可以使用聚合函数,只能用条件过滤。
-
-
当一条SQL语句中, 既有where 又有 group by \ having 时, 先执行 where, 再执行 group by, 最后执行having
# 查询商品表,返回供应商1003提供的产品数目
SELECT COUNT(*) AS prod_num
FROM products
WHERE vend_id = 1003;
# 如果我想查询每个供应商提供的产品数量呢?
SELECT vend_id, COUNT(*)
FROM products
GROUP BY vend_id;
# 查询每个供应商提供的产品数量,并且产品数量大于2的供应商id
SELECT vend_id, COUNT(*)
FROM products
GROUP BY vend_id
HAVING COUNT(*) > 2;
# 思考题:查询每个供应商提供的产品数目,
# 以及每个供应商提供的商品中最贵的价格;
# --提示,在同时查询多个聚合函数
SELECT vend_id, COUNT(*) AS '数量', MAX(prod_price) AS '最贵'
FROM products
GROUP BY vend_id;
# 思考题:在供应商id大于1001的供应商中,查询每个供应商提供的产品数目,
# 以及每个供应商提供的商品中最贵的价格;
SELECT vend_id, COUNT(*), MAX(prod_price)
FROM products
WHERE vend_id > 1001
GROUP BY vend_id;
# 思考题:在供应商id大于1001的供应商中,查询每个供应商提供的产品数目,
# 以及每个供应商提供的商品中最贵的价格,并且最高价格大于10的数据
SELECT vend_id, COUNT(*), MAX(prod_price)
FROM products
WHERE vend_id > 1001
GROUP BY vend_id
HAVING MAX(prod_price) > 10;
# 思考题:在供应商id大于1001的供应商中,查询每个供应商提供的产品数目,
# 以及每个供应商提供的商品中最贵的价格,
# 并且最高价格大于10的数据,并按供应商id从高到低排序
SELECT vend_id, COUNT(*), MAX(prod_price)
FROM products
WHERE vend_id > 1001
GROUP BY vend_id
HAVING MAX(prod_price) > 10
ORDER BY vend_id DESC;
# 作业题:从订单明细表中,查询每笔订单的订单编号和订单总额,
# 并且得到订单总额大于100的数据,将最终结果按照订单总额从大到小排序,
# 取前两条数据
SELECT order_num, SUM(quantity * item_price)
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity * item_price) > 100
ORDER BY SUM(quantity * item_price) DESC
LIMIT 2;
注意事项:
- GROUP BY 常和聚合函数一起使用,来统计数据。
- GROUP BY后面可以跟多个列。例如:
group by 年级列,班级列
。就会先按年级分组,再按班级进行分组。 - 分组后所有的数据统计都将在分组上进行汇总,也就是说不能从个别的列取数据。
- 在分组查询中,select 后的列名,要么是函数,要么是GROUP BY后的分组列。
- 如果分组中有NULL值,则NULL将作为一个分组返回。如果列中有多行NULL值,它们将分为一组。
- GROUP BY子句必须出现在WHERE子句之后,ORDER BY子句之前。
- 我们可以发现用GROUP BY分组的数据确实是以分组顺序输出的。但这样的顺序并不是一定的,因为它并不是SQL规范所要求的。所以,需要以分组顺序输出时,ORDER BY 必须要写。
- ORDER BY 是对最终的查询结果进行排序。
子查询
SQL还允许创建子查询(subquery),即嵌套在其他查询中的查询。
在WHERE子句中使用子查询能够编写出功能很强并且很灵活的SQL语句。
对于能嵌套的子查询的数目没有限制,不过在实际使用时由于性能的限制,不能嵌套太多的子查询。
提示:子查询一般与IN操作符结合使用,但也可以用于等于、不等于等。