利用临时表汇总多表的数据

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER proc [dbo].[createtable]
@fidName varchar(50),
@PageSize int,
@PageIndex int,
@OrderType int,
@strWhere varchar(1500),
@rows int output,
@pagecount int output
 as
--先查c_roommate
select ID as pid,Address,Price,Room,Hall,Toilet,[Floor],CreateTime,Floorarea
 into #tmp from c_roommate where Status=0 and Type=1 and Situation=1
--添加区分列。插入默认值为0,
alter table #tmp ADD sort int NULL CONSTRAINT sort DEFAULT 0 WITH VALUES
update #tmp set sort=1 where sort=0
--取消标识列
SET IDENTITY_INSERT #tmp On;
--插入C_Rent表数据
insert into #tmp(pid,Address,Price,Room,Hall,Toilet,[Floor],CreateTime,Floorarea)
select ID,PAddress,Price,Room,Hall,Toilet,[Floor],CreateTime,Floorarea from C_Rent
where type=1 and Status=0
--更改区分列的值
update #tmp set sort=2 where sort=0
--插入C_SecondSell表数据
insert into #tmp(pid,Address,Price,Room,Hall,Toilet,[Floor],CreateTime,Floorarea)
select ID,PAddress,Price,Room,Hall,Toilet,[Floor],CreateTime,Floorarea from C_SecondSell
where type=1 and Status=0
--更改区分列
update #tmp set sort=3 where sort=0
exec pagination '#tmp','*',@fidName,@PageSize,@PageIndex,@OrderType,@strWhere,@rows output,@pagecount output
drop table #tmp