S2_day05我的租房网

se HOUSE    select * from hos_house  --房间信息表    select * from hos_district  --区县表    select * from hos_street    --街道表    select * from hos_type    --房间类型表        --1.内容扩充：临时表    --如果表名前面有#，那么创建的表是临时表，位于tempdb数据库下    --的临时表文件夹下。    create table #mytemp2222    (      id int identity(1,1) primary key not null,      name nvarchar(20)    )            --任务一：分页显示查询出租房信息    --*******************方式一：双top，双order by分页************************    --查询输出第6条~第10条房屋出租信息        --did:区县编号(hos_district)        select top 5 *,hos_district.did    from hos_house,hos_street,hos_district    where  hmid    not in(select top 5 hmid from hos_house)    and hos_house.sid=hos_street.sid    and hos_street.sdid=hos_district.did        --*******************方式二：Row_Number() over(order by id)***************    select * from     (select *,row_number() over(order by hmid) as myid    from hos_house) as temp    where myid between 6 and 10    --*******************方式三：临时表分页***********************************    --查询输出第6条~第10条房屋出租信息    use house    select identity(int,1,1) as myid, UID, SID, HTID, PRICE, TOPIC, CONTENTS, HTIME, COPY    into #housetemp    from hos_house    --通过临时表我们发现，其实实现分页只有两中思路，一种是通过双top，双order by方式，    --另外一种方式就是用一定的手段(可以使系统提供Row_NUmber()函数，也    --可以通过identity(int,1,1)来产生)，在现有表上加上一个自动增长列。    select * from #housetemp    where myid between 6 and 10    --如何将临时表释放    drop table #housetemp    --练习2——查询指定客户发布的出租房屋信息    --需求说明::    --查询“张三”发布的所有出租房屋信息，并显示房屋分布的街道、区县        select DNAME as 区县,SNAME as 街道,HTNAME as 户型,price as 价格,topic as 标题,contents as 描述,htime as 时间,copy as 备注    from hos_district,    hos_street,    hos_type,    hos_house    where hos_house.sid=hos_street.sid    and hos_house.htid=hos_type.htid    and hos_street.sdid=hos_district.did    and uid=    (      select uid from sys_user      where uname='张三'    )    --阶段3：练习——按区县制作房屋出租清单    --根据户型和房屋所在区县和街道,为至少有2个街道有房屋出租的区县制作出租房屋清单    --方式一(推荐)：    select HTNAME as 户型,UNAME as 姓名,DNAME as 区县, SNAME as 街道    from hos_type,sys_user,hos_district,hos_street,hos_house    where hos_house.sid=hos_street.sid    and hos_type.htid=hos_house.htid    and sys_user.uid=hos_house.uid    and hos_district.did=hos_street.sdid    and hos_street.sdid in    (      select  hos_street.sdid      from  hos_street,hos_district,      (select distinct sid from hos_house) as temp      where hos_street.sid=temp.sid      and hos_street.sdid=hos_district.did      group by hos_street.sdid      having count(hos_street.sid)>=2    )        --方式二：    select HTNAME as 户型,UNAME as 姓名,DNAME as 区县, SNAME as 街道    from hos_type,sys_user,hos_district,hos_street,hos_house    where hos_house.sid=hos_street.sid    and hos_type.htid=hos_house.htid    and sys_user.uid=hos_house.uid    and hos_district.did=hos_street.sdid    and hos_house.SID in    (      select hos_street.SID      from hos_street      where sdid in      (        select sdid        from hos_street,(select distinct(SID) from hos_house) as temp       where hos_street.sid=temp.sid       group by sdid      having count(hos_street.SID)>=2      )    )            --阶段4：按季度统计本年发布的房屋出租数量    --要求输出本年1月1日至今的全部出租房屋数量，各区县    --出租房屋数量以及各街道、户型出租房屋数量。    use house    DECLARE @year int    --SET @year = DATEPART(yy,GETDATE())    set @year=2009        SELECT tmp.quarter AS '季度',hos_district.DNAME AS '区县',hos_street.SNAME AS '街道',    hos_type.HTNAME AS '户型',tmp.cnt AS '房屋数量'    FROM (      SELECT SID,HTID,COUNT(*) cnt,DATEPART(qq,HTIME) quarter      FROM hos_house      WHERE DATEPART(yy,HTIME)=@year      GROUP BY DATEPART(qq,HTIME),SID,HTID    ) tmp    --INNER JOIN sys_user ON (tmp.UID=sys_user.UID)    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(tmp.HTID=hos_type.HTID)            UNION        SELECT DATEPART(qq,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,HTIME)=@year    GROUP BY DATEPART(qq,HTIME),hos_district.DNAME        union        SELECT DATEPART(qq,HTIME),' 合计 ','  ','  ',COUNT(*) AS '房屋数量'    FROM hos_house    WHERE DATEPART(yy,HTIME)=@year    GROUP BY DATEPART(qq,HTIME)