SQL语句之group by 和having

最新推荐文章于 2022-04-28 12:17:05 发布

huangleijay

最新推荐文章于 2022-04-28 12:17:05 发布

阅读量712

点赞数

CC 4.0 BY-SA版权

分类专栏： ORACLE MYSQL学习

本文链接：https://blog.youkuaiyun.com/huangleijay/article/details/24482869

ORACLE MYSQL学习专栏收录该内容

10 篇文章

订阅专栏

首先看一个经典的问题和回答：

select item.itemnum,item.in1,item.in4,inventory.location from item,inventory
where item.itemnum=inventory.itemnum
and inventory.location='DYB'
and item.in1='D/MTD/MRM'
GROUP BY ITEM.ITEMNUM
提示错误是NOT A GROUP BY EXPRESSION
回答：

GROUP BY 是分组查询, 一般 GROUP BY是和聚合函数配合使用,你可以想想
你用了GROUP BY 按 ITEM.ITEMNUM 这个字段分组,那其他字段内容不同,变成一对多又改如何显示呢,比如下面所示
A B
1 abc
1 bcd
1 asdfg
select A,B from table group by A
你说这样查出来是什么结果,
A B
abc
1 bcd
asdfg

右边3条如何变成一条,所以需要用到聚合函数,比如
select A,count(B) 数量 from table group by A
这样的结果就是
A 数量
1 3

group by 有一个原则,就是 select 后面的所有列中,没有使用聚合函数的列,必须出现在 group by 后面！！

这上面这个非常的浅显易懂，那么我们进一步分析，来看个难的：

--如果不用count(*) 而用类似下面的语法(这个错误还是和上面的是一样的原理，大家应该很清楚)
select DepartmentID,DepartmentName from BasicDepartment group by DepartmentID

--将会出现错误
--消息 8120，级别 16，状态 1，第 1 行
--选择列表中的列 'BasicDepartment.DepartmentName' 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。
--这就是我们需要注意的一点，如果在返回集字段中，这些字段要么就要包含在Group By语句的后面，作为分组的依据；要么就要被包含在聚合函数中。(反正不能一对多的情况出现：因为一个组(比如，组1)只能占一行，组内多个成员的其他属性肯定是有不同的，这个不同不能通过这一行完全体现出来，系统做不到所以报错)
--出现的错误详解:咱们看看group by 的执行的过程,先执行select 的操作返回一个程序集(步骤1)，

--然后去执行分组的操作，这时候他将根据group by 后面的字段进行分组，并且将相同的字段并成一列数据(步骤2)，如果group by 后面没有这个字段的话就要分成好多的数据(因为没给其分组，所以每个人就相当于一组)。但是分组就只能将相同的数据分成一列数据，而一列中又只能放入一个字段，所以那些没有进行分组的
数据系统不知道将数据放入哪里，所以就出现此错误(跟我上面说的同理)
--目前一种分组情况只有一条记录，一个数据格是无法放入多个数值的，所以这里就需要通过一定的处理将这些多值的列转化成单值，然后将其放在对应的数据格中，那么完成这个步骤的就是聚合函数(就理解成数学函数，一般有：SUM 求组内此字段值的和, COUNT 某字段个数, MAX 某字段最大值, AVG 某字段平均值)。这就是为什么这些函数叫聚合函数(aggregate functions)了(意思大概是：将多值聚合成一个，聚合后能放到一个单元格中，一个单元格就是一个字段了，可以和group by字段一一对应，不再一对多了)

--group by all语法解析：
--如果使用 ALL 关键字，那么查询结果将包括由 GROUP BY 子句产生的所有组，即使某些组没有符合搜索条件的行 (字段将为null)。没有 ALL 关键字，包含 GROUP BY 子句的 SELECT 语句将不显示没有符合条件的行的组。
USE AdventureWorks;
GO
SELECT ProductID, AVG(UnitPrice) AS 'Average Price'
FROM Sales.SalesOrderDetail
WHERE OrderQty > 10
GROUP BY ProductID
ORDER BY ProductID ;
GO
结果：

ProductID   Average Price
707         20.7765
708         20.7482
709         6.175
711         20.7285
712         5.7592
714         32.05
.............................
(114 row(s) affected)

-- Using GROUP BY ALL
USE AdventureWorks;
GO
SELECT ProductID, AVG(UnitPrice) AS 'Average Price'
FROM Sales.SalesOrderDetail
WHERE OrderQty > 10
GROUP BY ALL ProductID
ORDER BY ProductID ;
GO

结果对于没有符合条件的行的组，包含聚合值（平均价格）的列为 NULL

ProductID   Average Price
707         20.7765
708         20.7482
709         6.175
710         NULL
711         20.7285
712         5.7592
..............................
Warning: Null value is eliminated by an aggregate or other SET operation.

(266 row(s) affected)

--group by 和having 解释：前提必须了解sql语言中一种特殊的函数：聚合函数，
--例如SUM, COUNT, MAX, AVG等。这些函数和其它函数的根本区别就是它们一般作用在多条记录上。
--WHERE关键字在使用集合函数时不能使用，所以在集合函数中加上了HAVING来起到测试查询结果是否符合条件的作用。
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)
    Insert into Table1 values(2,'女',19)
    Insert into Table1 values(4,'男',26)
    Insert into Table1 values(1,'男',24)
    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)
    Insert into Table1 values(2,'女',19

--举例子说明：查询table表查询每一个班级中年龄大于20，性别为男的人数
select COUNT(*)as '>20岁人数',classid from Table1 where sex='男' group by classid,age having age>20
--需要注意说明：当同时含有where子句、group by 子句、having子句及聚集函数时，执行顺序如下：
--执行where子句查找符合条件的数据；
--使用group by 子句对数据进行分组；对group by 子句形成的组运行聚集函数计算每一组的值；最后用having 子句去掉不符合条件的组。
--having 子句中的每一个元素也必须出现在select列表中。有些数据库例外，如oracle.
--having子句和where子句都可以用来设定限制条件以使查询结果满足一定的条件限制。
--having子句限制的是组，而不是行。where子句中不能使用聚集函数，而having子句中可以。

sqlserver 官网上的一句话：HAVING 子句对 GROUP BY 子句设置条件的方式与 WHERE 和 SELECT 的交互方式类似。WHERE 搜索条件在进行分组操作之前应用；而 HAVING 搜索条件在进行分组操作之后应用。HAVING 语法与 WHERE 语法类似，但 HAVING 可以包含聚合函数。HAVING 子句可以引用选择列表中显示的任意项。

下面的示例按产品 ID 对 SalesOrderDetail 进行了分组，并且只包含那些订单合计大于 $1,000,000 且其平均订单数量小于 3 的产品组。

复制

USE AdventureWorks;
GO
SELECT ProductID, AVG(OrderQty) AS AverageQuantity, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
GROUP BY ProductID
HAVING SUM(LineTotal) > $1000000.00
AND AVG(OrderQty) < 3 ;
GO

请注意，如果 HAVING 中包含多个条件，那么这些条件将通过 AND、OR 或 NOT 组合在一起。

若要查看总销量大于 $2,000,000 的产品，请使用下面的查询：

复制

USE AdventureWorks;
GO
SELECT ProductID, Total = SUM(LineTotal)
FROM Sales.SalesOrderDetail
GROUP BY ProductID
HAVING SUM(LineTotal) > $2000000.00 ;
GO

下面是结果集：

复制

ProductID   Total
----------- ----------------------
781         3864606.54937208
969         2010943.97244001
793         2897478.01200001
784         3699803.72383008
780         3880441.60780208
976         2079038.42948
795         2268057.09000002
783         4548164.01783709
779         4170215.3849281
782         5032968.13026809
794         2679200.01336002
753         2006264.4236

(12 row(s) affected)

若要确保对每种产品的计算中至少包含 1500 项，请使用 HAVING COUNT(*) > 1500 消除返回的销售总数小于 1500 项的产品。该查询类似于下面的示例：

复制

USE AdventureWorks;
GO
SELECT ProductID, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
GROUP BY ProductID
HAVING COUNT(*) > 1500 ;
GO

理解应用 WHERE、GROUP BY 和 HAVING 子句的正确顺序对编写高效的查询代码会有所帮助：

WHERE 子句用来筛选 FROM 子句中指定的操作所产生的行。
GROUP BY 子句用来分组 WHERE 子句的输出。
HAVING 子句用来从分组的结果中筛选行。

对于可以在分组操作之前或之后应用的任何搜索条件，在 WHERE 子句中指定它们会更有效。这样可以减少必须分组的行数。应当在 HAVING 子句中指定的搜索条件只是那些必须在执行分组操作之后应用的搜索条件。

Microsoft SQL Server 2005 查询优化器可以处理这些条件中的大多数条件。如果查询优化器确定 HAVING 搜索条件可以在分组操作之前应用，那么它就会在分组之前应用。查询优化器可能无法识别所有可以在分组操作之前应用的 HAVING 搜索条件。建议将所有这些搜索条件放在 WHERE 子句中，而不是 HAVING 子句中。

下面的示例显示了带有聚合函数的 HAVING 子句。它按产品 ID 分组 SalesOrderDetail 表中的行，并消除其平均订单数量小于/等于 5 的产品。

复制

USE AdventureWorks;
GO
SELECT ProductID 
FROM Sales.SalesOrderDetail
GROUP BY ProductID
HAVING AVG(OrderQty) > 5
ORDER BY ProductID ;
GO

下面的示例显示了不带聚合函数的 HAVING 子句。它按名称分组 ProductModel 表中的行，并消除那些不以 Mountain 开头的名称。

复制

USE AdventureWorks;
GO
SELECT pm.Name, AVG(ListPrice) AS 'Average List Price'
FROM Production.Product AS p
JOIN Production.ProductModel AS pm
ON p.ProductModelID = pm.ProductModelID
GROUP BY pm.Name
HAVING pm.Name LIKE 'Mountain%'
ORDER BY pm.Name ;
GO

请注意，ORDER BY 子句可用于排序 GROUP BY 子句的输出。