统计本年度发布的房屋出租信息

#按照季度统计本年度发布的房屋出租信息
USE hos_house
DECLARE @year INT
SET @year=DATEPART(YY,GETDATE())
#查询出以季节，街道，房屋类型来分类的房屋数量
SELECT tmp.quarter AS '季度',hos_district.dName AS '区县',hos_street.sName AS '街道',
hos_type.htName AS '户型',tmp.cnt AS '房屋数量' 
FROM (
    SELECT sid,hTid,COUNT(*) AS cnt,DATEPART(QQ,hTime) AS QUARTER
    FROM hos_house
    WHERE DATEPART(YY,HTIME)=@year
    #以季节，街道，房屋类型分类
    GROUP BY DATEPART(QQ,hTime),sid,hTid
   #派生出tmp表
)tmp                 
INNER JOIN hos_street ON tmp.sid=hos_street.sid
INNER JOIN hos_district ON hos_street.sDid=hos_district.did
INNER JOIN hos_type ON hos_type.hTid=tmp.hTid
UNION
#查询出以季节，区县分类的房屋数量
SELECT DATEPART(QQ,hos_house.hTime),hos_district.dName,'小计','',COUNT(*) AS '房屋数量'
FROM hos_house
INNER JOIN hos_street ON hos_house.sid=hos_street.sid
INNER JOIN hos_district ON hos_street.sDid=hos_district.did
WHERE DATEPART(YY,hos_house.hTime)=@year
GROUP BY DATEPART(qq,hos_house.HTIME),hos_district.DName
UNION
#查询出以季节分类的房屋数量 
SELECT DATEPART(QQ,hos_house.hTime),'合计','','',COUNT(*) AS '房屋数量'
FROM hos_house
INNER JOIN hos_street ON hos_house.sid=hos_street.sid
INNER JOIN hos_district ON hos_street.sDid=hos_district.did
WHERE DATEPART(YY,hos_house.hTime)=@year
GROUP BY DATEPART(qq,hos_house.hTime)