第五章项目----租房网

--阶段1:知道————分页显示查询出租房屋信息
--需求说明：查询出第4-6条出租房屋信息

select top 3*
from hos_house 
where HMID not in (select top 1 HMID from hos_house)

select *
from hos_house
where HMID>1 and HMID<5

--阶段2：练习————查询指定客户发布的出租房屋信息
--需求说明：查询张三发布所有出租房屋信息，并显示房屋分布的街道和区县

use House
go
select hos_district.DName as '区县',hos_street.SName as '街道',hos_type.HTName as'房屋类型',hos_house.PRICE as '价格',
       hos_house.TOPIC as'标题', hos_house.CONTENTS as'描述',hos_house.HTIME as '时间',
       hos_house.COPY 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 sys_user.UName='张三'

--阶段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：练习————按季度统计本年发布的房屋出租数量
--需求说明：按季度统计出本年各区各县个街道各种户型房屋出租数量
--          要求输出本年1月1日至今的全部出租房屋数量，各区县出租房屋数量以及各街道，户型出租房屋数量
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)