SQL SERVER中聚合函数主要有:
count:求数量
max:求最大值
min:求最小值
sum:求和
avg:求平均值
一、聚合函数举例应用
(1)求员工总人数
select COUNT(*) 数量 from People
(2)求最大值,求最高工资
select MAX(PeopleSalary) 最高工资 from People
(3)求最小时,求最小工资
select MIN(PeopleSalary) 最低工资 from People
(4)求和,求所有员工的工资总和
select SUM(PeopleSalary) 工资总和 from People
(5)求平均值,求所有员工的平均工资
--方案一:
select AVG(PeopleSalary) 平均工资 from People
--方案二:精确到2位小数
select ROUND(AVG(PeopleSalary),2) 平均工资 from People
--方案三:精确到2位小数
select Convert(decimal(12,2),AVG(PeopleSalary)) 平均工资 from People
ROUND函数用法:
round(num,len,[type])
其中:
num表示需要处理的数字,len表示需要保留的长度,type处理类型(0是默认值代表四舍五入,非0代表直接截取)
select ROUND(123.45454,3) --123.45500
select ROUND(123.45454,3,1) --123.45400
(6)求数量,最大值,最小值,总和,平均值,在一行显示
select COUNT(*) 数量,MAX(PeopleSalary) 最高工资,MIN(PeopleSalary) 最低工资,SUM(PeopleSalary) 工资总和,AVG(PeopleSalary) 平均工资 from People
(7)查询出武汉地区的员工人数,总工资,最高工资,最低工资和平均工资
select '武汉' 地区,COUNT(*) 数量,MAX(PeopleSalary) 最高工资,MIN(PeopleSalary) 最低工资
,SUM(PeopleSalary) 工资总和,AVG(PeopleSalary) 平均工资 from People
WHERE PEOPLEADDRESS = '武汉'
(8)求出工资比平均工资高的人员信息
select * from People where PeopleSalary > (select AVG(PeopleSalary) 平均工资 from People)
(9)求数量,年龄最大值,年龄最小值,年龄总和,年龄平均值,在一行显示
--方案一:
select COUNT(*) 数量,
MAX(year(getdate())-year(PeopleBirth)) 最高年龄,
MIN(year(getdate())-year(PeopleBirth)) 最低年龄,
SUM(year(getdate())-year(PeopleBirth)) 年龄总和,
AVG(year(getdate())-year(PeopleBirth)) 平均年龄
from People
--方案二:
select COUNT(*) 数量,
MAX(DATEDIFF(year, PeopleBirth, getDate())) 最高年龄,
MIN(DATEDIFF(year, PeopleBirth, getDate())) 最低年龄,
SUM(DATEDIFF(year, PeopleBirth, getDate())) 年龄总和,
AVG(DATEDIFF(year, PeopleBirth, getDate())) 平均年龄
from People
(10)计算出月薪在10000 以上的男性员工的最大年龄,最小年龄和平均年龄
--方案一:
select '男' 性别,COUNT(*) 数量,
MAX(year(getdate())-year(PeopleBirth)) 最高年龄,
MIN(year(getdate())-year(PeopleBirth)) 最低年龄,
SUM(year(getdate())-year(PeopleBirth)) 年龄总和,
AVG(year(getdate())-year(PeopleBirth)) 平均年龄
from People where PeopleSex = '男' and PeopleSalary >= 10000
--方案二:
select '男' 性别,COUNT(*) 数量,
MAX(DATEDIFF(year, PeopleBirth, getDate())) 最高年龄,
MIN(DATEDIFF(year, PeopleBirth, getDate())) 最低年龄,
SUM(DATEDIFF(year, PeopleBirth, getDate())) 年龄总和,
AVG(DATEDIFF(year, PeopleBirth, getDate())) 平均年龄
from People where PeopleSex = '男' and PeopleSalary >= 10000
(11)统计出所在地在“武汉或上海”的所有女员工数量以及最大年龄,最小年龄和平均年龄
--方案一:
select '武汉或上海' 地区,'女' 性别,COUNT(*) 数量,
MAX(year(getdate())-year(PeopleBirth)) 最高年龄,
MIN(year(getdate())-year(PeopleBirth)) 最低年龄,
SUM(year(getdate())-year(PeopleBirth)) 年龄总和,
AVG(year(getdate())-year(PeopleBirth)) 平均年龄
from People where PeopleSex = '女' and PeopleAddress in('武汉','上海')
--方案二:
select '武汉或上海' 地区,'女' 性别,COUNT(*) 数量,
MAX(DATEDIFF(year, PeopleBirth, getDate())) 最高年龄,
MIN(DATEDIFF(year, PeopleBirth, getDate())) 最低年龄,
SUM(DATEDIFF(year, PeopleBirth, getDate())) 年龄总和,
AVG(DATEDIFF(year, PeopleBirth, getDate())) 平均年龄
from People where PeopleSex = '女' and PeopleAddress in('武汉','上海')
(12)求出年龄比平均年龄高的人员信息
--方案一:
select * from People where
year(getdate())-year(PeopleBirth) >
(select AVG(year(getdate())-year(PeopleBirth))
from People)
--方案二:
select * from People where
DATEDIFF(year, PeopleBirth, getDate()) >
(select AVG(DATEDIFF(year, PeopleBirth, getDate()))
from People)
二、补充-SQL中常用时间处理函数
GETDATE() 返回当前的日期和时间
DATEPART() 返回日期/时间的单独部分
DATEADD() 返回日期中添加或减去指定的时间间隔
DATEDIFF() 返回两个日期直接的时间
DATENAME() 返回指定日期的指定日期部分的整数
CONVERT() 返回不同格式的时间
示例:
select DATEDIFF(day, '2019-08-20', getDate()); --获取指定时间单位的差值
SELECT DATEADD(MINUTE,-5,GETDATE()) --加减时间,此处为获取五分钟前的时间,MINUTE 表示分钟,可为 YEAR,MONTH,DAY,HOUR
select DATENAME(month, getDate()); --当前月份
select DATENAME(WEEKDAY, getDate()); --当前星期几
select DATEPART(month, getDate()); --当前月份
select DAY(getDate()); --返回当前日期天数
select MONTH(getDate()); --返回当前日期月数
select YEAR(getDate()); --返回当前日期年数
SELECT CONVERT(VARCHAR(22),GETDATE(),20) --2020-01-09 14:46:46
SELECT CONVERT(VARCHAR(24),GETDATE(),21) --2020-01-09 14:46:55.91
SELECT CONVERT(VARCHAR(22),GETDATE(),23) --2020-01-09
SELECT CONVERT(VARCHAR(22),GETDATE(),24) --15:04:07
Select CONVERT(varchar(20),GETDATE(),14) --15:05:49:330
时间格式控制字符串:
名称 | 日期单位 | 缩写 |
---|---|---|
年 | year | yyyy 或yy |
季度 | quarter | qq,q |
月 | month | mm,m |
一年中第几天 | dayofyear | dy,y |
日 | day | dd,d |
一年中第几周 | week | wk,ww |
星期 | weekday | dw |
小时 | Hour | hh |
分钟 | minute | mi,n |
秒 | second | ss,s |
毫秒 | millisecond | ms |