mysql的临时表用法

DELIMITER $$

USE `qgroupdata`$$

DROP PROCEDURE IF EXISTS `Ex_Print_Bg_Kdd_Search`$$

CREATE DEFINER=`root`@`%` PROCEDURE `Ex_Print_Bg_Kdd_Search`(
     _where VARCHAR(2000),  #查询条件
     _orderby VARCHAR(200), #排序条件
     _pageindex INT, #第几页
     _pagesize INT,  #每页的记录数        
     OUT _pagecount INT , #页数
     OUT _recordcount INT ,#查询的总记录数 
     OUT _buyercount INT #买家数 
    )
BEGIN
    
   SET @strsql = CONCAT('CREATE TABLE IF NOT EXISTS temp_exnumber AS  SELECT t.`logid`,  t.`buyerNick`,t.`toFullName`,t.`receiver_province`,t.`receiver_city`,t.`exid`,t.`excode`,t.`excodename`,t.`exnumber`,t.`orderids`,t.`goodsnum`,t.`weight`,t.`kddtype`  FROM ( SELECT * FROM `print_bg_kdd` WHERE exnumber<>','''','''','  AND exnumber IS NOT NULL AND ',_where ,' ORDER BY `logid` DESC) t GROUP BY t.`exnumber` ORDER BY t.logid DESC;'); 
   PREPARE strsql FROM @strsql;   
   EXECUTE strsql;  
   DEALLOCATE PREPARE strsql;
 
  SET @strsql2 = CONCAT('CREATE TABLE IF NOT EXISTS temp_exnumber2 AS  SELECT  t.`status`,t.`exnumber`,t.`adddatetime` FROM ( SELECT * FROM `print_bg_kdd` WHERE exnumber<>','''','''',' AND exnumber IS NOT NULL AND ',_where ,' ORDER BY `status` DESC) t GROUP BY t.`exnumber`;'); 
   PREPARE strsql2 FROM @strsql2;
   EXECUTE strsql2;
   DEALLOCATE PREPARE strsql2; 
 
     #记录数统计
     SELECT COUNT(0) INTO _recordcount FROM temp_exnumber ;
     #买家数统计
      SET @strsql4 = CONCAT('SELECT COUNT(DISTINCT buyerNick) INTO @buyercount FROM print_bg_kdd where ',_where);
    PREPARE strsql4 FROM @strsql4;
    EXECUTE strsql4;
   DEALLOCATE PREPARE strsql4; 
     SET _buyercount=@buyercount;
     
   SET @startRow = _pageSize*(_pageIndex -1);
   SET @pageSize = _pageSize; 
    SET @strsql3 = CONCAT('SELECT  a.*,b.status,b.adddatetime FROM temp_exnumber AS a INNER JOIN temp_exnumber2 AS b ON a.exnumber=b.exnumber  order by ',_orderby,' limit ',@startRow,',',@pageSize);   
   PREPARE strsql3 FROM @strsql3;
   EXECUTE strsql3;
   DEALLOCATE PREPARE strsql3; 
  
  #计算订单数
   SELECT orderids FROM temp_exnumber;
     
  
  #计算页数
 IF (_recordcount <= _pageSize) THEN
                    SET _pagecount = 1;
                    ELSE IF (_recordcount % _pageSize > 0) THEN
                    SET _pagecount = _recordcount / _pageSize + 1;
                    ELSE
                     SET _pagecount = _recordcount / _pageSize;
            END IF;
       END IF;  
   #删除临时表
   DROP TABLE temp_exnumber;
   DROP TABLE temp_exnumber2;
    END$$

DELIMITER ;