第五章租房网

SQL查询技巧与实践

最新推荐文章于 2021-02-02 10:34:21 发布

原创最新推荐文章于 2021-02-02 10:34:21 发布 · 388 阅读

0 ·

CC 4.0 BY-SA版权

本文通过具体案例展示了SQL查询的不同方面，包括基本的SELECT语句用法、子查询与连接查询的区别及应用、多表联接查询以及分组与聚合函数的高级运用等。通过对这些查询技巧的学习，读者可以更好地理解和掌握SQL在实际场景中的应用。

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

go
--阶段2

--子查询
select dname,sname,htid,price,topic,contents,htime,copy 
from [dbo].[hos_district] as district,[dbo].[hos_street] as street,[dbo].[hos_house] as house,[dbo].[sys_user] as user1
where user1.uid=house.uid and street.sid=house.sid and street.sdid=district.did and
house.uid =(select  uid from [dbo].[sys_user] where uname='张三') 

go
--连接查询
select dname,sname,htid,price,topic,contents,htime,copy
 from [dbo].[hos_district] as district
inner join [dbo].[hos_street] as street on street.sdid=district.did
inner join [dbo].[hos_house] as house on street.sid=house.sid
inner join [dbo].[sys_user] as user1 on user1.uid=house.uid
where house.uid =(select  uid from [dbo].[sys_user] where uname='张三') 

go

----------阶段3

--（3）阶段3正确的代码
------根据 户型和房屋 所在 区县和街道 为至少有两个街道 有出租房屋的区县 制作清单
------使用 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

   )

--阶段4

--查询按季度统计本年发布的房屋出租数量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