Select

select age,count(*),max(age) as 最大年龄 from person1 
group by Age 
having Age>20

select top 3 * from person1

--筛选出年龄不在top3中的数据，也就是第四、五
select top 2 * from person1 where age not in(
select top 3 age from person1 order by age desc)
order by age 

--去掉数据重复
select Department,count(*) from person1
group by Department

select distinct Department,Age from person1

--联合查询
select Name,Age,Department,1 from person1
union
select Name,Age,'无部门',2 from person4
--不会消除重量行,尽量不要丢掉all
select Age from person1
union all
select Age from person4
--查询person1和person4的最高年龄和最低年龄
select 'person1' as 人名,max(Age) as 最高年龄,min(Age)as 最低年龄 from person1
union all
select 'person4',max(Age),min(Age) from person4

--查询person1所有人的年龄和年龄总和
select Name,Age from person1
union all
select '年龄总和',sum(Age) from person1

--sql中常用的函数
select ABS(-3)
select ceiling(-3.12)
select floor(-3.12)
select round(-3.1223,4)
select LEN('abc')
select lower('ABC')
select len(ltrim(rtrim(('  a   b   c   d'))))
select len(ltrim(rtrim(('  bb  '))))
select substring('abc234',2,5)
select getdate()
select dateadd(day,-8,getdate())
select datediff(mm,'2012-9-9','2015-9-9')
select datediff(yy,'2000',getdate())
select datepart(hh,getdate())
--数据类型转换
select cast('20080808' as datetime)
select convert(datetime,'20080808')
select '1234'+1
select convert(int,'1234')+1
select datepart(yy,convert(datetime,'20080808'))

--统计员工工领
select datediff(yy,InDate,getdate()) as 员工工领,count(*) as 人数
from person1
group by datediff(yy,InDate,getdate())
order by datediff(yy,InDate,getdate()) desc
--统计每年入职人数
select datepart(yy,InDate) as 入职年份,count(*) as 人数
from person1
group by datepart(yy,InDate)
order by datepart(yy,InDate) desc

--ISNULL 函数
select isnull(name,'姓名未知') as 姓名 from person1

--CASE 函数
select Name,(
case 
when Age<20 then '90后'
when Age>=20 and Age<30 then '80后'
else '00后'
end
) as '哪一代'
from person1

--选择较最大的一列
select 
(case when A>B then A else B end) as AB列中的较大,
(case when A>C then A else C end) as AB列中的较大, 
(case when B>C then B else C end) as BC列中的较大
from person1  

--球赛比分,首先把胜负分开成两列,然后分组统计胜负得分
select name,
(case result when '胜' then 1 else 0 end )as 胜,
(case result when '负' then 1 else 0 end) as 负
from person1 

select name,sum(case result when '胜' then 1 else 0 end )as 胜,sum(case result when '负' then 1 else 0 end) as 负
from person1 
group by name
--收支统计
select name,
(case  when pay>0 then pay else 0 end
)as 收入,
(case  when pay<0 then ABS(pay) else 0 end) as 支出
from person1