sql子查询自主检测

1、 查询所有直接签约的交易的交易GUID（无定单信息）。(25分)

预期结果：

                            

SELECT TRADEGUID FROM S_TRADE
WHERE TRADEGUID NOT IN (SELECT TRADEGUID FROM S_ORDER)

或

SELECT TRADEGUID FROM S_TRADE
WHERE NOT EXISTS (SELECT * FROM S_ORDER WHERE TRADEGUID=S_TRADE.TRADEGUID)

2、 查询存在2个以上交易的客户的客户GUID，客户姓名，创建人。（25分）

预期结果：

SELECT CSTGUID,CSTNAME,CREATEDBY FROM P_CUSTOMER
WHERE CSTGUID IN (select cstguid from s_trade2cst group by cstguid having count(*)>1)

3、 查询在不同项目买过房间的客户姓名及项目名称（客户有2个以上交易且交易对应的房间的所属项目不同）（25分）

预期结果：

SELECT A.CSTNAME,D.PROJNAME FROM
S_TRADE2CST A JOIN S_TRADE B ON A.TRADEGUID=B.TRADEGUID
JOIN P_ROOM C ON B.ROOMGUID=C.ROOMGUID JOIN P_PROJECT D ON C.PROJGUID=D.PROJGUID
WHERE EXISTS (select * from s_trade2cst e join s_trade f on e.tradeguid=f.tradeguid
join p_room g on f.roomguid=g.roomguid where g.projguid<>c.projguid and e.cstguid=a.cstguid)
ORDER BY A.CSTNAME

4、 查出每个项目均价（楼栋下房间TOTAL/房间数）最高的楼栋，如果并列最高则都要显示。显示楼栋GUID，楼栋名称，均价，项目名称。结果按均价升序排序。（25分）

预期结果：

SELECT bld.BldGUID ,bld.BldName ,bldAvg.avarage ,proj.ProjName  
FROM p_Project proj
INNER JOIN p_Building bld ON bld.ProjGUID = proj.ProjGUID
INNER JOIN(
select SUM(Total)/COUNT(RoomGUID) avarage,BldGUID from p_Room group by BldGUID
) bldAvg
ON bldAvg.BldGUID = bld.BldGUID 
where 
(select SUM(Total)/COUNT(RoomGUID) 
from p_Room room1 
where room1.BldGUID = bld.BldGUID group by room1.BldGUID )
IN (
select top 1 SUM(Total)/COUNT(RoomGUID)avarage 
from p_Room room2 
where room2.ProjGUID  = proj .ProjGUID  group by BldGUID order by avarage desc)
ORDER BY bldAvg.avarage