WANFANG-SQL过程

--出租房源的搜索内容显示的存储过程 CREATE procedure Pr_ChuzuRoom_Search_Rs ( @wherestr varchar(300), @startIndex int, @endIndex int ) As SET NOCOUNT ON BEGIN --创建分页临时表 --判断表是不是存在，若存在就清空表，若不存在就建立表 if exists(select * from sysobjects where id=object_id('indextable1')) truncate table indextable1 else create table indextable1(id int identity(1,1),nid int) EXEC('insert into indextable1(nid) select roomid from room where '+@wherestr) select O.*,A.areaname,S.spacename,B.buildingname from room O left join area A on(O.areaid=A.areaid) left join space S on(S.spaceid=O.spaceid) left join building B on(O.zoneid=B.buildingid),indextable1 t where O.sellorlease=1 and O.roomid=t.nid and t.id between @startIndex and @endIndex order by t.id END SET NOCOUNT OFF GO

--得到小区排行的存储过程，小区按点击量排行
--名称：Pr_Uzonename参数：小区的id号
--状态：未用到
AlterprocedurePr_Uzonepaihang
(
@uzoneidint
)
AS
begin
declare@clickcountint
set@clickcount=(selectclickcountfrombuildingwherebuildingid=@uzoneid)
declare@indextabletable(nidint)
insertinto@indextableselectcount(*)frombuildingwhereclickcount>@clickcount
selectnid+1from@indextable
end
GO


--得到小区统计的存储过程，小区按点击量排行和人气和业主数
--名称：Pr_Uzonename参数：小区的id号
--状态：用到
AlterprocedurePr_Uzonepaihang
(
@uzoneidint
)
AS
begin

--人气
declare@clickcountint
set@clickcount=(selectclickcountfrombuildingwherebuildingid=@uzoneid)
--排行
declare@numint
set@num=(selectcount(*)frombuildingwhereclickcount>@clickcount)
--业主数
declare@usernumint
declare@temptabletable(aint)
insertinto@temptableselectDISTINCTuseridfromroomwherezoneid=@uzoneid
set@usernum=(selectcount(*)from@temptable)
--把以上参数合并到表
declare@indextabletable(paihangint,clickcountint,usernumint)
insertinto@indextableVALUES(@num+1,@clickcount,@usernum)
select*from@indextable
end
GO

--得到小区的基本信息的存储过程
--名称Pr_GetUz_BaseInfo参数：小区的id号
--状态用到
createprocedurePr_GetUz_BaseInfo
@uzoneidint
AS
selectB.*,S.spacename,A.areanamefrombuildingBLEFTJOINareaAON(B.areaid=A.areaid)LEFTJOINspaceSON

(S.spaceid=b.spaceid)whereB.buildingid=@uzoneidANDB.isbuilding=1
GO


--得到指定小区的周边小区的小区或者新楼盘的信息
--名称
--用到
createprocedurePr_GetUz_Nearbuilding
(
@uzoneidint,@typeint,@count1int
)
AS
begin
declare@spaceidint
set@spaceid=(selectspaceidfrombuildingwherebuildingid=@uzoneid)
ExEc('selecttop'+@count1+'*frombuildingwherespaceid='+@spaceid+'ANDisbuilding='+@type)
end

--第二个版本
createprocedurePr_GetUz_Nearbuilding
(
@uzoneidint,@typeint,@count1int
)
AS
begin
declare@spaceidint
setRowcount@count1
set@spaceid=(selectspaceidfrombuildingwherebuildingid=@uzoneid)
select*frombuildingwherespaceid=@spaceidANDisbuilding=@type
end

--视图的V_zonepinglun_user

SETQUOTED_IDENTIFIERON
GO
SETANSI_NULLSON
GO


ALTERVIEWdbo.V_zonepinglun_user
AS
SELECTdbo.users.username,dbo.usedzoneremark.*
FROMdbo.usedzoneremarkLEftJOIN
dbo.usersONdbo.usedzoneremark.userid=dbo.users.userid


GO
SETQUOTED_IDENTIFIEROFF
GO
SETANSI_NULLSON
GO


--插入一条小区的评论记录
createprocedurePr_Insert_aremark
(
@uzoneidint,@comparetinyint,@ismarkuptinyint,@factpricemoney,@useridint,@contenttext
)
AS
begin
INSERTINTOusedzoneremark(buildingid,compare,factprice,ismarkup,content,userid)VALUES

(@uzoneid,@compare,@factprice,@ismarkup,@content,@userid)
end


--得到指定小区的出售房源和出租房源
--0：售1：租，2：出售租均可
--用到
ALTERprocedurePr_GetUz_Rooms
(
@uzoneidint,@typeint,@count1int
)
AS
begin
if(@count1=0)
ExEc('select*fromroomwherezoneid='+@uzoneid+'ANDsellorlease='+@type)
else
ExEc('selecttop'+@count1+'*fromroomwherezoneid='+@uzoneid+'ANDsellorlease='+@type)
end


SETQUOTED_IDENTIFIERON
GO
SETANSI_NULLSON
GO


--得到指定小区的出售房源和出租房源
--0：售1：租，2：出售租均可
--用到
ALTERprocedurePr_GetUz_Rooms
(
@uzoneidint,@typeint,@count1int
)
AS
begin
if(@count1=0)
if(@type=3)
ExEc('select*fromroomwherezoneid='+@uzoneid)
else
ExEc('select*fromroomwherezoneid='+@uzoneid+'ANDsellorlease='+@type)
else
ExEc('selecttop'+@count1+'*fromroomwherezoneid='+@uzoneid+'ANDsellorlease='+@type)
end


GO
SETQUOTED_IDENTIFIEROFF
GO
SETANSI_NULLSON
GO


--得到周边中介
CREATEprocedurePr_Uz_GetZhongJie
(
@uzoneidint,
@startIndexint,
@endIndexint,
@docounttinyint)
AS
BEGIN
declare@spaceidint
set@spaceid=(selectspaceidfrombuildingwherebuildingid=@uzoneidandisbuilding=1)
--当@docount>=1,就返回@docount指定的条说记录，否则分页
if(@docount>=1)
EXEc('selecttop'+@docount+'*fromzhongjiewherespaceid='+@spaceid)
else
begin
declare@indextabletable(idintidentity(1,1),nidint)
setrowcount@endIndex
insertinto@indextable(nid)selectzhongjieidfromzhongjiewherespaceid=@spaceid
select*fromzhongjieZ,@indextableTwhereZ.zhongjieid=T.nidandT.idbetween@startIndexand@endIndex

orderbyT.id
end
END

--得到小区排行的存储过程，小区按点击量排行
--名称：Pr_Uzonename参数：小区的id号
--状态：未用到
AlterprocedurePr_Uzonepaihang
(
@uzoneidint
)
AS
begin
declare@clickcountint
set@clickcount=(selectclickcountfrombuildingwherebuildingid=@uzoneid)
declare@indextabletable(nidint)
insertinto@indextableselectcount(*)frombuildingwhereclickcount>@clickcount
selectnid+1from@indextable
end
GO


--得到小区统计的存储过程，小区按点击量排行和人气和业主数
--名称：Pr_Uzonename参数：小区的id号
--状态：用到
AlterprocedurePr_Uzonepaihang
(
@uzoneidint
)
AS
begin

--人气
declare@clickcountint
set@clickcount=(selectclickcountfrombuildingwherebuildingid=@uzoneid)
--排行
declare@numint
set@num=(selectcount(*)frombuildingwhereclickcount>@clickcount)
--业主数
declare@usernumint
declare@temptabletable(aint)
insertinto@temptableselectDISTINCTuseridfromroomwherezoneid=@uzoneid
set@usernum=(selectcount(*)from@temptable)
--把以上参数合并到表
declare@indextabletable(paihangint,clickcountint,usernumint)
insertinto@indextableVALUES(@num+1,@clickcount,@usernum)
select*from@indextable
end
GO

--得到小区的基本信息的存储过程
--名称Pr_GetUz_BaseInfo参数：小区的id号
--状态用到
createprocedurePr_GetUz_BaseInfo
@uzoneidint
AS
selectB.*,S.spacename,A.areanamefrombuildingBLEFTJOINareaAON(B.areaid=A.areaid)LEFTJOINspaceSON

(S.spaceid=b.spaceid)whereB.buildingid=@uzoneidANDB.isbuilding=1
GO


--得到指定小区的周边小区的小区或者新楼盘的信息
--名称
--用到
createprocedurePr_GetUz_Nearbuilding
(
@uzoneidint,@typeint,@count1int
)
AS
begin
declare@spaceidint
set@spaceid=(selectspaceidfrombuildingwherebuildingid=@uzoneid)
ExEc('selecttop'+@count1+'*frombuildingwherespaceid='+@spaceid+'ANDisbuilding='+@type)
end

--第二个版本
createprocedurePr_GetUz_Nearbuilding
(
@uzoneidint,@typeint,@count1int
)
AS
begin
declare@spaceidint
setRowcount@count1
set@spaceid=(selectspaceidfrombuildingwherebuildingid=@uzoneid)
select*frombuildingwherespaceid=@spaceidANDisbuilding=@type
end

--视图的V_zonepinglun_user

SETQUOTED_IDENTIFIERON
GO
SETANSI_NULLSON
GO


ALTERVIEWdbo.V_zonepinglun_user
AS
SELECTdbo.users.username,dbo.usedzoneremark.*
FROMdbo.usedzoneremarkLEftJOIN
dbo.usersONdbo.usedzoneremark.userid=dbo.users.userid


GO
SETQUOTED_IDENTIFIEROFF
GO
SETANSI_NULLSON
GO


--插入一条小区的评论记录
createprocedurePr_Insert_aremark
(
@uzoneidint,@comparetinyint,@ismarkuptinyint,@factpricemoney,@useridint,@contenttext
)
AS
begin
INSERTINTOusedzoneremark(buildingid,compare,factprice,ismarkup,content,userid)VALUES

(@uzoneid,@compare,@factprice,@ismarkup,@content,@userid)
end


--得到指定小区的出售房源和出租房源
--0：售1：租，2：出售租均可
--用到
ALTERprocedurePr_GetUz_Rooms
(
@uzoneidint,@typeint,@count1int
)
AS
begin
if(@count1=0)
ExEc('select*fromroomwherezoneid='+@uzoneid+'ANDsellorlease='+@type)
else
ExEc('selecttop'+@count1+'*fromroomwherezoneid='+@uzoneid+'ANDsellorlease='+@type)
end


SETQUOTED_IDENTIFIERON
GO
SETANSI_NULLSON
GO


--得到指定小区的出售房源和出租房源
--0：售1：租，2：出售租均可
--用到
ALTERprocedurePr_GetUz_Rooms
(
@uzoneidint,@typeint,@count1int
)
AS
begin
if(@count1=0)
if(@type=3)
ExEc('select*fromroomwherezoneid='+@uzoneid)
else
ExEc('select*fromroomwherezoneid='+@uzoneid+'ANDsellorlease='+@type)
else
ExEc('selecttop'+@count1+'*fromroomwherezoneid='+@uzoneid+'ANDsellorlease='+@type)
end


GO
SETQUOTED_IDENTIFIEROFF
GO
SETANSI_NULLSON
GO


--得到周边中介
CREATEprocedurePr_Uz_GetZhongJie
(
@uzoneidint,
@startIndexint,
@endIndexint,
@docounttinyint)
AS
BEGIN
declare@spaceidint
set@spaceid=(selectspaceidfrombuildingwherebuildingid=@uzoneidandisbuilding=1)
--当@docount>=1,就返回@docount指定的条说记录，否则分页
if(@docount>=1)
EXEc('selecttop'+@docount+'*fromzhongjiewherespaceid='+@spaceid)
else
begin
declare@indextabletable(idintidentity(1,1),nidint)
setrowcount@endIndex
insertinto@indextable(nid)selectzhongjieidfromzhongjiewherespaceid=@spaceid
select*fromzhongjieZ,@indextableTwhereZ.zhongjieid=T.nidandT.idbetween@startIndexand@endIndex

orderbyT.id
end
END

GO

CREATE VIEW dbo.V_roomnum
AS
SELECT TOP 100 PERCENT zoneid AS zoneid, COUNT(roomid) AS roomnum
FROM a0522144228.room
GROUP BY zoneid
ORDER BY roomnum DESC
CREATE PROCEDURE Pr_InsertAZhongjie
(
@areaid int,
@spaceid int,
@company varchar(100),
@address varchar(100),
@linkman varchar(100),
@phone varchar(100),
@chuanzhen varchar(100),
@jianjie text,
@gonggao text
)
AS
insert into zhongjie(areaid,spaceid,company,address,linkman,phone,chuanzhen,jianjie,gonggao)VALUES(@areaid,@spaceid,@company,@address,@linkman,@phone,@chuanzhen,@jianjie,@gonggao)
GO