高级查询（二）
查询张三发布的所有出租房屋信息，并显示房屋分布的街道和区县
1 SELECT dname 区县,sname 街道,hTID 户型,price 价格,topic 标题,contents 描述,hTime 时间,copy 备注 FROM 2 hos_house 3 INNER JOIN hos_street ON hos_street.SID=hos_house.SID 4 INNER JOIN hos_district ON hos_district.DID=hos_street.sDID 5 WHERE 6 UID IN(SELECT UID from sys_user WHERE uname='张三');
根据户型和房屋所在的曲线和街道，为至少有两个街道有出租房屋的区县制作出租屋清单
1 SELECT htName 户型,uName 姓名,dName 区县,sName 街道 from hos_house 2 INNER JOIN hos_type ON hos_type.hTID=hos_house.hTID 3 INNER JOIN hos_street ON hos_street.SID=hos_house.SID 4 INNER JOIN hos_district ON hos_district.DID=hos_street.sDID 5 INNER JOIN sys_user ON sys_user.UID=hos_house.UID 6 WHERE hos_street.sDID IN 7 ( 8 SELECT hos_street.sDID FROM hos_house 9 INNER JOIN hos_street ON hos_street.SID=hos_house.SID 10 INNER JOIN hos_district ON hos_district.DID=hos_street.sDID 11 GROUP BY hos_street.sDID 12 HAVING COUNT(hos_street.sDID)>1 13 )
按季度统计出本年各区县各街道各种房屋出租数量
1 SELECT abc.season 季度, hos_district.dName 区县,hos_street.sName 街道,hos_type.hTName 户型,abc.num 房屋数量 FROM 2 (SELECT QUARTER(hTime) season, SID,hTID,COUNT(*)num from hos_house WHERE YEAR(hTime)=2009 GROUP BY season,SID,hTID) AS abc 3 INNER JOIN hos_street ON hos_street.SID=abc.SID 4 INNER JOIN hos_type ON hos_type.hTID=abc.hTID 5 INNER JOIN hos_district ON hos_district.DID=hos_street.sDID
