子查询group by导致外层不走索引

转载

架构魔法师 2024-11-02 18:30:15

文章标签 子查询group by导致外层不走索引数据库 mysql sql datagrip 文章分类 数据仓库大数据

一、分组查询

分组查询是指使用group by字句对查询信息进行分组。

1、格式

SELECT 字段1,字段2… FROM 表名 GROUP BY 分组字段 HAVING 分组条件;

分组操作中的having子语句，是用于在分组后对数据进行过滤的，作用类似于where条件。

having与where的区别:
1、having是在分组操作执行后, 对分组后的数据进行过滤.
2、where是在分组操作执行前, 对分组前的数据只能使用表原始列进行条件过滤having后面可以使用聚合函数
3、where后面不可以使用聚合函数。
4、当一条SQL语句中, 既有where 又有 group by \ having时, 先执行 where, 再执行 group by, 最后执行having

2、练习

# 查询商品表，返回供应商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;

3、拓展

（1）查询每个供应商提供的产品数目，以及每个供应商提供的商品中最贵的价格（同时查询多个聚合函数）

SELECT vend_id, COUNT(*) AS '数量', MAX(prod_price) AS '最贵'FROM products GROUP BY vend_id;

（2）在供应商id大于1001的供应商中，查询每个供应商提供的产品数目，以及每个供应商提供的商品中最贵的价格；

SELECT vend_id, COUNT(*), MAX(prod_price)FROM products WHERE vend_id > 1001 GROUP BY vend_id;

（3）在供应商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;

（4）在供应商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;

（5）从订单明细表中，查询每笔订单的订单编号和订单总额，并且得到订单总额大于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;

4、注意事项

1、GROUP BY 常和聚合函数一起使用，来统计数据。
2、GROUP BY后面可以跟多个列。例如： group by 年级列，班级列 。就会先按年级分组，再按班级进行分组。
3、分组后所有的数据统计都将在分组上进行汇总，也就是说不能从个别的列取数据。
4、在分组查询中，select 后的列名，要么是函数，要么是GROUP BY后的分组列。
5、如果分组中有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。
6、GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。
7、用GROUP BY分组的数据确实是以分组顺序输出的。但这样的顺序并不是一定的，因为它并不是SQL规范所要求的。所以，需要以分组顺序输出时，ORDER BY必须要写。
8、ORDER BY 是对最终的查询结果进行排序。

二、子查询

到为止所看到的所有SELECT语句都是简单查询，即从单个数据库表中检索数据的单条语句。
SQL还允许创建子查询（subquery），即嵌套在其他查询中的查询。

先要学会分析表、要找到包含这些信息的表

正向思维：首先客户表可以看出秦始皇的客户编号是10001

子查询group by导致外层不走索引_数据库

在订单表中可以看到客户10001下了两单，并有对应的订单编号

子查询group by导致外层不走索引_数据库_02

从订单产品表中可以看到每个订单编号对应有哪些产品，展示了产品编号、数量、单价等信息

子查询group by导致外层不走索引_sql_03

1、例子

假设现在需要列出订购物品id为60005的所有客户id、客户名称，具体的步骤如下

(1) 从明细表中查出包含物品60005的所有订单的编号。
(2) 根据前一步骤查询出的订单编号，从订单表中查出所有客户的ID。
(3) 根据前一步骤查询出的的所有客户ID，从客户表中查出对应的客户信息。

根据题意可反向思维推导：

（1）从订单明细表中查询产品id为60005的所有订单编号

SELECT order_num
FROM orderitems
WHERE prod_id = 60005;

子查询group by导致外层不走索引_mysql_04

（2）根据上一步查询出的订单编号查询客户信息

SELECT cust_id
FROM orders
WHERE order_num IN (20005, 20007, 20009);

子查询group by导致外层不走索引_sql_05

综合一下：现在把第一个查询的结果作为第二个查询语句的子查询，也可以得出同样的结果

SELECT cust_id
FROM orders
WHERE order_num IN (
SELECT order_num
FROM orderitems
WHERE prod_id = 60005
);

解析：
首先，它将执行小括号内的子查询SELECT order_num FROM orderitems WHERE prod_id=60005；并返回三个订单编号：20005,20007,20009然后，这三个值以IN操作符要求的逗号分隔的格式传递给外部查询的WHERE子句，外部查询变成：SELECT cust_id FROM orders WHERE order_num IN (20005,20007,20009);

（3）现在得到了订购物品的所有客户的ID：10001，10004。下一步是检索这些客户ID的客户信息。

SELECT cust_id, cust_name
FROM customers
WHERE cust_id IN (
10001,
10004
);

子查询group by导致外层不走索引_sql_06

全部综合：用子查询进行嵌套则可以改为以下指令，也可得到同样的结果

SELECT cust_id, cust_name
FROM customers
WHERE cust_id IN (
   SELECT cust_id
   FROM orders
   WHERE order_num IN (
      SELECT order_num
      FROM orderitems
      WHERE prod_id = 60005
   )
);

注意事项：
1、在WHERE子句中使用子查询能够编写出功能很强并且很灵活的SQL语句。
2、对于能嵌套的子查询的数目没有限制，不过在实际使用时由于性能的限制，不能嵌套太多的子查询。
3、子查询一般与IN操作符结合使用，但也可以用于测试等于、不等于等。

2、练习

（1）使用子查询，返回购买价格为10元或以上产品的顾客id、顾客名称。

提示：使用orderitems表查找匹配的订单号（order_num）,然后使用order表检索这些匹配订单的顾客id（cust_id）

SELECT cust_id, cust_name
FROM customers
WHERE cust_id IN (
   SELECT cust_id 
   FROM orders 
   WHERE order_num IN (
      SELECT order_num 
      FROM orderitems 
      WHERE item_price >= 10
   )
);

子查询group by导致外层不走索引_数据库_07

（2）查询购买id为 60005 的产品的所有下单日期，以及下单的顾客id

提示：在orderitems 表中查询订购了60005 的订单编号，然后从order表中查询订单编号对应的客户id和订单日期，按订单日期对结果进行排序。

SELECT cust_id, order_date
FROM orders
WHERE order_num IN (
   SELECT order_num
   FROM orderitems
   WHERE prod_id = 60005)
ORDER BY order_date;   #ORDER BY 关键字用于对结果集进行排序

子查询group by导致外层不走索引_数据库_08

（3）查询购买id为 60005 的产品的所有顾客的邮件

SELECT cust_email
FROM customers
WHERE cust_id IN (
   SELECT cust_id
   FROM orders
   WHERE order_num IN (
      SELECT order_num
      FROM orderitems
      WHERE prod_id = 60005)
);

子查询group by导致外层不走索引_mysql_09