SQL聚集函数
2019-08-13 08:31:48 14 举报
AI智能生成
SQL聚集函数
作者其他创作
大纲/内容
分类
COUNT()
MAX()
MIN()
SUM()
AVG()
示例
SELECT COUNT(*)
FROM heros
WHERE hp_max > 6000;
生命值大于6000且有次要定位的英雄数量
SELECT COUNT(role_assist)
FROM heros
WHERE hp_max > 6000;
SELECT MAX(hp_max)
FROM heros
WHERE role_main = '射手' or role_assist = ‘射手’;
针对不同数据进行函数统计
SELECT COUNT(DISTINCT hp_max)
FROM heros;
SELECT ROUNT(AVG(DISTINCT hp_max), 2)
FROM heros;
FROM heros
WHERE hp_max > 6000;
生命值大于6000且有次要定位的英雄数量
SELECT COUNT(role_assist)
FROM heros
WHERE hp_max > 6000;
SELECT MAX(hp_max)
FROM heros
WHERE role_main = '射手' or role_assist = ‘射手’;
针对不同数据进行函数统计
SELECT COUNT(DISTINCT hp_max)
FROM heros;
SELECT ROUNT(AVG(DISTINCT hp_max), 2)
FROM heros;
如何分组,并进行聚集统计
使用GROUP BY进行分组
示例
按照英雄的主要定位进行分组,并统计每组英雄的数量
SELECT COUNT(*), role_main
FROM heros
GROUP BY role_main;
按照英雄的次要定位进行分组,并统计每组英雄的数量
SELECT COUNT(*), role_assist
FROM heros
GROUP BY role_assist;
SELECT COUNT(*) as num, role_main, role_assist
FROM heros
GROUP BY role_main, role_assist
ORDER BY num DESC
SELECT COUNT(*), role_main
FROM heros
GROUP BY role_main;
按照英雄的次要定位进行分组,并统计每组英雄的数量
SELECT COUNT(*), role_assist
FROM heros
GROUP BY role_assist;
SELECT COUNT(*) as num, role_main, role_assist
FROM heros
GROUP BY role_main, role_assist
ORDER BY num DESC
HAVING vs WHERE
HAVING
作用于分组
支持所有WHERE的操作
WHERE
用于数据行的过滤
示例
先对hp_max大于6000的英雄进行过滤,然后和上例一样操作
SELECT COUNT(*) as num, role_main, role_assist
FROM heros
WHERR hp_max > 6000
GROUP BY role_main, role_assist
HAVING num > 5
ORDER BY num DESC
SELECT COUNT(*) as num, role_main, role_assist
FROM heros
WHERR hp_max > 6000
GROUP BY role_main, role_assist
HAVING num > 5
ORDER BY num DESC
按照英雄的主要定位,次要定位进行分组,并且筛选分组中英雄数量大于5的组
最后按照分组中的英雄数量从高到低进行排序
SELECT COUNT(*) as num, role_main, role_assist
FROM heros
GROUP BY role_main, role_assist
HAVING num > 5
ORDER BY num DESC
最后按照分组中的英雄数量从高到低进行排序
SELECT COUNT(*) as num, role_main, role_assist
FROM heros
GROUP BY role_main, role_assist
HAVING num > 5
ORDER BY num DESC
概念
对一组数据进行汇总
输入一组->输出单个值
SQL语句顺序
WHERE: 数据量过滤
GROUP BY: 进行分组
HAVING: 进行分组过滤
ORDER BY: 进行排序
练习
筛选最大生命值大于6000的英雄,按照主要定位进行分组
选择分组英雄数量大于5的分组,按照分组英雄从高到低进行排序
并显示每个分组的英雄数量,主要定位和平均最大生命值
SELECT COUNT(*) an num, role_main, AVG(hp_max)
FROM heros
WHERE hp_max > 6000
GROUP BY role_main
HAVING num > 5
ORDER BY num DESC
筛选最大生命值与最大法力值之和大于7000的英雄
按照攻击范围来进行分组,显示分组的英雄数量,
以及分组英雄的最大生命值与法力值之和的平均值,最大值和最小值,
并按照分组英雄数从高到低进行排序,其中聚集函数的结果包括小数点后两位
SELECT COUNT(*) as num, ROUND(AVG(hp_max, mp_max),2), ROUND(MAX(hp_max+mp_max),2), ROUND(MIN(hp_max, mp_max),2)
FROM heros
WHERE hp_max+mp_max > 7000
GROUP BY attck_range
ORDER BY num DESC
选择分组英雄数量大于5的分组,按照分组英雄从高到低进行排序
并显示每个分组的英雄数量,主要定位和平均最大生命值
SELECT COUNT(*) an num, role_main, AVG(hp_max)
FROM heros
WHERE hp_max > 6000
GROUP BY role_main
HAVING num > 5
ORDER BY num DESC
筛选最大生命值与最大法力值之和大于7000的英雄
按照攻击范围来进行分组,显示分组的英雄数量,
以及分组英雄的最大生命值与法力值之和的平均值,最大值和最小值,
并按照分组英雄数从高到低进行排序,其中聚集函数的结果包括小数点后两位
SELECT COUNT(*) as num, ROUND(AVG(hp_max, mp_max),2), ROUND(MAX(hp_max+mp_max),2), ROUND(MIN(hp_max, mp_max),2)
FROM heros
WHERE hp_max+mp_max > 7000
GROUP BY attck_range
ORDER BY num DESC
0 条评论
下一页