|
CREATE DEFINER=`root`@`localhost` PROCEDURE `yuyu`(in INPHONEMODNO varchar(10),in INBigTypeID varchar(10), in INNotDisplaySupcode varchar(800))
begin
DROP TEMPORARY TABLE IF EXISTS tb_tmp_topprod;
CREATE TEMPORARY TABLE tb_tmp_topprod (
id INT AUTO_INCREMENT PRIMARY KEY,
ProdCode varchar(14) collate utf8_unicode_ci NOT NULL,
ProdName varchar(100) collate utf8_unicode_ci default NULL,
ProdEName varchar(100) collate utf8_unicode_ci default NULL,
PRODSPELL varchar(100) collate utf8_unicode_ci default NULL,
ThirdTypeID varchar(100) collate utf8_unicode_ci default NULL,
ThirdTypeName varchar(100) collate utf8_unicode_ci default NULL,
ThirdTypeEName varchar(100) collate utf8_unicode_ci default NULL,
ThirdTypeImg varchar(100) collate utf8_unicode_ci default NULL,
SupCode varchar(100) collate utf8_unicode_ci default NULL,
FILEPATH varchar(400) collate utf8_unicode_ci default NULL,
FILESIZE BIGINT,
ProdImg varchar(100) collate utf8_unicode_ci default NULL,
ProdMovie varchar(100) collate utf8_unicode_ci default NULL,
ProdPrice decimal(18,4) default NULL,
Integral int,
ZY varchar(800) collate utf8_unicode_ci default NULL,
FULLTEXT KEY `ProdNameIndex` (`ProdName`)
)ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
if INNotDisplaySupcode<>'' then
if INBigTypeID in('04','07') then
INSERT INTO tb_tmp_topprod( ProdCode,ProdName,ProdEName,PRODSPELL,ThirdTypeID,ThirdTypeName,ThirdTypeEName,ThirdTypeImg,SupCode,FILEPATH,FILESIZE,ProdImg,ProdMovie,ProdPrice,Integral,ZY)
select a.ProdCode,a.ProdName,a.ProdEName,a.PRODSPELL,a.ThirdTypeID,a.ThirdTypeName,a.ThirdTypeEName,a.ThirdTypeImg,a.SupCode,a.FILEPATH,a.FILESIZE,a.ProdImg,a.ProdMovie,a.ProdPrice,0 as Integral,a.ZY from xa_dg_prodinfo a where CONVERT(a.BigtypeID using utf8)=CONVERT(INBigTypeID using utf8) and convert(a.supcode using utf8) not in (convert(INNotDisplaySupcode using utf8));
else
-- 省略----------
end if;
if INBigTypeID in('01','02','05') then
select a.ProdCode,a.ProdName,a.ProdEName,a.PRODSPELL,a.SupCode,a.FILEPATH,a.FILESIZE,a.ProdImg,a.ProdMovie,a.ProdPrice,a.Integral,a.ZY,b.tag from tb_tmp_topprod a
left join xa_dg_prod_activeprodlist b on b.Tag='TOP' and instr(a.prodname,b.prodname)>0 order by b.tag desc,b.ID asc limit 0,10;
end if;-- 删除临时表
DROP TEMPORARY TABLE IF EXISTS tb_tmp_topprod;
END
|
