Fifth Project----Rental Network

--Phase 1: Know ———— pagination display query rental housing information
--Demand Description: Check out 第4-6条 rental housing information

Select top
From Hos_house
where Hmid not in (select top 1 hmid from Hos_house)

SELECT *
From Hos_house
where Hmid>1 and Hmid<5

--Phase 2: Practice ———— Query the rental housing information for the specified customer
--Requirements: Query Zhang San publish all rental housing information, and show the streets and counties of the housing distribution

Use House
Go
Select Hos_district. Dname as ' counties ', Hos_street. SName as ' street ', Hos_type. Htname as ' house type ', Hos_house. Price as ' prices ',
Hos_house. TOPIC as ' title ', Hos_house. CONTENTS as ' description ', Hos_house. Htime as ' time ',
Hos_house. COPY as ' remarks '
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
INNER join Sys_user on Sys_user. Uid=hos_house. Uid
INNER join Hos_type on Hos_type. Htid=hos_house. Htid
where Sys_user. Uname= ' Zhang San '

--Phase 3: Contact ———— to make a list of housing rentals by districts
--Requirements: According to the type of house and the county and the street, for at least two streets have rented housing district to make a house list

Select Hos_type. Htname as ' type ', Sys_user. UName as ' name ', Hos_district. Dname as ' counties ', Hos_street. SName as ' street '
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
INNER join Sys_user on Sys_user. Uid=hos_house. Uid
INNER join Hos_type on Hos_type. Htid=hos_house. Htid
where Hos_district. Did in (select Hos_district. Did
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_district. Did
Having Count (*) =2 or COUNT (*) >2)

--Phase 4: Practice ———— Quarterly statistics on the number of housing rentals released this year
--Requirements: According to the quarterly statistics of each district of the county streets of various types of housing rental quantity
--demand for output of the total number of rental houses this year January 1 to date, the number of rental houses in each district and the number of street, type rental housing
DECLARE @year int

Set @year =datepart (Yy,getdate ())
--Check out the number of houses sorted by season, street, house type
Select Tmp.quarter as ' Quarter ', hos_district. Dname as ' counties ', Hos_street. SName as ' street ', Hos_type. Htname as ' unit ', tmp.cnt as ' number of houses '
From (
Select Sid,htid,count (*) as Cnt,datepart (Qq,htime) as Quarter
From Hos_house
where DATEPART (yy,htime) [email protected]
Group by DATEPART (Qq,htime), Sid,htid--classification by season, street, house type
) TMP--derive the TMP table
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 Hos_type. Htid=tmp. Htid
Union
--Check out the number of houses by season and district
Select DATEPART (qq,hos_house. Htime), Hos_district. Dname, ' subtotal ', ', COUNT (*) as ' number of houses '
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,hos_house. htime) [email protected]
Group BY DATEPART (Qq,hos_house. Htime), Hos_district. Dname
Union
--Check out the number of houses in season category
Select DATEPART (qq,hos_house. htime), ' Total ', ', ', ', COUNT (*) as ' number of houses '
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,hos_house. htime) [email protected]
Group BY DATEPART (Qq,hos_house. Htime)

Fifth Project----Rental Network