SQL高級 第五章 我的租房網


use HOUSE
select * from hos_house --房間信息表
select * from hos_district --區縣表
select * from hos_street --街道表
select * from hos_type --房間類型表

--1.內容擴充:臨時表
--如果表名前面有#,那麼創建的表是臨時表,位於tempdb數據庫下
--的臨時表文件夾下。
create table #mytemp2222
(
id int identity(1,1) primary key not null,
name nvarchar(20)
)
--使用臨時表的好處:臨時表中的數據當我們斷開連接後自動釋放,這樣
--可以節省數據庫服務器的空間

--任務一:分頁顯示查詢出租房信息

--經典分頁思路(一、雙top,雙order by 二、Row_number()函數 三、臨時表方式(和Row_Number()原理一致))
--將出租房屋記錄批量插入臨時表

--*******************方式一:雙top,雙order by分頁************************
--查詢輸出第6條~第10條房屋出租信息

--did:區縣編號(hos_district)

select top 5 *,hos_district.did
from hos_house,hos_street,hos_district
where hmid
not in(select top 5 hmid from hos_house)
and hos_house.sid=hos_street.sid
and hos_street.sdid=hos_district.did

--*******************方式二: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
--*******************方式三:臨時表分頁***********************************
--查詢輸出第6條~第10條房屋出租信息
use house
select identity(int,1,1) as myid, UID, SID, HTID, PRICE, TOPIC, CONTENTS, HTIME, COPY
into #housetemp
from hos_house
--通過臨時表我們發現,其實實現分頁隻有兩中思路,一種是通過雙top,雙order by方式,
--另外一種方式就是用一定的手段(可以使系統提供Row_NUmber()函數,也
--可以通過identity(int,1,1)來產生),在現有表上加上一個自動增長列。
select * from #housetemp
where myid between 6 and 10
--如何將臨時表釋放
drop table #housetemp
--練習2——查詢指定客戶發佈的出租房屋信息
--需求說明::
--查詢“張三”發佈的所有出租房屋信息,並顯示房屋分佈的 街道、區縣

select DNAME as 區縣,SNAME as 街道,HTNAME as 戶型,price as 價格,topic as 標題,contents as 描述,htime as 時間,copy as 備註
from hos_district,
hos_street,
hos_type,
hos_house
where hos_house.sid=hos_street.sid
and hos_house.htid=hos_type.htid
and hos_street.sdid=hos_district.did
and uid=
(
select uid from sys_user
where uname='張三'
)
--階段3:練習——按區縣制作房屋出租清單
--根據戶型和房屋所在區縣和街道,為至少有2個街道有房屋出租的區縣制作出租房屋清單
--方式一(推薦):
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_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
)

--方式二:
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
)
)

--階段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)[email protected]
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)[email protected]
GROUP BY DATEPART(qq,HTIME),hos_district.DNAME

union

SELECT DATEPART(qq,HTIME),' 合計 ',' ',' ',COUNT(*) AS '房屋數量'
FROM hos_house
WHERE DATEPART(yy,HTIME)[email protected]
GROUP BY DATEPART(qq,HTIME)

0 個評論

要回覆文章請先登錄註冊