分组查询&子查询_group by 子查询

分组查询

分组查询是指使用 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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57

注意事项：

• 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操作符结合使用，但也可以用于等于、不等于等。