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)