mysql中group by和order by到底应该如何用

最新推荐文章于 2024-05-06 02:16:20 发布

nfzhlk

最新推荐文章于 2024-05-06 02:16:20 发布

阅读量5.4k

点赞数 6

分类专栏：数据库测试技术

本文链接：https://blog.csdn.net/nfzhlk/article/details/109204159

版权

测试技术同时被 2 个专栏收录

24 篇文章 3 订阅

订阅专栏

数据库

9 篇文章 0 订阅

订阅专栏

一、group by的原理

　　先来看下表1，表名为test：

表1

　　执行如下SQL语句：

SELECT  name  FROM  test GROUP  BY  name

你应该很容易知道运行的结果，没错，就是下表2：

表2

　　可是为了能够更好的理解“group by”多个列“和”聚合函数“的应用，我建议在思考的过程中，由表1到表2的过程中，增加一个虚构的中间表：虚拟表3。下面说说如何来思考上面SQL语句执行情况：

1.FROM test：该句执行后，应该结果和表1一样，就是原来的表。

2.FROM test Group BY name：该句执行后，我们想象生成了虚拟表3，如下所图所示，生成过程是这样的：group by name，那么找name那一列，具有相同name值的行，合并成一行，如对于name值为aa的，那么<1 aa 2>与<2 aa 3>两行合并成1行，所有的id值和number值写到一个单元格里面。

3.接下来就要针对虚拟表3执行Select语句了：

（1）如果执行select *的话，那么返回的结果应该是虚拟表3，可是id和number中有的单元格里面的内容是多个值的，而关系数据库就是基于关系的，单元格中是不允许有多个值的，所以你看，执行select * 语句就报错了。【备注：mysql8已经修复了该问题，隐式排序】

（2）我们再看name列，每个单元格只有一个数据，所以我们select name的话，就没有问题了。为什么name列每个单元格只有一个值呢，因为我们就是用name列来group by的。

（3）那么对于id和number里面的单元格有多个数据的情况怎么办呢？答案就是用聚合函数，聚合函数就用来输入多个数据，输出一个数据的。如cout(id)，sum(number)，而每个聚合函数的输入就是每一个多数据的单元格。

（4）例如我们执行select name,sum(number) from test group by name，那么sum就对虚拟表3的number列的每个单元格进行sum操作，例如对name为aa的那一行的number列执行sum操作，即2+3，返回5，最后执行结果如下：

（5）group by 多个字段该怎么理解呢：如group by name,number，我们可以把name和number 看成一个整体字段，以他们整体来进行分组的。如下图

（6）接下来就可以配合select和聚合函数进行操作了。如执行select name,sum(id) from test group by name,number，结果如下图：

***二、order by的用法***

使用order by，一般是用来，依照查询结果的某一列（或多列）属性，进行排序（升序：ASC；降序：DESC；默认为升序）。

当排序列含空值时：

ASC：排序列为空值的元组最后显示。

DESC：排序列为空值的元组最先显示。

为了好记忆，我的理解是，可以把null值看做无穷大，因为不知道具体为多少。然后去考虑排序，asc升序null肯定在最后，而desc降序，null肯定在最前面。（我的想法，轻喷。。。

1》单一列属性排序

举例1：默认排序：

select * from s

select * from s order by sno desc

按照sno降序：

2》多个列属性排序

选择多个列属性进行排序，然后排序的顺序是，从左到右，依次排序。

如果前面列属性有些是一样的话，再按后面的列属性排序。（前提一定要满足前面的属性排序，因为在前面的优先级高）。

举例2：

select * from s  
order by sname desc, sage desc

首先按照sname降序排序，然后出现了xx一样的，在按照sage降序排序。（默认sage是升序）。

如果最开始使用sno排序，

select * from s 
order by sno desc,  sage asc

必须先满足前面列属性的排序（sno在前优先级高）。才会去考虑后续列属性的排序。

三、group by的用法

group by按照查询结果集中的某一列（或多列），进行分组，值相等的为一组。

1》细化集函数（count，sum，avg，max，min）的作用对象：

未对查询结果分组，集函数将作用于整个查询结果。

对查询结果分组后，集函数将分别作用于每个组。

例子3：

求各个课程号及相应的选课人数：

select cno,count(sno) from sc

对整个表进行count。

select cno,count(sno) from sc group by cno

对分组的表进行count

sc表内容如下：

2》GROUP BY子句的作用对象是查询的中间结果表

分组方法：按指定的一列或多列值分组，值相等的为一组。

使用GROUP BY子句后，SELECT子句的列名列表中只能出现分组属性（比如：sno）和集函数（比如：count（））。

select sno,count(cno) from sc group by sno

3》多个列属性进行分组举例：

select cno,grade,count(cno) from sc group by cno,grade

cno为1且成绩为66的，有3个

4》使用HAVING短语筛选最终输出结果

只有满足HAVING短语指定条件的组才输出。

HAVING短语与WHERE子句的区别：作用对象不同。

1》WHERE子句作用于基表或视图，从中选择满足条件的元组。

2》HAVING短语作用于组，从中选择满足条件的组。

举例：

查询选修了3门以上课程的学生学号：

select sno from sc group by sno having count(cno)>3

举例：

查询选修了3门以上课程，且所有课程成绩都高于60分的学生学号及课程数

select sno , count(cno)
from sc 
where grade > 60  
group by sno having count(cno) > 3

参考文档：1.https://blog.csdn.net/yulutian/article/details/93247203
2.https://blog.csdn.net/jiangxindu1/article/details/48147149?utm_medium=distribute.pc_relevant_t0.none-task-blog-BlogCommendFromMachineLearnPai2-1.channel_param&depth_1-utm_source=distribute.pc_relevant_t0.none-task-blog-BlogCommendFromMachineLearnPai2-1.channel_param