SQL语句--Group By总结.doc

SQL语句-Group By总结SQL语句-Group By总结1SQL语句-GroupBy总结1.GroupBy语句简介：GroupBy语句从英文的字面意义上理解就是“根据(by)一定的规则进行分组(Group)”。它的作用是通过一定的规则将一个数据集划分成若干个小的区域，然后针对若干个小区域进行数据处理。P.S.这里真是体会到了一个好的命名的力量，GroupBy从字面是直接去理解是非常好理解的。恩，以后在命名的环节一定要加把劲:)。话题扯远了。2.GroupBy的使用：上面已经给出了对GroupBy语句的理解。基于这个理解和SQLServer2022的联机帮助，下面对GroupBy语句的各种典型使用进行依次列举说明。2.1GroupByExpressions:这个恐怕是GroupBy语句最常见的用法了，GroupBy+分组字段(可以有多个)。在执行了这个操作以后，数据集将根据分组字段的值将一个数据集划分成各个不同的小组。比如有如下数据集，其中水果名称(FruitName)和出产国家(ProductPlace)为联合主键：FruitNameProductPlacePriceAppleChina$1.1AppleJapan$2.1AppleUSA$2.5OrangeChina$0.8BananaChina$3.1PeachUSA$3.0如果我们想知道每个国家有多少种水果，那么我们可以通过如下SQL语句来完成：SELECTCOUNT(*)AS水果种类,ProductPlaceAS出产国FROMT_TEST_FRUITINFOGROUPBYProductPlace这个SQL语句就是使用了GroupBy+分组字段的方式，那么这句SQL语句就可以解释成“我按照出产国家(ProductPlace)将数据集进行分组，然后分别按照各个组来统计各自的记录数量。”很好理解对吧。这里值得注意的是结果集中有两个返回字段，一个是ProductPlace(出产国),一个是水果种类。如果我们这里水果种类不是用Count(*)，而是类似如下写法的话：SELECTFruitName,ProductPlaceFROMT_TEST_FRUITINFOGROUPBYProductPlace那么SQL在执行此语句的时候会报如下的类似错误：选择列表中的列"T_TEST_FRUITINFO.FruitName"无效，因为该列没有包含在聚合函数或GROUPBY子句中。这就是我们需要注意的一点，如果在返回集字段中，这些字段要么就要包含在GroupBy语句的后面，作为分组的依据；要么就要被包含在聚合函数中。我们可以将GroupBy操作想象成如下的一个过程，首先系统根据SELECT语句得到一个结果集，如最开始的那个水果、出产国家、单价的一个详细表。然后根据分组字段，将具有相同分组字段的记录归并成了一条记录。这个时候剩下的那些不存在于GroupBy语句后面作为分组依据的字段就有可能出现多个值，但是目前一种分组情况只有一条记录，一个数据格是无法放入多个数值的，所以这里就需要通过一定的处理将这些多值的列转化成单值，然后将其放在对应的数据格中，那么完成这个步骤的就是聚合函数。这就是为什么这些函数叫聚合函数(aggregatefunctions)了。2.2GroupByAllexpressions：GroupByAll+分组字段,这个和前面提到的GroupByExpressions的形式多了一个关键字ALL。这个关键字只有在使用了where语句的，且where条件筛选掉了一些组的情况才可以看出效果。在SQLServer2022的联机帮助中，对于GroupByAll是这样进行描述的：作者：mnmnm6692022-4-1418:46回复此发言2SQL语句：GroupBy总结如果使用ALL关键字，那么查询结果将包括由GROUPBY子句产生的所有组，即使某些组没有符合搜索条件的行。没有ALL关键字，包含GROUPBY子句的SELECT语句将不显示没有符合条件的行的组。其中有这么一句话“如果使用ALL关键字，那么查询结果将包含由GroupBy子句产生的所有组.没有ALL关键字，那么不显示不符合条件的行组。”这句话听起来好像挺耳熟的，对了，好像和LEFTJOIN和RIGHTJOIN有点像。其实这里是类比LEFTJOIN来进行理解的。还是基于如下这样一个数据集：FruitNameProductPlacePriceAppleChina$1.1AppleJapan$2.1AppleUSA$2.5OrangeChina$0.8BananaChina$3.1PeachUSA$3.首先我们不使用带ALL关键字的GroupBy语句：SELECTCOUNT(*)AS水果种类,ProductPlaceAS出产国FROMT_TEST_FRUITINFOWHERE(ProductPlace"Japan")GROUPBYProductPlace那么在最后结果中由于Japan不符合where语句，所以分组结果中将不会出现Japan。现在我们加入ALL关键字：SELECTCOUNT(*)AS水果种类,ProductPlaceAS出产国FROMT_TEST_FRUITINFOWHERE(ProductPlace"Japan")GROUPBYALLProductPlace重新运行后，我们可以看到Japan的分组，但是对应的“水果种类”不会进行真正的统计，聚合函数会根据返回值的类型用默认值0或者NULL来代替聚合函数的返回值。2.3GROUPBYExpressionsWITHCUBE|ROLLUP：首先需要说明的是GroupByAll语句是不能和CUBE和ROLLUP关键字一起使用的。首先先说说CUBE关键字，以下是SQLServer2022联机帮助中的说明：指定在结果集内不仅包含由GROUPBY提供的正常行，还包含汇总行。在结果集内返回每个可能的组和子组组合的GROUPBY汇总行。GROUPBY汇总行在结果中显示为NULL，但可用来表示所有值。使用GROUPING函数确定结果集内的空值是否是GROUPBY汇总值。结果集内的汇总行数取决于GROUPBY子句内包含的列数。GROUPBY子句中的每个操作数（列）绑定在分组NULL下，并且分组适用于所有其它操作数（列）。由于CUBE返回每个可能的组和子组组合，因此不论指定分组列时所使用的是什么顺序，行数都相同。我们通常的GroupBy语句是按照其后所跟的所有字段进行分组，而如果加入了CUBE关键字以后，那么系统将根据所有字段进行分组的基础上，还会通过对所有这些分组字段所有可能存在的组合形成的分组条件进行分组计算。由于上面举的例子过于简单，这里就再适合了，现在我们的数据集将换一个场景，一个表中包含人员的基本信息：员工所在的部门编号(C_EMPLINFO_DEPTID)、员工性别(C_EMPLINFO_SEX)、员工姓名(C_EMPLINFO_NAME)等。那么我现在想知道每个部门各个性别的人数，那么我们可以通过如下语句得到：SELECTC_EMPLINFO_DEPTID,C_EMPLINFO_SEX,COUNT(*)ASC_EMPLINFO_TOTALSTAFFNUMFROMT_PERSONNEL_EMPLINFOGROUPBYC_EMPLINFO_DEPTID,C_EMPLINFO_SEX但是如果我现在希望知道：1.所有部门有多少人(这里相当于就不进行分组了，因为这里已经对员工的部门和性别没有做任何限制了，但是这的确也是一种分组条件的组合方式)；2.每种性别有多人(这里实际上是仅仅根据性别(C_EMPLINFO_SEX)进行分组)；扩展阅读：数据库简单SQL语句总结SQL关系型数据库系统简单SQL语句总结数据库简单SQL语句总结全篇以学生成绩的管理为例描述。1.在查询结果中显示列名：a.用as关键字：selectnameas"姓名"fromstudentsorderbyageb.直接表示：selectname"姓名"fromstudentsorderbyage2.精确查找:a.用in限定范围：select*fromstudentswherenativein("湖南","四川")b.between.and：select*fromstudentswhereagebetween20and30c.“=”：select*fromstudentswherename="李山"d.like:select*fromstudentswherenamelike"李%"(注意查询条件中有“%”，则说明是部分匹配，而且还有先后信息在里面，即查找以“李”开头的匹配项。所以若查询有“李”的所有对象，应该命令："%李%"若是第二个字为李，则应为"_李%"或"_李"或"_李_"。)e.匹配检查符：select*fromcourseswherecnolike"AC%"(表示或的关系，与"in(.)"类似，而且""可以表示范围，如：select*fromcourseswherecnolike"A-C%")3.对于时间类型变量的处理a.smalldatetime：直接按照字符串处理的方式进行处理，例如：select*fromstudentswherebirth>="1980-1-1"andbirthSQL关系型数据库系统简单SQL语句总结groupbygender(查看男女学生各有多少)注意：从哪种角度分组就从哪列"groupby"对于多重分组，只需将分组规则罗列。比如查询各届各专业的男女同学人数，那么分组规则有：届别(grade)、专业(mno)和性别(gender)，所以有"groupbygrade,mno,gender"selectgrade,mno,gender,count(*)fromstudentsgroupbygrade,mno,gender通常group还和having联用，比如查询1门课以上不及格的学生，则按学号(sno)分类有：selectsno,count(*)fromgradeswheremark16.UNION联合合并查询结果，如：SELECT*FROMstudentsWHEREnamelike张%UNIONALLSELECT*FROMstudentsWHEREnamelike李%7.多表查询a.内连接selectg.sno,s.name,c.coursenamefromgradesgJOINstudentssONg.sno=s.snoJOINcoursescONo=o(注意可以引用别名)b.外连接2SQL关系型数据库系统简单SQL语句总结b1.左连接o,max(coursename),count(sno)fromcoursesLEFTJOINgradesONo=o左连接特点：显示全部左边表中的所有项目，即使其中有些项中的数据未填写完全。左外连接返回那些存在于左表而右表中却没有的行，再加上内连接的行。b2.右连接与左连接类似b3.全连接selectsno,name,majorfromstudentsFULLJOINmajorsONstudents.mno=majors.mno两边表中的内容全部显示c.自身连接o,c1.coursename,c1.pno,c2.coursenamefromcoursesc1,coursesc2wherec1.pno=o采用别名解决问题。d.交叉连接selectlastname+firstnamefromlastnameCROSSJOINfirstanme相当于做笛卡儿积8.嵌套查询a.用关键字IN,如查询李山的同乡：select*fromstudentswherenativein(selectnativefromstudentswherename=李山)b.使用关键字EXIST,比如，下面两句是等价的：select*fromstudentswheresnoin(selectsnofromgradeswherecno=B2)SQL关系型数据库系统简单SQL语句总结select*fromstudentswhereexists(select*fromgradeswheregrades.sno=students.snoANDcno=B2)9.关于排序ordera.对于排序order，有两种方法：asc升序和desc降序b.对于排序order,可以按照查询条件中的某项排列，而且这项可用数字表示，如：selectsno,count(*),avg(mark)fromgradesgroupbysnohavingavg(mark)>85orderby310.其他a.对于有空格的识别名称，应该用""括住。b.对于某列中没有数据的特定查询可以用null判断，如selectsno,coursenofromgradeswheremarkISNULLc.注意区分在嵌套查询中使用的any与all的区别，any相当于逻辑运算“|”而all则相当于逻辑运算“&&”d.注意在做否定意义的查询是小心进入陷阱：如，没有选修B2课程的学生：selectstudents.*fromstudents,gradeswherestudents.sno=grades.snoANDoB2上面的查询方式是错误的，正确方式见下方：select*fromstudentswherenotexists(select*fromgradeswheregrades.sno=students.snoANDcno="B2")SQL关系型数据库系统简单SQL语句总结11.关于有难度多重嵌套查询的解决思想：如，选修了全部课程的学生：select*fromstudentswherenotexists(select*fromcourseswhereNOTEXISTS(select*fromgradeswheresno=students.snoANDcno=o)最外一重：从学生表中选，排除那些有课没选的。用notexist。由于讨论对象是课程，所以第二重查询从course表中找，排除那些选了课的即可。第 8 页 共 8 页