00:00
好,今天的最后一个内容啊,叫分组查询。分组查询先看一下这来了一个需求。什么样的需求呢?每个年级的学生人数各是多少人?每个年级的人数各是多少人?也就是说我要这个结果。就是一年级四人,二年级三人,三年级三人,我这么一个结果。这个结果咱们应该怎么查出来呢?其实啊,咱们学过这个聚合函数之后啊,想查询出咱们表中共有多少人,很简单是吧。哎,很简单,但是想再具体求出某个年级多少人就有点难了,咱们来看看啊,怎么做。先看看这个分组查询的原理,肯定是用分组查嘛。看好了啊。画的这个动画。分组,一切以组为单位。
01:02
这么搞?咱们分组查询,你看我查询的是第一学期的人数,第二学期的人数,第三学期的人数是吧。查询的都是人数,但是咱们应该按照分年级不同进行分组,也就是说。都是一年级,他们就是一组,二年级一组,三年级一组。就是以组为单位是吧,以组为单位好,这样分组之后,咱们再各自计算这个组里有多少人。哎,各自组里有多少人就算出来了。哎,以他们作为一个组是吧,他们作为一个组,像咱们这个班级里边现在是不是分组的。哎,我们一组多少人,二组多少人。就是这样的一个原理啊,以组为单位,就是分组的概念。好,那这个分组查询的代码怎么写呢?使用这个关键字。
02:03
使用这个关键字。Group呗,这group就是分组的意思是吧,一组一群的意思啊啊。咱们直接看需求,看我怎么写的。嗯。From。16人的。好,这是咱们这个表里的全部数据了。放在那。哎,这个是不是一年级的。哎,这些都是一年级的吧。好看好了啊,我要的结果是什么?首先你要查询的时候一定要知道你查的是啥。是不是一年级比如说八人。二年级十人,三年级九人,是不是要的这么一个数据结果啊。好,那前边这个列,这个列是不是就是great ID?后边这个列应该是什么,是不是人数啊,那人数的话,刚才咱们学过的聚合函数是不是看的星啊。
03:05
OK,看好了啊,所以查询这个星改为。Great ID和count。OK,这样的话感觉没啥问题,但是一执行报错。来报什么错?他说不是单组的分组函数。不是单组的分组函数,啥意思呢?记住了。只要出现刚才那个错误,就是说明你这个查询并没有真正的分组。并没有真正的分组。所以。简单点记报那个错,后边就是没有。就必须把狗半写上。写goodbye by,然后呢,Group by后边应该写什么呢?以什么什么作为分组是吧?刚才咱们说的是不是以年级做分组啊?所以。
04:00
拜,后边拜谁呢?拜的就是格瑞。好,这一执行完事了。看到一年级24人,二年级三年级。这个是咱们所要的结果。算的结果加一起来是不是62啊。哎,咱们整个表里是62条数据。OK,这就叫分组。分组。好,咱们再把这个代码再读一遍啊。查询的结果你先放在这儿。对吧,ID是他。数量行数是它是吧。然后呢,一直行,咱们这块执行到这儿的时候是报错了。不是单独的分组函数对吧,为什么?因为你想啊。看他先啥意思来着。他是。在很多数据里边统计出个数是吧。比如说咱们经常用的是不是一个表里有多少条啊,就是说给你一个结果集,这里边有多条数据多少条,这是不知道多少个,咱们一个count星是不是知道里边多少个了,他是帮你数出来吧。
05:08
对吧,OK,那你看这个东西是一堆数据里边帮你显示个数,那你前边应该是不是起个名啊,说一组里边。有多少个,二组里边有多少个,三组里边有多少个,是不是应该这么去写?OK,然后呢,以组为单位这个概念一出现,那么关键字必须得跟上。必须得分多。你不再是你单个的数据了,你必须要分组,必须要分组,你年级作为分组,这样的话呀,加上这个这个这个分组之后啊,它就会这么去算了。哎,那就会把表中所有一年级的,一年级的形成一个形成一个整体。
06:01
然后计算一年级有多少个。好,然后再找相同的。相同的年级啊,二年级的,二年级的形成一组,在计算它这里边儿有多少个。所以这个结果就出现这个结果了。OK。这是分组查询啊,好,我多写几个案例,咱们来看一看啊。再来一个。这个是。每个年级各。多少人再来一个?男女比例多少人?男女生各多少人?男女。同学各多少人?男女同学各多少人?其实咱们要的结果呢,脑子里边应该形成一个画面了啊,男。20人。女20人是不是这个意思啊?男多少人,女多少人,OK吧。
07:02
那你看啊,男同学和女同学各多少人?在这个查询中是以谁作为分组呢?就比如说咱现在这个这个生活中哈,我说男的靠左边站,女的靠右边站。是不是以性别作为分组啊对吧?哎,左边的男同学我再挨个查一遍,123456789个是吧,右边的女同学12356789对吧。咱们来写一下啊,是不是以性别分组啊。查询。性别。然后对应着多少人。好像。来自于学生表。Group。一定要分组,以什么分组啊,性别刚说过sex是吧?好,结果出来。男同学和女同学的人数和比例。OK。哎,就是这个结果啊,就是这个结果。
08:00
再往后看。好,这个就是刚才咱们写的这个分组啊,代码呢,跟这个是一样的啊,只不过它加了个啥呀。加了一个列的别名是吧。啊,Sla中啊sla列,这个列表中只能包含被分组的列。和为每个分组返回的一个表达式,这函数啥意思呢?就是说select后边跟的这个里边。这个叫select列表,好吧。他只能是写被分组的练。刚才是不是以年级分组啊和聚合函数。只能写这个。你不能写别的啊,不能写别的。这个呢,有一个套路,有一个套路。就是说你哎呀不知道,哎呀每次都考论考虑谁谁谁分组很麻烦啊。你就想结果你看我给大家说一下这个套路啊。
09:02
先看第一个啊,先看第一个。咱们要的结果你脑子里边形成一下,就是一年级多少人,二年级多少人是吧。所以第一列肯定就是年级编号没问题吧?第二列是不是就多少人啊,是不是康德星啊?OK吧,好,那后边这个分组这个到底写啥呢。行了。分组通常这个列写成什么,写成前边。除了聚合函数的列。看到了吧,把聚合函数一抹掉,是不是就和瑞台D拉过来了?你看下一个也是。下一个也行看。拜,跟谁分组啊?除了聚合函数的是不是这个列。哎,这个就是这是100%正确的一个套路。好吧。好,咱们来再看一个表。员工根据就怎么说呢。
10:04
统计每个部门多少员工部门啊。部门。多少员工?员工表了吧?同啊,Select from。员工。这个每个部门多少员工,其实是不是就是呃,50号部门,50号部门所,所以说这个50号部门。就这个数据来说,是不是他有两个人啊。对吧。哎,所以我要的结果你看啊,我要什么结果,就是一号部门三个人,五号部门八个人,我要的是不是这个结果啊。好,看出来啊。首先查询的是几号部门,那就是部门编号,逗号就统计几个人啊。看他行好。
11:01
按套路拜,后边拜谁?那谁?哎,除了聚合的这个列。就是这个结果。就是前面多是吧,嗯,慢慢来啊,马上后边就讲到了啊,马上就知道了,马上就讲到了。好吧,这个看清了没。这个看清了没?其实啊,说白了说到这儿了,就是一个套路的问题哈。形成这个概念啊,先查,先把这个结果搞出来,一号部门多少人,二号部门多少人,把这个结果先放在这儿。然后分组的时候你就查,你就怎么写。除了聚合函数这个列,你就给我扔在这肯定没错。OK。好,这就是他了,这就是他。
12:03
好呃,然后呢,咱们再来看看啊,部门人数咱们已经统计完成了是吧。有没有什么别的需求?这儿呢,还有需求了。我统计什么?查询每门课的平均分。每门课的平均分。来,我把这个copy过来啊。每门每门课程的平均分来哪张表啊?以后一看到分数表。伪造的表啊。分数吗?这个表还记得什么列了吗?这个列为分数列。考了80分,50分90分啊,然后呢,每门课什么意思这呢。一号是不是表示的都是第一门课呀?
13:00
哎,就是把就咱们假设这个表里就这些数据好吧,那就是一号的分组,一号的分成组,然后求这些和这些的平均值,OK。那你一看到这个结果的时候,脑子里边形成,哎,他要查啥。是不是第一门课平均分66,第二门课平均分77,是不是这个结果啊。你看科目ID怎么练?是不是这个列科目编号吗。所以后边这个练什么练。是不叫平均值啊。来写上,首先是科目的编号,后边是平均值。哪个列的平均值能用星了吗?应该是。这个列的平均值吧。写上student result。
14:00
好了。执行程序写到这儿,他执行,你不用执行,100%报错,为啥嘞。明明有单个单个列,还有聚合列,那肯定需要分组啊。肯定需要分组。你不信咱们这样执行一下,肯定报的那个什么,不是单组的分组函数。对吧。哎,好,那么grew后边我应该分什么呢?叫除了聚合这个列的列吧,所以直接把ID往这一扔搞定。就是这个结果。第一门课平均分,第二门课第八门课是吧,就是这个结果了。就是这个结果。好,OK,看看看就看见了吗。来看一下PPT也是一样的哈。这样的。
15:01
啊,他是起了个名是吧,这个名。这是咱们这个结果啊。OK。再来。再来。查询每门课程的平均分,哎,刚才搞过了吧,他又来需求了,按照分数由低到高顺序显示。就变成这个结果就是越来越值越大,但是分数这个列啊,咋做来着。叫排序升序吧,是不是升序啊。发现了。后边order by。有同学要有这块有疑问了,Out,能不能放前面啊?我之前我说过奥必须放什么。哎,末尾哈,Order by啊,Order by放在这,那这个order by我要跟谁排序,排哪个列,是不是这个列啊。
16:00
好,这个列,这个列的列名叫什么。是不是叫avg啊,也就是说我要把这个值是不是要进行排序啊。所以把整个的这个平均值的计算链直接放在后边。OK。好,再来执行一下,看对不对啊,是不是六十八六十九七十七十七七啊。越来越大,越来越大。对吧,哎,这个就是all y和分组的同时应用。同时一个应用啊。接着往后看。多列分组。多列分组,看这写的什么每学期男女人数,这咋办呢?我先把这个需求搞过来。每学期男女生。
17:02
要的什么呀,就是说这个意思,一年级男八人,一年级女六人。二年级男多少人?二年级女多少人?OK,是不是这个结果啊?那这个结果咱们应该怎么查呢?其实多列分组啊,它是这么一个需求,首先。以年级进行分组。年级进行分组,看到了吧,这都是一年级的,这都是二年级的,拉帮结派分成了两个派是吧,然后在。这个分组中再分组呗。这么搞?对吧,在这个里边再把它筛选一下,男的一组女的一组。这里边儿男的一组,女的一组。具体怎么写,看我的。首先咱们查询的结果select。我要求查询年级。
18:01
一年级男是不是性别呀?然后多少人呢?OK。然后来自于学生表。好了,这里边出现了聚合,出现了普通的列,那肯定需要用到group g。好,问题又来了,我刚才说goodbye后边写啥来着?除了聚合函数吧,你就把聚合函数这玩意一删。哎,剩两个了对吧,那他就懵了,哎呀,之前一直国外一个,那这两个能不能搞过来呀。我就说了那句话,还是那句话。除了聚合函数的列,全给我搞过来。好吧。就把这俩玩意儿。完事。完事了。是不是这个结果。有的,哎呀,顺序不太对,哎。顺序不对的话,好办不?
19:02
好吗?哎。Order by拜谁呢?我就拜一个great吧。是不是这个结果。一年级男女二年级男女三年级男女OK。所以。这个套路是不是特别好用啊?后边到底BY什么?BY的就是除了聚合函数的列。除了聚合函数列。好,这就是咱们这个多列分组啊,多列分组。好,这个代码呢,跟我刚才是一样的啊。子句在这里有什么作用?排序是吧,排序啊。那刚才这个写完了之后呢,需求又来了。新的需求是吧。或许总人数超过18人的年级。18人的年级。
20:00
咱们来看一看刚才这个数据哈,每个年级各多少人,在哪里呢?每个年级各多少人?在呢吧。执行一下。是不是有24 20和18呀。现在我要求是18的,不要,因为他需求是18人以上的。所以我要把这个值砍掉,我只要这两个。怎么写?有同学说加个where条件把这个这个这个抗的星是吧。得过滤一下是吧。好,我们来一遍啊。需求是。统计每个年级的人数,条件是什么呢?超过。啊,低于。18人的不显示。对吧。好了。Slack。
21:01
每个年级,然后人数。人数来自于学生表。Group by。Great ID就是咱们上边写的一模一样的吧?对这个结果,我要对这个结果是不是再次把它要筛选出去。好,有同学说后边where can't星。要超过18的才留下。好像写的没啥问题哈,能够读得通。一直行。一直想。告诉你啥呀。语句未正确结束是吧?记着啊。先生是这样的。现在呢,咱们这个结果,这个结果是经过分组统计之后的结果,对吧。
22:01
咱们是通过grew by这个还这个这个关键字统计出来的结果,那么对。写上来啊。对分组之后的数据再次进行条件过滤的话。不能使用where。二。使用having。也就是说where不能用了,因为咱们这个数据是分组之后的。所以我只能用having,又是一个新关键字啊,这个关键字的用法和where一样,所以在这一块呢,用having把where替代。完事。OK。不行。也不行。你必须得先它,因为它是先分组才能出现那个结果啊,如果你把这个哈放前面,那不是先过先过滤再分组吗。
23:05
结果是不一样的啊。等会咱们再说where,如WHY,还有having这三个语法都用的时候怎么样?好,现在呢,把这句话呢再捣鼓一遍。对吧,现在这个结果,看着这个结果是分组之后的数据吧。那想要对这个分组之后的数据啊,想要再次条件过滤的话。不能用。刚才用过报错是吧。哎,而用having就成了。用having就成了。这句话很重要很重要啊。咱们再多写几个练习就能更熟悉一些啊,比如说。Ah。好了。按呃统计。
24:00
每个部门多少人?每个部门多少人对吧。然后呢?想想啊,怎么办?写一个,把这个where也给大家加上吧。嗯,统计每个部门多少人。这多少人了?统计每个部门的最高月薪工资吧,月最高工资。每个部门的最高工资。看到了吗?然后呢,你先把这个统计出来,我再说,然后。每个部门最高的工资,来查一下,看看啥意思啊?好,咱就说这50号部门吧。两个50吧。那么这50号部门的最高工资是多少?是不是两千五啊,哎,我要的就是这个结果,就是说显示50号部门2500。
25:04
OK。哎,然后比如说这个41号部门显示是多少。是不显示一千四百五啊。因为这个值比这个值大嘛。这个就是每个部门的最高工资,好怎么统计?首先每个部门的编号搞得上来。然后后边是不是显示的就是最高值了。Mars。Salary。OK,是不是有普通列,有这不普通列吗?是不是还有聚合呀?所以go by。Z,拜拜谁?除了聚合函数的这个列,OK,肯定没毛病,好,你就找50多,看别的50是不是2500。没毛病是吧?那这个就是咱们这一段的需求搞定了,然后然后。那我再给它过滤啊。
26:01
部门。人数。低于三人的不显示。显示。低于三人的不显示。啥意思嘞,就是说42号部门。可能有多少个人?可能有八个人。43号部门可能就一个人对吧。我要求对这个人数再次进行过滤。对这个人数进行过滤好怎么做呢?是不是把这个人数再过滤一下不就行了吗?对分组之后的数据再次过滤的话,后边写啥。还有没。对吧,Having什么东西什么什么东西是吧,是不是人数啊。好行吗?康型对吧。多少?低于三的不显示,那就是大于二呗。
27:01
大于二的显示呗。是吧,行行好,就这几个。就是这个结果音。好,咱来看一看啊,我记得刚才咱们在看莱星这个表中全部数据的时候,50号部门俩人吧。对吧,好58部门出来了吗。没有吧,两个人是不是我说低于30的不显示啊,所以两个人。这个50号部门怎么样了,干掉了。所以再次证明我这个结果是没问题的。哎,低于三人的不显示,就这么干。咱们再来一个。呃,统计。每个部门,嗯。平均工资。而且平均工资高于一千四的。
28:03
蒙语啊,不一定有数据哈,呃,高于一千四的才显示。就是统计每个部门的平均工资。这个好统计是吧。一号部门800块,二号部门700块。咋统计啊星。部门编号。平均工资avg。Salary。来自于员工表。肯定要group by啊,Group。拜拜啥嘞,非聚合列。去执行,这个就是每个部门的平均工资。哪个部门的,五号部门的。它比较高是吧。对吧。然后呢,他说平均工资高于一千四才显示,也就是说呀,一千四以下这个这个。这个这个是不是都不要啊。OK,又是对分组之后的数据,是不是再次过滤啊?
29:05
所以在它后边用。Having。Avg。平均工资。超过多少的?1400。OK,就是这个结果。你看没有一千四以下的啊。没有1400。好,再来。统计。每个部门。中。每个部门中。呃,新工资。高于一千四的人数。低于两人的不显示。
30:03
每个部门工资高于一千四的人数。来。查看一下这个数据先。这个没有比上面两个都难啊。看一下啊,你就甭看上面那俩代码了,看这吧。他说,每个部门中工资高于一千四的人数。我这块比如说啊。55门。不是。搞错了啊,50号部门是不是这两人呢。一个是2500,一个是1550,好,那么也就是说50号部门中有几个人高于1400?是不是两个人都高于一千四啊,所以我这个要求是不是50号部门显示两人啊。对吧。假设。再看这个。41号部门。假设啊,要。
31:00
啊。三个人2000。两个人1000,那么这块我应该显示几。应该显示三对吧。看好了,怎么写呢?这个东西怎么写?统计每个部门中高于一千四的人数。OK,这么写啊。嗯,不买。编号。人数。看。那你这样写的group by?然后呢,DPTD。好,告诉我,现在我这个结果是啥意思,你先甭看这个中文,你就把这个翻译成中文是啥。这叫每个部门的人数吧。对吧,这个查询是不是每个部门的人数啊。但是我这个条件他加上了吗。你必须得分清楚。这个条件是分组之前就应该加上,还是分组之后再加?
32:06
一定是在分组之前吧。就是说你看我说咱班两米身高以上的有几个人。是不是得先看身高再统计个数啊?哎,所以呢,高于一千四这个玩意儿一定是在分组之前。那么分组之前就要用到。Where?工资。超过1400。OK。然后再分组。执行。这样的就是啥意思嘞,现在把这个语句翻译一遍,34号部门有一个人超过了1400,是不是这意思啊。50号部门两个没毛病吧。然后十号部门一个。就是这个结果,然后现在呢,又来需求了。结果刚搞到这,他又来了,低于两人的不显示,那也就是说这111是不是全干掉啊。
33:06
那么现在低于两个人不显示在什么时候过滤呢?是不是分组之后啊?所以在他后边再来。Hi。Count芯。应该。低于两人的,不显示大于一呗。好,就是这个结果。就是这结果。因为50套部门里边一哥们2500,一哥们一千五百五是吧,全都超过了1400。好。这就是这个结果,好,这回呢,大家来看一下,我这个里边用到了where,用到了goodbye,用到了having是吧。好,咱们简单看一下这。分组之后筛选,咱们用having,这刚才都说过了啊,咱就过了,然后where和having的一个对比,记住了,看好了这篇。
34:05
什么时候用where where是干嘛的?它是用来筛选from子句中指定产生的结果的。Where啊,Where干嘛的,它是筛选from之后的。看我这个代码。你看我这个where是不是写在紧挨着from啊?您from完成之后,我首先就用where进行第一层过滤。进行第一层过滤。好,然后用group by用来分组where子句的输出。也就是说,先where条件过滤一下,然后再用group by分组,最后用having having是用来group by之后的结果再次筛选。所以这个有点类似于咱们像。呃,就像这个水龙头那种过滤器是吧,先第一层粗略的过滤一下。
35:02
然后细致的过滤一下,最后。非常细致的过滤一下。对吧,一层一层一层一层,所以记住这个三个关键字的使用顺序其实很好记,WGH。你就记住,他们仨不可能颠倒,不可能颠倒。Where能写在这吗?不能,H能写在这吗?不能,必须是WGOK。必须按照这个顺序啊。好,那现在呢,考验一下大家的语文实力哈。把这个语句翻译成中文,应该怎么去说好?咱们来看一看。按照我这30年的语文,怎么给他翻译过来的?但是呢?查询有多个员工的工资不低于2000的部门。我要的是部门编号啊。
36:02
你看我这个多个,哎,这个是点睛之笔啊,夸一下自己。大于一。是不是多个?哎,就在这呢啊。30多年文化全用在这儿了。大于一啊,OK,读读下来没问题吧。OK,好了,接下来是大家的。四个练习写出来。每一个的表呢,我已经给大家给后边写在这了啊。
我来说两句