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