mysql(13章) - 分组数据

2024-08-28 本文已影响0人足__迹

select 子句顺序

子句	说明	是否必须使用
SELECT	要返回的列或表达式	是
FROM	从中检索数据的表	仅在从表数据时使用
WHERE	行级过滤
GROUP BY	分组说明	仅在按组计算聚集时使用
HAVING	组级过滤	否
order BY	输出排序顺序	否
LIMIT	要检索行数	否

GROUP BY（分组）

举例：根据患者性别分组，查询患者姓名
1，输入

SELECT p.gender,AVG(DISTINCT p.age),MIN(p.age),MAX(p.age)  FROM patient p 
GROUP BY p.gender

2，输出：

image.png

3,分析：

上面使用SELECT查询，平均年龄，最大年龄，最小年龄，GROUPBY子句指示MySQL按gender排序并分组数据，这导致对每种gender 分组输出。

注意点：

GROUPBY子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制。
如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）。
GROUP BY子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。
除聚集计算语句外，SELECT语句中的每个列都必须在GROUPBY子句中给出。
如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。
GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

小技巧

使用ROLLUP使用WITHROLLUP关键字，可以得到每个分组以及每个分组汇总级别（针对每个分组）的值，如下所示：
SELECT p.gender,p.drugstore_code,AVG(DISTINCT p.age),MIN(p.age),MAX(p.age) FROM patient p
GROUP BY p.gender,p.drugstore_code WITH ROLLUP

过滤分组

在MYsql中，WHERE过滤指定的是行而不是分组。MySQL为此目的提供了另外的子句，那就是HAVING子句。HAVING非常类似于WHERE。事实上，目前为止所学过的所有类型的WHERE子句都可以用HAVING来替代。唯一的差别是WHERE过滤行，而HAVING过滤分组。
举例：增加分组数值筛选

输入：

SELECT p.gender,p.drugstore_code,AVG(DISTINCT p.age),MIN(p.age),MAX(p.age)  FROM patient p 
GROUP BY p.gender,p.drugstore_code  WITH ROLLUP 
HAVING AVG(DISTINCT p.age)>0 and MIN(p.age)>0 and MAX(p.age)>0

输出：

image.png
分析：

最后一行增加了HAVING子句，过滤了数值小于0 的数值

HAVING与Where 的差别：

HAVING和WHERE的差别这里有另一种理解方法，WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。这是一个重要的区别，WHERE排除的行不包括在分组中。这可能会改变计算值，从而影响HAVING子句中基于这些值过滤掉的分组。

举例：增加筛选，筛选出已删除的数据

输入：

SELECT p.gender,p.drugstore_code,AVG(DISTINCT p.age),MIN(p.age),MAX(p.age)  FROM patient p 
WHERE p.deleted_flag =0
GROUP BY p.gender,p.drugstore_code 
HAVING AVG(DISTINCT p.age)>0 and MIN(p.age)>0 and MAX(p.age)>0

输出：

image.png

分组与排序

ORDER BY	GROUP BY
排序产生的输出	分租行，但输出可能不是分租的顺序
任意列都可以使用（甚至非选择的列也可以使用）	只可能使用选择列或者表达式列。而且必须使用每个选择表达式
不一定需要	如果与聚集函数一起使用（或表达式），泽必须使用

举例：增加排序

1.输入：

SELECT p.gender,p.drugstore_code,AVG(DISTINCT p.age)FROM patient p 
WHERE p.deleted_flag =1 and p.drugstore_code ='E00068'
GROUP BY p.gender,p.drugstore_code 
HAVING AVG(DISTINCT p.age)>0
ORDER BY AVG(DISTINCT p.age) desc

2.输出