sql连接查询自主检测

1、  查询每个房间的房间GUID(RoomGUID)，房间名(Room)，单元(Unit)，房间对应的楼栋名称(BldName)，项目名称(ProjName)。按项目、楼栋、单元、房间排序。

预期结果：

SELECT room.RoomGUID,room.room,room.Unit,bld.BldName,proj.ProjName 
FROM p_Roomroom
LEFT JOINp_Building bld ON bld.BldGUID = room.BldGUID
LEFT JOINp_Project proj ON proj.ProjGUID = room.ProjGUID
ORDER BYproj.ProjName ,bld.BldName ,room.Unit ,room.Room 

2、  根据交易-客户表（s_Trade2Cst）的客户顺序号字段(CstNum)，显示每个交易的交易GUID和客户姓名（最多为4个。顺序号为1的显示为CSTNAME1，依此类推）

预期结果：

SELECT t1.TradeGUID,t1.CstName AS 'CSTNAME1',t2.CstName AS 'CSTNAME2',t3.CstName AS 'CSTNAME3',t4.CstName AS 'CSTNAME4'
FROM (select TradeGUID,CstName from s_Trade2Cst where CstNum=1) t1
LEFT JOINs_Trade2Cst t2 ON t2.TradeGUID =t1.TradeGUID AND t2.CstNum=2
LEFT JOINs_Trade2Cst t3 ON t3.TradeGUID =t1.TradeGUID AND t3.CstNum=3
LEFT JOINs_Trade2Cst t4 ON t4.TradeGUID =t1.TradeGUID AND t4.CstNum=4

3、楼栋名称，该楼栋的房间平均价格( 该楼栋房间TOTAL之和/房间数量)

预期结果：

SELECT bld.BldGUID  AS '楼栋GUID',bld.BldName AS'楼栋名称',SUM(room.Total)/COUNT(1) AS '楼栋均价' FROM p_Building  bld
INNER JOINp_Room  room ON room.BldGUID =bld.BldGUID
GROUP BYbld.BldGUID,bld.BldName

4、查询项目GUID，项目名称，该项目的房间平均价格( 该项目房间TOTAL之和/房间数量)

预期结果：

SELECT  proj.ProjGUID  AS '项目GUID' ,proj.ProjNameAS '项目名称',CASE COUNT(1) WHEN 0 THEN 0 ELSE SUM(ISNULL(Total,0))/COUNT(1) END AS '项目均价'
FROM p_Projectproj
LEFT JOINp_Room room ON room.ProjGUID =proj.ProjGUID
GROUP BYproj.ProjGUID,proj.ProjName

5、查询楼栋GUID，楼栋名称，该楼栋的房间平均价格，楼栋对应的项目名称，项目平均价格

预期结果：

SELECT bld1.BldGUID  AS '楼栋GUID',bld1.BldName AS'楼栋名称',bld2.average AS'楼栋均价' ,proj1.ProjName  AS '项目名称',proj2.average AS'项目均价'
from p_Building  bld1
LEFT JOINp_Project proj1ON proj1.ProjGUID  =bld1.ProjGUID
LEFT JOIN(select  sum(Total)/count(1) average,BldGUID  from p_Room group by BldGUID ) bld2 ON bld2.BldGUID =bld1.BldGUID
LEFT JOIN(select  sum(Total)/count(1) average,ProjGUID from p_Room group by ProjGUID) proj2 ON proj2.ProjGUID =bld1.ProjGUID