第五章

select top 9 * from hos_house
where HMID not in
(
   select top 6 HMID from hos_house
)
----------------------------------------------------
select DNAME,SNAME,PRICE,TOPIC,CONTENTS,HTIME,COPY 
from hos_house,hos_district,hos_street
where hos_district.DID=hos_street.SDID 
and hos_street.SID=hos_house.SID
and UID = (select UID from 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_street.SID
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
)





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