优化Myschool 第五章 租房网
来源:互联网 发布:华为中央软件院欧拉 编辑:程序博客网 时间:2024/04/27 20:43
--阶段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
0 0
- 优化MySchool 第五章 租房网
- 优化Myschool 第五章 租房网
- 优化MYSCHOOL租房网
- 第五章租房网
- MySchool 05 租房网项目
- 第五章 租房网 完整版
- accp7.0S2优化myschool数据库设计第五章项目
- 优化MySchool第九章
- 优化MySchool 第八章
- 优化MySchool 第八章
- 优化MySchool 第二章
- 优化myschool 第九章
- S2 第五章 我的租房网
- 第五章 我的租房网
- SQL 第五章 我的租房网
- 第五章:项目:我的租房网
- S2 优化MySchool第四章
- 优化MySchool第七章练习
- SQL 第六章简答题
- Nginx对长连接的处理及HTTP协议中body长度的确定
- seq2seq学习笔记
- 如何将一个函数作为另一个函数的输入参数
- 设计模式学习记录——代理模式
- 优化Myschool 第五章 租房网
- fxs和fxo的工作原理小结
- ACM找BUG(特例)个人心得---不断更新
- 博客搬家
- JSP 技术
- 基本UDP套接字编程
- drawRect的绘制的使用(绘制文本字符、绘制图片、绘制图形)
- 用户权限管理模块的数据库设计
- CSDN日报20170224——《程序员该用哪种姿势来理财》