sql中的 where 、group by 和 having 用法解析

亦凉 2023-10-06 08:49 18阅读 0赞

--sql中的 where 、group by 和 having 用法解析
    
      
    
    --如果要用到group by 一般用到的就是“每这个字” 例如说明现在有一个这样的表：每个部门有多少人 就要用到分组的技术
    
      
    
    select DepartmentID as '部门名称',COUNT(*) as '个数' from BasicDepartment group by DepartmentID
    
    --这个就是使用了group by +字段 进行了分组，其中我们就可以理解为我们按照了部门的名称ID
    
    --DepartmentID将数据集进行了分组；然后再进行各个组的统计数据分别有多少；
    
    --如果不用count(*) 而用类似下面的语法
    
    select DepartmentID,DepartmentName from BasicDepartment group by DepartmentID
    
      
    
    --将会出现错误
    
    --选择列表中的列 'BasicDepartment.DepartmentName' 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。
    
      这就是我们需要注意的一点，如果在返回集字段中，这些字段
    
      要么就要包含在Group By语句的后面,作为分组的依据；
    
      要么就要被包含在聚合函数中,作为分组的依据；
    
    --出现的错误详解:咱们看看group by 的执行的过程,先执行select 的操作返回一个程序集，
    
    --然后去执行分组的操作，这时候他将根据group by 后面的字段
    
    --进行分组，并且将相同的字段并称一列数据，如果group by 后面没有这个字段的话就要分成好多的数据。
    
    --但是分组就只能将相同的数据分成两列数据，而一列中又只能放入一个字段，所以那些没有进行分组的
    
    --数据系统不知道将数据放入哪里，所以就出现此错误
    
    --目前一种分组情况只有一条记录，一个数据格是无法放入多个数值的，
    
    --所以这里就需要通过一定的处理将这些多值的列转化成单值，然后将其放在对应的
    
    --数据格中，那么完成这个步骤的就是聚合函数。这就是为什么这些函数叫聚合函数(aggregate functions)了
    
      
    
    --group by all语法解析：
    
    --如果使用 ALL 关键字，那么查询结果将包括由 GROUP BY 子句产生的所有组，即使某些组没有符合搜索条件的行。
    
    --没有 ALL 关键字，包含 GROUP BY 子句的 SELECT 语句将不显示没有符合条件的行的组。
    
    select DepartmentID,DepartmentName as '部门名称',
    
    COUNT(*) as '个数' from BasicDepartment group by all DepartmentID,DepartmentName
    
      
    
    ==========================================================================================================
    
      
    
    --group by 和having 解释：前提必须了解sql语言中一种特殊的函数：聚合函数，
    
    例如SUM, COUNT, MAX, AVG等。这些函数和其它函数的根本区别就是它们一般作用在多条记录上。
    
      
    
    having是分组（group by）后的筛选条件，分组后的数据组内再筛选
    
    where则是在分组前筛选
    
      
    
    where子句中不能使用聚集函数，而having子句中可以，所以在集合函数中加上了HAVING来起到测试查询结果是否符合条件的作用。
    
    即having子句的适用场景是可以使用聚合函数
    
      
    
    having 子句限制的是组，而不是行
    
    having 子句中的每一个元素也必须出现在select列表中。有些数据库例外，如oracle
    
      
    
    当同时含有 where 子句、group by 子句 、having 子句及聚集函数时，执行顺序如下：
    
    执行where子句查找符合条件的数据；
    
    使用group by 子句对数据进行分组；对group by 子句形成的组运行聚集函数计算每一组的值；最后用having 子句去掉不符合条件的组
    
      
    
      ex:  显示每个地区的总人口数和总面积．仅显示那些面积超过1000000的地区。
    
        SELECT region, SUM(population), SUM(area)
    
        FROM bbc
    
        GROUP BY region
    
        HAVING SUM(area)>1000000
    
        在这里，我们不能用where来筛选超过1000000的地区，因为表中不存在这样一条记录。(where子句中不能使用聚集函数)
    
        相反，HAVING子句可以让我们筛选成组后的各组数据
    
      
    
      ex:create TABLE Table1
    
        (
    
          ID int identity(1,1) primary key NOT NULL,
    
          classid int,
    
          sex varchar(10),
    
          age int,
    
        )
    
        --添加测试多条数据
    
        Insert into Table1 values(1,'男',20)
    
        Insert into Table1 values(2,'女',22)
    
        Insert into Table1 values(3,'男',23)
    
        Insert into Table1 values(4,'男',22)
    
        Insert into Table1 values(1,'男',24)
    
        ..........
    
        查询每一个班级中年龄大于20，性别为男的人数至少为2个的情况
    
        select COUNT(age) as '>20岁人数',classid from Table1 where sex='男' group by classid having COUNT(age)>2
    
      
    
    底下这个例子很好
    
        SQL> select * from sc;
    
      
    
            SNO PNO    GRADE
    
          ---------- ----- ----------
    
    YW       95
    
    SX       98
    
    YY       90
    
    YW      89
    
    SX       91
    
    YY       92
    
    YW      85
    
    SX       88
    
    YY       96
    
    YW      95
    
    SX       89
    
    YY       88
    
      
    
        这个表所描述的是4个学生对应每科学习成绩的记录，其中SNO（学生号）、PNO（课程名）、GRADE（成绩）。
    
      
    
        1、显示90分以上学生的课程名和成绩
    
      
    
        //这是一个简单的查询，并没有使用分组查询
    
      
    
        SQL> select sno,pno,grade from sc where grade>=90;
    
      
    
            SNO PNO    GRADE
    
          ---------- ----- ----------
    
    YW      95
    
    SX       98
    
    YY       90
    
    SX       91
    
    YY       92
    
    YY       96
    
    YW      95
    
      
    
        2、显示每个学生的成绩在90分以上的各有多少门
    
      
    
        --进行分组显示，并且按照where条件之后计数
    
      
    
        SQL> select sno,count(*) from sc where grade>=90 group by sno;
    
      
    
            SNO  COUNT(*)
    
        -  --------- ----------
    
         3
    
         2
    
         1
    
         1
    
      
    
        3、这里我们并没有使用having语句，接下来如果我们要评选三好学生，条件是至少有两门课程在90分以上才能有资格，
    
          列出有资格的学生号及90分以上的课程数。
    
      
    
        //进行分组显示，并且按照where条件之后计数，在根据having子句筛选分组
    
      
    
        SQL> select sno,count(*) from sc where grade>=90 group by sno having count(*)>=2；
    
      
    
            SNO  COUNT(*)
    
        ---------- ----------
    
         3
    
         2
    
      
    
        这个结果是我们想要的，它列出了具有评选三好学生资格的学生号，跟上一个例子比较之后，发现这是在分组后进行的子查询。
    
      
    
        4、学校评选先进学生，要求平均成绩大于90分的学生都有资格，并且语文课必须在95分以上，请列出有资格的学生
    
      
    
        //实际上，这个查询先把语文大于95分的学生号提取出来，之后求平均值，分组显示后根据having语句选出平均成绩大于90的
    
      
    
        SQL> select sno,avg(grade) from sc where SNO IN (SELECT SNO FROM SC WHERE GRADE>=95 AND PNO='YW') group by sno having avg(grade)>=90;
    
      
    
            SNO AVG(GRADE)
    
        ---------- ----------
    
      94.3333333
    
      90.6666667
    
      
    
        5、查询比平均成绩至少比学号是3的平均成绩高的学生学号以及平均分数
    
      
    
        //having子句中可进行比较和子查询
    
        SQL> select sno,avg(grade) from sc
    
             group by sno
    
             having avg(grade) > (select avg(grade) from sc where sno=3);

–sql中的 where 、group by 和 having 用法解析

–如果要用到group by 一般用到的就是“每这个字” 例如说明现在有一个这样的表：每个部门有多少人 就要用到分组的技术

select DepartmentID as ‘部门名称’,COUNT(\*) as ‘个数’ from BasicDepartment group by DepartmentID

–这个就是使用了group by +字段 进行了分组，其中我们就可以理解为我们按照了部门的名称ID

–DepartmentID将数据集进行了分组；然后再进行各个组的统计数据分别有多少；

–如果不用count(\*) 而用类似下面的语法

select DepartmentID,DepartmentName from BasicDepartment group by DepartmentID

–将会出现错误

–选择列表中的列 ‘BasicDepartment.DepartmentName’ 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。

这就是我们需要注意的一点，如果在返回集字段中，这些字段

要么就要包含在Group By语句的后面,作为分组的依据；

要么就要被包含在聚合函数中,作为分组的依据；

–出现的错误详解:咱们看看group by 的执行的过程,先执行select 的操作返回一个程序集，

–然后去执行分组的操作，这时候他将根据group by 后面的字段

–进行分组，并且将相同的字段并称一列数据，如果group by 后面没有这个字段的话就要分成好多的数据。

–但是分组就只能将相同的数据分成两列数据，而一列中又只能放入一个字段，所以那些没有进行分组的

–数据系统不知道将数据放入哪里，所以就出现此错误

–目前一种分组情况只有一条记录，一个数据格是无法放入多个数值的，

–所以这里就需要通过一定的处理将这些多值的列转化成单值，然后将其放在对应的

–数据格中，那么完成这个步骤的就是聚合函数。这就是为什么这些函数叫聚合函数(aggregate functions)了

–group by all语法解析：

–如果使用 ALL 关键字，那么查询结果将包括由 GROUP BY 子句产生的所有组，即使某些组没有符合搜索条件的行。

–没有 ALL 关键字，包含 GROUP BY 子句的 SELECT 语句将不显示没有符合条件的行的组。

select DepartmentID,DepartmentName as ‘部门名称’,

COUNT(\*) as ‘个数’ from BasicDepartment group by all DepartmentID,DepartmentName

==========================================================================================================

–group by 和having 解释：前提必须了解sql语言中一种特殊的函数：聚合函数，

例如SUM, COUNT, MAX, AVG等。这些函数和其它函数的根本区别就是它们一般作用在多条记录上。

having是分组（group by）后的筛选条件，分组后的数据组内再筛选

where则是在分组前筛选

where子句中不能使用聚集函数，而having子句中可以，所以在集合函数中加上了HAVING来起到测试查询结果是否符合条件的作用。

即having子句的适用场景是可以使用聚合函数

having 子句限制的是组，而不是行

having 子句中的每一个元素也必须出现在select列表中。有些数据库例外，如oracle

当同时含有 where 子句、group by 子句 、having 子句及聚集函数时，执行顺序如下：

执行where子句查找符合条件的数据；

使用group by 子句对数据进行分组；对group by 子句形成的组运行聚集函数计算每一组的值；最后用having 子句去掉不符合条件的组

ex: 显示每个地区的总人口数和总面积．仅显示那些面积超过1000000的地区。

SELECT region, SUM(population), SUM(area)
    
    FROM bbc
    
    GROUP BY region
    
    HAVING SUM(area)>1000000
    
    在这里，我们不能用where来筛选超过1000000的地区，因为表中不存在这样一条记录。(where子句中不能使用聚集函数)
    
    相反，HAVING子句可以让我们筛选成组后的各组数据

ex:create TABLE Table1

(
    
      ID int identity(1,1) primary key NOT NULL,
    
      classid int,
    
      sex varchar(10),
    
      age int,
    
    )
    
    --添加测试多条数据
    
    Insert into Table1 values(1,'男',20)
    
    Insert into Table1 values(2,'女',22)
    
    Insert into Table1 values(3,'男',23)
    
    Insert into Table1 values(4,'男',22)
    
    Insert into Table1 values(1,'男',24)
    
    ..........
    
    查询每一个班级中年龄大于20，性别为男的人数至少为2个的情况
    
    select COUNT(age) as '>20岁人数',classid from Table1 where sex='男' group by classid having COUNT(age)>2

底下这个例子很好

SQL> select * from sc;
    
    
    
        SNO PNO    GRADE
    
      ---------- ----- ----------

YW 95

SX 98

YY 90

YW 89

SX 91

YY 92

YW 85

SX 88

YY 96

YW 95

SX 89

YY 88

这个表所描述的是4个学生对应每科学习成绩的记录，其中SNO（学生号）、PNO（课程名）、GRADE（成绩）。
    
    
    
    1、显示90分以上学生的课程名和成绩
    
    
    
    //这是一个简单的查询，并没有使用分组查询
    
    
    
    SQL> select sno,pno,grade from sc where grade>=90;
    
    
    
        SNO PNO    GRADE
    
      ---------- ----- ----------

YW 95

SX 98

YY 90

SX 91

YY 92

YY 96

YW 95

2、显示每个学生的成绩在90分以上的各有多少门
    
    
    
    --进行分组显示，并且按照where条件之后计数
    
    
    
    SQL> select sno,count(*) from sc where grade>=90 group by sno;
    
    
    
        SNO  COUNT(*)
    
    -  --------- ----------
    
     3
    
     2
    
     1
    
     1
    
    
    
    3、这里我们并没有使用having语句，接下来如果我们要评选三好学生，条件是至少有两门课程在90分以上才能有资格，
    
      列出有资格的学生号及90分以上的课程数。
    
    
    
    //进行分组显示，并且按照where条件之后计数，在根据having子句筛选分组
    
    
    
    SQL> select sno,count(*) from sc where grade>=90 group by sno having count(*)>=2；
    
    
    
        SNO  COUNT(*)
    
    ---------- ----------
    
     3
    
     2
    
    
    
    这个结果是我们想要的，它列出了具有评选三好学生资格的学生号，跟上一个例子比较之后，发现这是在分组后进行的子查询。
    
    
    
    4、学校评选先进学生，要求平均成绩大于90分的学生都有资格，并且语文课必须在95分以上，请列出有资格的学生
    
    
    
    //实际上，这个查询先把语文大于95分的学生号提取出来，之后求平均值，分组显示后根据having语句选出平均成绩大于90的
    
    
    
    SQL> select sno,avg(grade) from sc where SNO IN (SELECT SNO FROM SC WHERE GRADE>=95 AND PNO='YW') group by sno having avg(grade)>=90;
    
    
    
        SNO AVG(GRADE)
    
    ---------- ----------

94.3333333

90.6666667

5、查询比平均成绩至少比学号是3的平均成绩高的学生学号以及平均分数
    
    
    
    //having子句中可进行比较和子查询
    
    SQL> select sno,avg(grade) from sc
    
         group by sno
    
         having avg(grade) > (select avg(grade) from sc where sno=3);

–sql中的 where 、group by 和 having 用法解析

–如果要用到group by 一般用到的就是“每这个字” 例如说明现在有一个这样的表：每个部门有多少人 就要用到分组的技术

select DepartmentID as ‘部门名称’,COUNT(\*) as ‘个数’ from BasicDepartment group by DepartmentID

–这个就是使用了group by +字段 进行了分组，其中我们就可以理解为我们按照了部门的名称ID

–DepartmentID将数据集进行了分组；然后再进行各个组的统计数据分别有多少；

–如果不用count(\*) 而用类似下面的语法

select DepartmentID,DepartmentName from BasicDepartment group by DepartmentID

–将会出现错误

–选择列表中的列 ‘BasicDepartment.DepartmentName’ 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。

这就是我们需要注意的一点，如果在返回集字段中，这些字段

要么就要包含在Group By语句的后面,作为分组的依据；

要么就要被包含在聚合函数中,作为分组的依据；