SQLServer之聚合函数

聚合函数

SQL SERVER中聚合函数主要有：

count:求数量
max:求最大值
min:求最小值
sum:求和
avg:求平均值
注意：where里不能用这些条件

一、聚合函数举例应用

（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