第五章案例3和4

--阶段3：
select hos_type.HTName as '户型', sys_user.UName as'姓名', hos_district.DName as'区县', hos_street.SName 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
inner join sys_user on sys_user.UID=hos_house.UID
inner join hos_type on hos_type.HTID=hos_house.HTID
where hos_district.DID in(select hos_district.DID 
                       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
                       group by hos_district.DID
                       having COUNT(*)=2 or COUNT(*)>2 )                   
--阶段4
declare @year datetime		
set @year = 2009			
select DATEPART(QQ,HTIME) 季度,DName 区县,SName 街道,HTName 户型,COUNT(HMID) 房屋数量
from hos_house h,hos_district d,hos_street s,hos_type t
where h.HTID = t.HTID and s.SDID = d.DID and h.SID = s.SID and DATEPART(YY,HTIME) = @year
group by DATEPART(QQ,HTIME),DName,SName,HTName
union
select DATEPART(QQ,HTIME) 季度,d.DName 区县,'   小计','',COUNT(h.HMID) 房屋数量
from hos_house h,hos_street s,hos_district d
where s.SDID = d.DID and h.SID = s.SID and DATEPART(YY,HTIME) = @year
group by DATEPART(QQ,HTIME),DName
union
select DATEPART(QQ,HTIME) 季度,'  合计  ','','',COUNT(HMID) 房屋数量
from hos_house
where DATEPART(YY,HTIME) = @year
group by DATEPART(QQ,HTIME)
go