07-SQLSERVER聚合函数

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

时间格式控制字符串：