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

--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);

SELECT region, SUM(population), SUM(area)

FROM bbc

GROUP BY region

HAVING SUM(area)>1000000

在这里，我们不能用where来筛选超过1000000的地区，因为表中不存在这样一条记录。(where子句中不能使用聚集函数)

相反，HAVING子句可以让我们筛选成组后的各组数据

(

  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

  ---------- ----- ----------

这个表所描述的是4个学生对应每科学习成绩的记录，其中SNO（学生号）、PNO（课程名）、GRADE（成绩）。



1、显示90分以上学生的课程名和成绩



//这是一个简单的查询，并没有使用分组查询



SQL> select sno,pno,grade from sc where grade>=90;



    SNO PNO    GRADE

  ---------- ----- ----------

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)

---------- ----------

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);

SELECT region, SUM(population), SUM(area)

FROM bbc

GROUP BY region

HAVING SUM(area)>1000000

在这里，我们不能用where来筛选超过1000000的地区，因为表中不存在这样一条记录。(where子句中不能使用聚集函数)

相反，HAVING子句可以让我们筛选成组后的各组数据

(

  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

  ---------- ----- ----------

这个表所描述的是4个学生对应每科学习成绩的记录，其中SNO（学生号）、PNO（课程名）、GRADE（成绩）。



1、显示90分以上学生的课程名和成绩



//这是一个简单的查询，并没有使用分组查询



SQL> select sno,pno,grade from sc where grade>=90;



    SNO PNO    GRADE

  ---------- ----- ----------

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)

---------- ----------

5、查询比平均成绩至少比学号是3的平均成绩高的学生学号以及平均分数



//having子句中可进行比较和子查询

SQL> select sno,avg(grade) from sc

     group by sno

     having avg(grade) > (select avg(grade) from sc where sno=3);