优化MySchool 第五章 租房网

 /****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 [HMID]
      ,[UID]
      ,[SID]
      ,[HTID]
      ,[PRICE]
      ,[TOPIC]
      ,[CONTENTS]
      ,[HTIME]
      ,[COPY]
  FROM [House].[dbo].[hos_house]
  
  
  --分页显示查询出租房屋信息(使用top关键字)
  
  select top 5 * from dbo.hos_house where HMID not in(
    select top 5 HMID from dbo.hos_house
  )
  
  --方式二：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
  
  
  
  --上级练习二,查询指定客户发布的出租房屋信息
  select DNAME,SNAME,S.UID,PRICE,TOPIC,CONTENTS,HTIME,COPY
  from dbo.hos_district D,dbo.hos_street H,dbo.sys_user S,dbo.hos_house P
  where S.UID=P.UID and H.SID=P.SID and  D.DID=S.UID and
    S.UID in(
  select UID from dbo.sys_user where UNAME='张三'
  )
  
  
 ---上机练习三,按区县制作房屋出租清单

  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
  )
)



--上机练习四,按季度统计本年发布的房屋出租数量
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)