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_Room
ORDER BY ProjGUID,BldGUID

2、使用APPLY语法实现:

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

预期结果：

 

SELECT bldInfo.BldGUID,bldInfo.BldName,bldInfo.avgTotal,proj.ProjName
FROM p_Project proj
CROSS APPLY(
select top 1 avg(ISNULL(Total,0)) over(partition by room.BldGUID) as 'avgTotal'
, p_Project.ProjName,p_Building.BldName,room.BldGUID
from p_Room room
left join p_Project on p_Project.ProjGUID=room.ProjGUID
left join p_Building on p_Building.BldGUID=room.bldGUID
where room.ProjGUID=proj.ProjGUID
order by avgtotal desc
) AS bldInfo

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

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

   预期结果：

 

WITH class_CTE AS
(
--基本语句
select ClassFullCode,ClassFullName,ParentCode,0 AS level
from s_Class where ParentCode='' or ParentCode is null
 
union all
--递归语句
select class.ClassFullCode,class.ClassFullName,class.ParentCode ,CTE.level+1
from s_Class class
inner 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 customer
PIVOT(count(CstGUID) for provincein([湖南],[湖北],[山东])) AS tab