SQL 第五章 -- 小型练习项目
来源:互联网 发布:xp如何关闭软件网络 编辑:程序博客网 时间:2024/06/04 23:22
use HOUSEselect * 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分页************************--查询输出第6条~第10条房屋出租信息--did:区县编号(hos_district)select top 5 *,hos_district.didfrom hos_house,hos_street,hos_districtwhere hmidnot in(select top 5 hmid from hos_house)and hos_house.sid=hos_street.sidand hos_street.sdid=hos_district.did--*******************方式二:Row_Number() over(order by id)***************select * from (select *,row_number() over(order by hmid) as myidfrom hos_house) as tempwhere myid between 6 and 10--*******************方式三:临时表分页***********************************--查询输出第6条~第10条房屋出租信息use houseselect identity(int,1,1) as myid, UID, SID, HTID, PRICE, TOPIC, CONTENTS, HTIME, COPYinto #housetempfrom hos_house--通过临时表我们发现,其实实现分页只有两中思路,一种是通过双top,双order by方式,--另外一种方式就是用一定的手段(可以使系统提供Row_NUmber()函数,也--可以通过identity(int,1,1)来产生),在现有表上加上一个自动增长列。select * from #housetempwhere 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_housewhere hos_house.sid=hos_street.sidand hos_house.htid=hos_type.htidand hos_street.sdid=hos_district.didand 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_housewhere hos_house.sid=hos_street.sidand hos_type.htid=hos_house.htidand sys_user.uid=hos_house.uidand hos_district.did=hos_street.sdidand 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_housewhere hos_house.sid=hos_street.sidand hos_type.htid=hos_house.htidand sys_user.uid=hos_house.uidand hos_district.did=hos_street.sdidand 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 houseDECLARE @year int--SET @year = DATEPART(yy,GETDATE())set @year=2009SELECT 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)UNIONSELECT DATEPART(qq,HTIME),hos_district.DNAME,' 小计 ',' ',COUNT(*) AS '房屋数量'FROM hos_houseINNER 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)=@yearGROUP BY DATEPART(qq,HTIME),hos_district.DNAMEunionSELECT DATEPART(qq,HTIME),' 合计 ',' ',' ',COUNT(*) AS '房屋数量'FROM hos_houseWHERE DATEPART(yy,HTIME)=@yearGROUP BY DATEPART(qq,HTIME)
0 0
- SQL 第五章 -- 小型练习项目
- Django小型项目练习:模拟商场储物柜
- 第五章 编程练习
- 第五章练习
- 第五章练习
- Python 第五章练习
- 人力资源第五章练习解答
- simply scheme 第五章 练习
- 第五六章练习总结
- 第五章 5.1节练习
- SQL第五章
- SQL笔记 第五章
- 第五章 PL/SQL
- 第五章:sql语言
- 第五章 体检项目
- andriod小型sql数据库
- 小型FTP项目
- 小型项目总结之一
- 什么是反向代理,什么是正向代理,两者有什么的区别
- ToolBar 修改边距
- swift 函数语法、格式
- 深入Spring:自定义注解加载和使用
- curl如何获取https中的内容
- SQL 第五章 -- 小型练习项目
- windows 7 下配置sublime text 3以同时使用python2 和 3
- Eclipse安装
- adb常用命令大全
- Ubuntu关机重启命令简介
- CPP粗糙
- 类对象与memset函数
- C++之构造函数重载一题
- sqlsever安装问题