第一本书 第五章 上机

----阶段1---
 select top 5 * from hos_house
	where HMID not in (
	select top 5 HMID from hos_house
	)

	----阶段2---
	select  DName,SName,[HTID],[PRICE],[TOPIC] ,[CONTENTS],[HTIME],[COPY] 
	from hos_house a,hos_district b,hos_street c ,sys_user d
	where b.DID=c.SDID and a.SID=c.SID and d.UID=a.UID
	and UNAME='张三'

	---阶段3---
	--select HTName,UNAME,DName,SName 
	--from sys_user a,hos_district b,hos_street c ,hos_type d,hos_house e
	--where a.UID=e.UID and b.DID=c.SDID and c.SID=e.SID and d.HTID=e.HTID
	-- having count(c.SID)>1
	----------------------按区县指作房屋出租清单-----------
------根据 户型和房屋 所在 区县和街道 为至少有两个街道 有出租房屋的区县 制作清单
------使用 having字句 筛选出 街道数量 大于1  的  区县

SELECT hos_type.HTNAME AS '户型',sys_user.UNAME AS '姓名',

hos_district.DNAME AS '区县',hos_street.SNAME AS '街道'

FROM hos_house    INNER JOIN sys_user ON (hos_house.UID=sys_user.UID)

                  INNER JOIN hos_street ON(hos_house.SID=hos_street.SID)

                  INNER JOIN hos_district ON(hos_street.SDID=hos_district.DID)

                  INNER JOIN hos_type ON(hos_house.HTID=hos_type.HTID)

WHERE hos_street.SDID IN (
      SELECT hos_street.SDID
      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_street.SDID
  having COUNT(hos_street.SDID)>1

   )





  ----------------按季度统计本年 发布的 房屋出租数量---------------------------
   ----按季度统计出本年度 各区县 各街道 各种户型  房屋出租数量
   --输出 本年从1月1日起至今的 全部 出租房屋数量，各区县出租房屋数量 及 各街道，户型出租房屋数量 
--查询按季度统计本年发布的房屋出租数量6
select datepart
(
   QUARTER,hos_house.HTIME) as 季度,hos_district.DNAME as 区县,
   hos_street.SNAME as 街道,hos_type.HTNAME as 户型 ,
   COUNT(hos_house.HMID)as 数量
from hos_house,hos_street,hos_district,hos_type,sys_user
 where hos_house.SID=hos_street.SID 
         and hos_street.SDID=hos_district.DID
         and hos_type.HTID=hos_house.HTID 
         and	sys_user.UID=hos_house.UID
 group by datepart(QUARTER,hos_house.HTIME),hos_district.DNAME,hos_street.SNAME,hos_type.HTNAME