MySql014——分组的GROUP BY子句&排序ORDER BY&SELECT子句顺序

Love The Way You Lie 2024-05-05 22:19 87阅读 0赞

> 前提：使用[《MySql006——检索数据：基础select语句（使用products表、查询单列、多列、所有列、DISTINCT去除重复行、LIMIT限制返回结果的行数、了解完全限定）》][MySql006_select_products_DISTINCT_LIMIT]中创建的products表  
> ![在这里插入图片描述][7af96a258656474c9f9f28e0a9cf485b.png]

## 一、GROUP BY子句基础用法 ##

SELECT 
        vend_id, COUNT(*) AS num_prods
    FROM
        study.products
    GROUP BY vend_id;

上面的SELECT语句指定了两个列，

*  1、vend\_id代表产品供应商的ID；
 *  2、num\_prods为计算字段（用COUNT(\*)函数建立）。

GROUP BY子句指示MySQL`按vend_id进行分组`数据，即vend\_id相同的为一组。  
这导致对每组vend\_id计算num\_prods一次（而不是整个表）。  
从输出中可以看到，供应商1001有3个产品，供应商1002有2个产品，供应商1003有7个产品，而供应商1005有3个产品。  
![在这里插入图片描述][9ebc1cf4181141a19f8cde8937464f3c.png]

> `注意：`GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

## 二、过滤分组 ##

### 2.1、准备工作：在study库中创建表orders（订单表），并插入数据 ###

#####################
    # Create orders table
    #####################
    use `study`;
    
    CREATE TABLE orders
    (
      order_num  int      NOT NULL AUTO_INCREMENT,
      order_date datetime NOT NULL ,
      cust_id    int      NOT NULL ,
      PRIMARY KEY (order_num)
    ) ENGINE=InnoDB;
    
    #######################
    # Populate orders table
    #######################
    INSERT INTO orders(order_num, order_date, cust_id)
    VALUES(20005, '2005-09-01', 10001);
    INSERT INTO orders(order_num, order_date, cust_id)
    VALUES(20006, '2005-09-12', 10003);
    INSERT INTO orders(order_num, order_date, cust_id)
    VALUES(20007, '2005-09-30', 10004);
    INSERT INTO orders(order_num, order_date, cust_id)
    VALUES(20008, '2005-10-03', 10005);
    INSERT INTO orders(order_num, order_date, cust_id)
    VALUES(20009, '2005-10-08', 10001);

![在这里插入图片描述][892ca2da9a0a48579559f22beb887809.png]

### 2.2、例子1：想要列出至少有两个订单的所有顾客。 ###

SELECT 
        cust_id, COUNT(*) AS orders
    FROM
        study.orders
    GROUP BY cust_id	-- 使用GROUP BY，根据cust_id将相同顾客信息，分成一组
    HAVING COUNT(*) >= 2;	-- 使用HAVING和COUNT()选出订单数大于等于2 的顾客信息

![在这里插入图片描述][0b757ad6d8a94dd694d0c4c1a05cd927.png]

### 2.3、例子2，它列出具有2个及以上、价格为10及以上的产品的供应商 ###

SELECT 
        vend_id, COUNT(*) AS num_prods
    FROM
        study.products
    WHERE
        prod_price >= 10  -- 价格要大于等于10
    GROUP BY vend_id 	-- 根据vend_id分组
    HAVING COUNT(*) >= 2	-- 只选择分组中数据大于等于2条的
    
    # 即：根据vend_id分组，只选择分组中数据大于等于2条，且价格大于等于10的数据

![在这里插入图片描述][ff34cd49bc534ecf96114600a10cb510.png]

## 三、分组GROUP BY和排序ORDER BY区别 ##

### 3.1、准备工作：在study库中创建表orderitems（订单详细表），并插入数据 ###

#########################
    # Create orderitems table
    #########################
    CREATE TABLE orderitems
    (
      order_num  int          NOT NULL ,
      order_item int          NOT NULL ,
      prod_id    char(10)     NOT NULL ,
      quantity   int          NOT NULL ,
      item_price decimal(8,2) NOT NULL ,
      PRIMARY KEY (order_num, order_item)
    ) ENGINE=InnoDB;
    
    ###########################
    # Populate orderitems table
    ###########################
    INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
    VALUES(20005, 1, 'ANV01', 10, 5.99);
    INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
    VALUES(20005, 2, 'ANV02', 3, 9.99);
    INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
    VALUES(20005, 3, 'TNT2', 5, 10);
    INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
    VALUES(20005, 4, 'FB', 1, 10);
    INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
    VALUES(20006, 1, 'JP2000', 1, 55);
    INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
    VALUES(20007, 1, 'TNT2', 100, 10);
    INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
    VALUES(20008, 1, 'FC', 50, 2.50);
    INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
    VALUES(20009, 1, 'FB', 1, 10);
    INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
    VALUES(20009, 2, 'OL1', 1, 8.99);
    INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
    VALUES(20009, 3, 'SLING', 1, 4.49);
    INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
    VALUES(20009, 4, 'ANV03', 1, 14.99);

![在这里插入图片描述][f826e51ca8794acb916ee2634da9304d.png]  
直接上例子

### 3.2、例子 ###

它检索总计订单价格大于等于50的订单的订单号和总计订单价格，并按总计订单价格排序输出。

SELECT 
        ORDER_NUM, SUM(quantity * item_price) AS ordertotal
    FROM
        study.orderitems
    GROUP BY order_num
    HAVING SUM(quantity * item_price) >= 50
    ORDER BY ordertotal;

在这个例子中，GROUP BY子句用来按订单号（order\_num列）分组数据，以便SUM(\*)函数能够返回总计订单价格。HAVING子句过滤数据，使得只返回总计订单价格大于等于50的订单。最后，用ORDER BY子句排序输出。  
![在这里插入图片描述][15addecd9ad545988abe529caf9fdbc3.png]

## 四、SELECT子句顺序 ##

下面回顾一下SELECT语句中子句的顺序

=================================================================
    子 句 			说 明 					是否必须使用
    =================================================================
    SELECT 			要返回的列或表达式 			是
    FROM 			从中检索数据的表 			仅在从表选择数据时使用
    WHERE 			行级过滤 					否
    GROUP BY 		分组说明 				仅在按组计算聚集时使用
    HAVING 			组级过滤 					否
    ORDER BY 		输出排序顺序 					否
    LIMIT 			要检索的行数 					否
    =================================================================

## 与君共享 ##

?????最后，有兴趣的小伙伴可以点击下面链接，这里有我整理的MySQL学习博客内容，谢谢~ ?????

[《MySQL数据库学习》][MySQL]

[MySql006_select_products_DISTINCT_LIMIT]: https://blog.csdn.net/weixin_45084986/article/details/132094102
[7af96a258656474c9f9f28e0a9cf485b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/05/68ebae113aeb4c6e9f91b39a90052900.png
[9ebc1cf4181141a19f8cde8937464f3c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/05/ea6772d8d5894defa528538c7eb7051b.png
[892ca2da9a0a48579559f22beb887809.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/05/6e9cfead7eb24e848681c4243641829d.png
[0b757ad6d8a94dd694d0c4c1a05cd927.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/05/172e30837afd47e2b61f9e468b5ee091.png
[ff34cd49bc534ecf96114600a10cb510.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/05/5b720652301d49db8bbf3673e93e7cd4.png
[f826e51ca8794acb916ee2634da9304d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/05/75f07432304d422099bfaaa908d9638e.png
[15addecd9ad545988abe529caf9fdbc3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/05/a7ceb90d74114fe896943c7b74f18908.png
[MySQL]: https://blog.csdn.net/weixin_45084986/category_10980911.html