sql server code week14

--------------------------------------------------------------------------------------------------- 使用数据库use AdventureWorks---------如何使用排名函数------- 出现在 select 后面----------- row_number()-- 返回有序数字-- rank() -- 出现了并列的现象可是生成的不是有序数字-- dense_rank() -- 不但出现了并列而且生成了有序数字-- ntile(n) -- 给表的数据进行分组 ntile(组的个数)--------------------  over  -------------------- row_number()  over(order by desc|asc)select  * from [HumanResources].[EmployeePayHistory]go-- 检索 那个员工的工资最高select  EmployeeID ,Rate from [HumanResources].[EmployeePayHistory]order by Rate descgo-- 检索后 给这些员工进行 排名 -- 使用排名ROW_NUMBER()函数生成了连续的数字 select EmployeeID,rate,ROW_NUMBER() over(order by rate desc ) as 'ROW_NUMBER_排名'from [HumanResources].[EmployeePayHistory]goselect EmployeeID,Rate,rank()       over(order by rate desc ) as 'RANK_排名' -- 出现了并列的现象可是生成的不是有序数字from [HumanResources].[EmployeePayHistory]goselect EmployeeID,Rate,dense_rank() over(order by rate desc ) as 'DENSE_RANK_排名'from [HumanResources].[EmployeePayHistory]go--  实例：-- 条件 ： 根据销量值    where-- 目标：销售人员-- 特殊点：每个销售区  partition byselect * from [Sales].[SalesPerson] -- 销售部销售人员表goselect SalesPersonID,TerritoryID,SalesQuota,SalesYTDfrom [Sales].[SalesPerson] -- 销售部销售人员表---------------------这个函数 是表中有区域 ，然后我们在区域中进行排名-- TerritoryID 表示的是区域号  select distinct TerritoryID from [Sales].[SalesPerson]/*以 青岛为例 市南区 1 市北区 2 崂山区 3 城阳区 4 即墨区 5 平度区 6 黄岛区 7 .....  ...*/-----SalesYTD  Sales YTD  year to date 年初到目前为止 -- 查看 每个区域的销售排名 partition by TerritoryID-- over(partition by TerritoryID order by SalesYTD desc )select SalesPersonID,TerritoryID,SalesYTD ,dense_rank() over(partition by TerritoryID order by SalesYTD desc ) as N'区域的销售排名'from [Sales].[SalesPerson]where TerritoryID is not nullgo----- select * from [HumanResources].[Employee]select EmployeeID,BirthDate from [HumanResources].[Employee]---这个表中没有区域，我们使用ntile(组的个数) 函数 按照特定条件 ，进行分组select EmployeeID,BirthDate,HireDate ,ntile(4) over(order by BirthDate) as 'ntile'from [HumanResources].[Employee]where datepart(mm,HireDate)>=4 and datepart(yy,HireDate) >=2001goselect EmployeeID,BirthDate,HireDate ,ntile(6) over(order by BirthDate) as 'ntile'from [HumanResources].[Employee]where datepart(mm,HireDate)>=4 and datepart(yy,HireDate) >=2001goselect datepart(mm,HireDate) from [HumanResources].[Employee]select datepart(yy,HireDate) from [HumanResources].[Employee]-------------------------------------------------------------------create table Emp_SalesData( [year] int , [month] int, productId varchar(20) , amount int)/* P1: 苹果手机 P2:华为手机 P3:8848太空手机*/insert into Emp_SalesData values(2012,2,'P1',20000),(2012,2,'P1',20000),(2012,2,'P2',12000),(2012,3,'P1',15000),(2012,3,'P2',8000),(2012,4,'P1',12000),(2012,4,'P2',14000),(2012,5,'P3',14000)goselect * from Emp_SalesData  go-- 聚合函数 在数据库中 经常与分组函数 在一起 group by -- 同一个表中出现了 不同的月份-- 1 我想 查每一个月份的销售总额select [month] ,sum(amount) as N'统计的每月销售总量' from Emp_SalesData group by [month] -- 分组查询-- 2 想要看看 哪个月份销售的最高select productId, avg(amount) -- 聚合函数不会随意的和无关列搭配from Emp_SalesData where productId like 'p%' -- where 在 group by 关键字前 作为 构成分组查询的条件出现group by productId  -- 是按照商品编号 进行分组having avg(amount) <40000  -- 分组查询时 注意 使用的条件查询关键字时 havingorder by avg(amount) -- order by 可以和聚合函数一起使用goselect productId,sum(amount) as N'统计的每件商品的销售总量'from Emp_SalesDatagroup by productIdgoselect productId,sum(amount) as N'Tatol'from Emp_SalesDatagroup by productIdgo-- 分析函数和排名函数都属于逻辑函数 -- 分析函数可以和聚合函数相配合  主要分析数据--  分析函数 也 需要 over子句的配合-- 3 分析函数 下月的销售量select [month] ,sum(amount) as 'total',lead(sum(amount),3) over (order by [month]) as N'下月的销售量'from Emp_SalesDatagroup by [month]order by [month]-- 4 分析函数 上月的销售量select [month] ,sum(amount) as 'total',lag(sum(amount),2) over (order by [month]) as N'上月的销售量'from Emp_SalesDatagroup by [month]order by [month]-- 5  目前 返回有序记录集中的第一个值 --原表：select * from Emp_SalesData  -- 检索 一年中的第一个月select distinct  productId ,first_value([month]) over(partition by productId order by [month] )as N'一年中的第一个月'from Emp_SalesDatago-- 6 检索 一年中的最近一个月select distinct  productId ,last_value([month]) over(partition by productId order by [month] rows between current rowand unbounded following)as N'一年中的最近一个月'from Emp_SalesDatago--原表：select * from Emp_SalesData  -- 系统函数select host_id()select host_name()select SUSER_SID('sa')select DB_NAME(1)select DB_ID('master')select user_name(1) as UserNameselect cast([NationalIDNumber] as char(20)) as IDNUMBERfrom [HumanResources].[Employee]goselect [NationalIDNumber] from [HumanResources].[Employee]