OVER，APPLY，CTE，PIVOT自主检测

1、使用OVER开窗函数实现：

查询房间GUID，房间号(room)，房间所在楼栋的房间平均价格，房间所在项目的房间平均价格，按项目GUID、楼栋排序GUID排序

预期结果：

SELECT RoomGUID,Room,AVG(ISNULL(Total,0))OVER(partition by BldGuid) AS '楼栋均价',AVG(Total) OVER(PARTITION BY ProjGUID) AS '项目均价'FROM p_RoomORDER BY ProjGUID,BldGUID

2、使用APPLY语法实现:

 查出每个项目下均价（楼栋下房间TOTAL/房间数）最高的楼栋，如果并列最高则显示1条。显示楼栋GUID，楼栋名称，楼栋均价，项目名称。结果楼栋按均价降序排序。

预期结果：

 

SELECT bldInfo.BldGUID,bldInfo.BldName,bldInfo.avgTotal,proj.ProjNameFROM p_Project projCROSS APPLY(select top 1 avg(ISNULL(Total,0)) over(partition by room.BldGUID) as 'avgTotal', p_Project.ProjName,p_Building.BldName,room.BldGUIDfrom p_Room roomleft join p_Project on p_Project.ProjGUID=room.ProjGUIDleft join p_Building on p_Building.BldGUID=room.bldGUIDwhere room.ProjGUID=proj.ProjGUIDorder by avgtotal desc) AS bldInfo

3、使用公用表达式递归实现：

   查询每s_class表中，每个班级的完整编码(classFullCode)、全名(ClassFullName)、层级(level)。层级定义为，如果为顶层班级则层级为1，如果是层级为1的班级的子班级，则层级为2，以此递增。

   预期结果：

 

WITH class_CTE AS(--基本语句select ClassFullCode,ClassFullName,ParentCode,0 AS levelfrom s_Class where ParentCode='' or ParentCode is null union all--递归语句select class.ClassFullCode,class.ClassFullName,class.ParentCode ,CTE.level+1from s_Class classinner join class_CTE CTE on class.ParentCode =CTE.ClassFullCode --递归调用)SELECT * FROM class_CTE

4、使用pivot关键字实现：

查询湖南、湖北、山东三省各创建人(createdby)创建了多少客户。

预期结果：

--先以传入PIVOT参数（CstGUID，province）之外的字段，即CreatedBy进行分组--然后系统内部进行casewhen操作--最后进行count聚合SELECT tab.CreatedBy,tab.[湖南],tab.[湖北],tab.[山东]FROM(select CreatedBy,CstGUID,province from p_Customer where Province <>'') as customerPIVOT(count(CstGUID) for provincein([湖南],[湖北],[山东])) AS tab