《数据库技术与开发》工程实训指导书
DECLARE @userid INT DECLARE @streetid INT DECLARE @htid INT DECLARE @price DECIMAL DECLARE @htime DATETIME DECLARE @topic VARCHAR(50) DECLARE @contents VARCHAR(50) DECLARE @copy VARCHAR(50) DECLARE @num INT BEGIN TRANSACTION SET @num = 0 WHILE @num<30 BEGIN
SET @userid=(SELECT TOP 1 UserId FROM sys_user ORDER BY NEWID()) SET @streetid=(SELECT TOP 1 streetid FROM hos_street ORDER BY NEWID()) SET @htid=(SELECT TOP 1 htid FROM hos_type ORDER BY NEWID()) --租金在-4000之间随机产生
SET @price=1000+CAST(3000*RAND() AS INT)
--发布时间@htime，要求小于当前系统时间，发布时间在当前系统时间一年内
6
《数据库技术与开发》工程实训指导书
SET @htime=CAST(DATEADD(DAY,-CAST(RAND()*DATEPART(DAYOFYEAR,GETDATE()) AS INT),GETDATE()) AS DATE)
SET @topic=(SELECT TOP 1 topic FROM #topic ORDER BY NEWID()) PRINT @topic
SET @contents=(SELECT TOP 1 content FROM #content ORDER BY NEWID()) SET @copy=(SELECT TOP 1 copy FROM #copy ORDER BY NEWID()) INSERT INTO hos_house(UserId,StreetId,HTId,Price,Topic,Contents,HTime,Copy) VALUES(@userid,@streetid,@htid,@price,@topic,@contents,@htime,@copy) SET @num=@num+1 END
COMMIT TRANSACTION
3、实训三：综合查询
(1) 分页显示查询出租房屋信息 查询输出第6-10条出租房屋信息：
? 使用Top关键字实现查询分页显示，用临时表先取出前10条记录，然后再
在临时表里取出第6-第10条记录。
SELECT TOP 10 * INTO #hos_house_top10 FROM hos_house
SELECT * FROM #hos_house_top10 WHERE HMId NOT IN(SELECT TOP 5 HMId FROM #hos_house_top10)
7
《数据库技术与开发》工程实训指导书
? 使用RowNumber函数，要求所有的列标题使用中文，查询结果如下图所示：
SELECT TOP 10 ROW_NUMBER() OVER(ORDER BY HMId) AS rowid,* INTO #temp FROM hos_house
SELECT HMId AS 房源编号,UserId AS 用户编号,StreetID AS 街道编号,Topic AS 标题,Contents AS 房源描述,Price AS 月租,HTime AS 发布时间,Copy AS 备注 FROM #temp WHERE rowid BETWEEN 6 AND 10
(2) 查询指定客户发布的出租房屋信息
查询张三发布的所有出租房屋信息，并显示房屋分布的街道和区县，要求分别按下述2种方法实现图7的查询结果：
? 使用内联接inner join查询和子查询实现。
SELECT hd.DName '区县',hs.SName '街道',ht.HTName '房屋类型',hh.Topic '标题', hh.Price '价格',hh.Contents '房源描述',hh.HTime '时间',hh.Copy '备注' FROM hos_house hh
INNER JOIN hos_street hs ON hh.StreetId=hs.StreetId INNER JOIN hos_district hd ON hs.SDId=hd.DId INNER JOIN sys_user su ON su.UserId=hh.UserId INNER JOIN hos_type ht ON ht.HTId = hh.HTId WHERE su.UserName='张三'
8
《数据库技术与开发》工程实训指导书
? 使用where子句和内查询实现。
SELECT hd.DName '区县',hs.SName '街道',ht.HTName '房屋类型',hh.Topic '标题', hh.Price '价格',hh.Contents '房源描述',hh.HTime '时间',hh.Copy '备注' FROM hos_house hh,hos_street hs,hos_district hd,sys_user su,hos_type ht
WHERE hh.StreetId=hs.StreetId AND hs.SDId=hd.DId AND su.UserId=hh.UserId AND ht.HTId = hh.HTId AND su.UserName='张三'
(3) 按区县制作房屋出租清单
SELECT ht.HTName '户型',su.UserName '姓名',hd.DName '区县',hs.SName '街道' FROM hos_house hh
INNER JOIN hos_street hs ON hh.StreetId=hs.StreetId INNER JOIN hos_district hd ON hs.SDId=hd.DId INNER JOIN sys_user su ON su.UserId=hh.UserId INNER JOIN hos_type ht ON ht.HTId = hh.HTId
WHERE hd.DId IN(SELECT hd.DId FROM hos_house hh
INNER JOIN hos_street hs ON hh.StreetId=hs.StreetId INNER JOIN hos_district hd ON hs.SDId=hd.DId GROUP BY hd.DId HAVING COUNT(*)>1)
ORDER BY hs.StreetId
9
《数据库技术与开发》工程实训指导书
4、实训四：业务统计
(1) 按季度统计本年度发布的房屋出租数量
CREATE VIEW View_QTDst AS
SELECT DATEPART(QUARTER,hh.HTime) '季度',hd.DName '区县',hs.SName '街道',ht.HTName '户型',COUNT(hh.HMId) '数量' FROM hos_house hh INNER JOIN hos_street hs ON hh.StreetId=hs.StreetId INNER JOIN hos_district hd ON hs.SDId=hd.DId INNER JOIN sys_user su ON su.UserId=hh.UserId INNER JOIN hos_type ht ON ht.HTId = hh.HTId
GROUP BY DATEPART(QUARTER,hh.HTime),hd.DName,hs.SName,ht.HTName
10