MYSQL多表分页存储过程

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_AllSiteSearch`(IN pagesize int,IN pageindex int,IN skey varchar(100),IN dt varchar(10),IN itype varchar(10),IN siteid int)
BEGIN
Declare pagecount int DEFAULT 1;
Declare count int DEFAULT 0;
Declare news_count int DEFAULT 0;
Declare house_count int DEFAULT 0;
Declare esf_count int DEFAULT 0;
Declare zf_count int DEFAULT 0;
Declare gtv_count int DEFAULT 0;
Declare ssql varchar(4000) DEFAULT '';
Declare top int;
Declare showcount int DEFAULT 0;
Declare str1 varchar(200) DEFAULT '';
Declare str2 varchar(200) DEFAULT '';
Declare topnum int;


Set top=pagesize*pageindex;


DROP TABLE IF EXISTS _t;
CREATE TEMPORARY TABLE _t(
id INT auto_increment primary key,
img varchar(300),
title varchar(200),
content varchar(1000),
author varchar(100),
hits varchar(15),
dt datetime,
itype varchar(10),
cid varchar(10),
catalogid varchar(10),
path varchar(50),
orderid tinyint,
 filename VARCHAR(200)
);
if skey <> '' and siteid <> '' THEN
If dt <> '' And NOT(DATE_FORMAT(dt,'%Y%m%d') IS NULL) THEN
SET str1=concat(' And CONVERT(a.addtime,DATE)>=''',CONVERT(dt,CHAR),'''');
SET str2=concat(' And CONVERT(a.PublishDate,DATE)>=''',CONVERT(dt,CHAR),'''');
end if;

Set @ssql=concat('Select count(*) into @news_count From t_con a,t_class c Where a.catalogid=c.id and status=30 and publishflag=''Y'' And a.SiteID=''',siteid,'''');
set @ssql = CONCAT(@ssql,' and contenttypeid=''article'' and ifnull(a.prop1,'''')='''' And (title like ''%',skey,'%'' Or author like ''%',skey,'%'') ',str2,';');
PREPARE STMT FROM @ssql;
EXECUTE STMT;
SET news_count = @news_count;

Set @ssql=concat('Select count(*) into @house_count From t_house a Where Q_isRecyle=0 And Q_Audited = 1 And Q_HouseName like ''%',skey,'%'' And SiteID=''',siteid,'''',str1,';');
PREPARE STMT FROM @ssql;
EXECUTE STMT;
SET house_count = @house_count;
Set @ssql=concat('Select count(*) into @house_count From t_con a,t_class c Where a.catalogid=c.id and status=30 and publishflag=''Y'' And a.SiteID=''',siteid,'''');
SET @ssql = CONCAT(@ssql,' and contenttypeid=''newhousenews'' and ifnull(a.prop1,'''')<>'''' And ifnull(a.ConfigProps,'''')<>'''' And (title like ''%',skey,'%'' Or author like ''%',skey,'%'') ',str2,';');
PREPARE STMT FROM @ssql;
EXECUTE STMT;
SET house_count = house_count+@house_count;
/*
Set @ssql=concat('Select count(*) into @esf_count From t_esf a Where h_infostate=0 And h_title like ''%',skey,'%'' And h_infotype=''出售'' And SiteID=''',siteid,'''',str1,';');
PREPARE STMT FROM @ssql;
EXECUTE STMT;
SET esf_count = @esf_count;

Set @ssql=concat('Select count(*) into @zf_count From t_esf a Where h_infostate=0 And h_title like ''%',skey,'%'' And h_infotype=''出租'' And SiteID=''',siteid,'''',str1,';');
PREPARE STMT FROM @ssql;
EXECUTE STMT;
SET zf_count = @zf_count;
*/
Set @ssql=concat('Select count(*) into @gtv_count From t_con a,t_class c Where a.catalogid=c.id and status=30 and publishflag=''Y'' and contenttypeid=''video'' And a.SiteID=''',siteid,'''');
SET @ssql = CONCAT(@ssql,' And (title like ''%',skey,'%'' Or author like ''%',skey,'%'') ',str2,';');
PREPARE STMT FROM @ssql;
EXECUTE STMT;
SET gtv_count = @gtv_count;



Set showcount=news_count+house_count+esf_count+zf_count+gtv_count;
Set count=showcount;

If (itype='1') THEN
Set count=news_count;
end if;

If (itype='2') THEN
Set count=house_count;
end if;

If (itype='3') THEN
Set count=esf_count;
end if;

If (itype='4') THEN
Set count=zf_count;
end if;

If (itype='5') THEN
Set count=gtv_count;
end if;
Set pagecount=CEIL(count/pagesize);


if(count>0) THEN


if(pageindex<1)THEN
Set pageindex=1;
End if;
if(pageindex>pagecount)THEN
Set pageindex=pagecount;
End if;


If(itype='1' Or itype='')THEN
SET @ssql = CONCAT('Insert Into _t(img,title,content,author,hits,dt,itype,cid,catalogid,path,orderid,filename)');
SET @ssql = CONCAT(@ssql,' Select a.logofile,title,summary,author,a.hitcount,Convert(a.PublishDate,date),''1'',a.id cid,a.catalogid,c.path,1,(case a.LinkFlag when ''y'' then RedirectURL else a.StaticFileName end) StaticFileName');
SET @ssql = CONCAT(@ssql,' From t_con a,t_class c Where a.catalogid=c.id and status=30 and publishflag=''Y'' and ifnull(a.prop1,'''')='''' And a.SiteID=''',siteid,'''');
SET @ssql = CONCAT(@ssql,' and contenttypeid=''article'' And (title like ''%',skey,'%'' Or author like ''%',skey,'%'') ',str2,' Order By a.PublishDate Desc limit ',top,';');
PREPARE STMT FROM @ssql;
EXECUTE STMT;
End if;

If(itype='2' Or itype='')THEN
Set @ssql = CONCAT('Insert Into _t(img,title,content,author,hits,dt,itype,cid,catalogid,path,orderid,filename)');
SET @ssql = CONCAT(@ssql,' Select Q_logo,Q_HouseName,Q_Position,'''',Q_click,Convert(a.addtime,date),''2'',Q_id,'''','''',0,'''' From t_house a ');
SET @ssql = CONCAT(@ssql,' Where Q_HouseName like ''%',skey,'%'' And Q_isRecyle=0 And Q_Audited = 1 And SiteID=''',siteid,'''',str1,' order by a.addtime DESC limit ',top,';');
PREPARE STMT FROM @ssql;
EXECUTE STMT;
Set @ssql = CONCAT('Insert Into _t(img,title,content,author,hits,dt,itype,cid,catalogid,path,orderid,filename)');
SET @ssql = CONCAT(@ssql,' Select a.logofile,title,summary,author,a.hitcount,Convert(a.PublishDate,date),''2'',a.id cid,a.catalogid,c.path,1,(case a.LinkFlag when ''y'' then RedirectURL else a.StaticFileName end) StaticFileName');
SET @ssql = CONCAT(@ssql,' From t_con a,t_class c Where a.catalogid=c.id and status=30 and publishflag=''Y'' and ifnull(a.prop1,'''')<>'''' And a.SiteID=''',siteid,'''');
SET @ssql = CONCAT(@ssql,' and contenttypeid=''newhousenews'' And (title like ''%',skey,'%'' Or author like ''%',skey,'%'') ',str2,' Order By a.PublishDate Desc limit ',top,';');
PREPARE STMT FROM @ssql;
EXECUTE STMT;
End if;
/*
If(itype='3' Or itype='')THEN
Set @ssql = CONCAT('Insert Into _t(img,title,content,author,hits,dt,itype,cid,catalogid,path,orderid,filename)');
SET @ssql = CONCAT(@ssql,' Select h_img,h_title,h_remark,'''',h_clickcount,Convert(a.addtime,date),''3'',h_id,'''','''',1,'''' From t_esf a ');
SET @ssql = CONCAT(@ssql,' Where h_infostate=0 And h_title like ''%',skey,'%'' And h_infotype=''出售'' And SiteID=''',siteid,'''',str1,' Order By a.addtime Desc limit ',top,';');
PREPARE STMT FROM @ssql;
EXECUTE STMT;
End if;

If(itype='4' Or itype='')THEN
Set @ssql = CONCAT('Insert Into _t(img,title,content,author,hits,dt,itype,cid,catalogid,path,orderid,filename)');
SET @ssql = CONCAT(@ssql,' Select h_img,h_title,h_remark,'''',h_clickcount,Convert(a.addtime,date),''4'',h_id,'''','''',1,'''' From t_esf a');
SET @ssql = CONCAT(@ssql,' Where h_infostate=0 And h_title like ''%',skey,'%'' And h_infotype=''出租'' And SiteID=''',siteid,'''',str1,' Order By a.addtime Desc limit ',top,';');
PREPARE STMT FROM @ssql;
EXECUTE STMT;
End if;
*/
If(itype='5' Or itype='')THEN
SET @ssql = CONCAT('Insert Into _t(img,title,content,author,hits,dt,itype,cid,catalogid,path,orderid,filename)');
SET @ssql = CONCAT(@ssql,' Select a.logofile,title,summary,author,a.hitcount,Convert(a.PublishDate,date),''5'',a.id cid,a.catalogid,c.path,1,(case a.LinkFlag when ''y'' then RedirectURL else a.StaticFileName end) StaticFileName');
SET @ssql = CONCAT(@ssql,' From t_con a,t_class c Where a.catalogid=c.id and status=30 and publishflag=''Y'' And a.SiteID=''',siteid,'''');
SET @ssql = CONCAT(@ssql,' and contenttypeid=''video'' And (title like ''%',skey,'%'' Or author like ''%',skey,'%'') ',str2,' Order By a.PublishDate Desc limit ',top,';');
PREPARE STMT FROM @ssql;
EXECUTE STMT;
End if;
end if;
end if;
Select '全部' name,'' itype,showcount num
Union All
Select '新闻' name,'1',news_count
Union All
Select '新房' name,'2',house_count
Union All
/*
Select '二手房' name,'3',esf_count
Union All
Select '租房' name,'4',zf_count
Union All
*/
Select '视频' name,'5',gtv_count;


Select pagecount,count;
Set topnum = top - pagesize;
SET @ssql = CONCAT('Select * From _t Order By orderid,dt Desc LIMIT ',topnum,',',top,';');
PREPARE STMT FROM @ssql;
EXECUTE STMT;
Drop Table _t;
END