CREATE DEFINER=`root`@`%` PROCEDURE `getMaSalemenPLPercentageRecordByPage`(IN `pageSize` int,IN `pageNo` int,IN `companyID` long,IN `apixID` long,IN `userID` long,IN `beginTime` varchar(30),IN `endTime` varchar(30))
BEGIN
#根据日期分组,计算某一页的数据
DECLARE beginS int DEFAULT 0;
DECLARE endS int DEFAULT 10;
DECLARE baseSql VARCHAR(5000) DEFAULT '';
DECLARE totalCount int DEFAULT 0;-- 总条数
DECLARE beginTimePage TIMESTAMP;-- 分页开始时间
DECLARE endTimePage TIMESTAMP;-- 分页结束时间
set beginS=(pageNo-1)*pageSize;
set endS=pageSize;
DROP table if EXISTS baseTable;
set baseSql=CONCAT(baseSql,"create temporary table baseTable select '合计' as total ");
set baseSql=CONCAT(baseSql," from table_a where 1=1 ");
if(companyID is not null and companyID != '' and companyID != 0) then set baseSql=CONCAT(baseSql," AND a.company_id = ",companyID); END if;
if(apixID is not null and apixID != '' and apixID != 0) then set baseSql=CONCAT(baseSql," AND a.apix_id = ",apixID); END if;
if(userID is not null and userID != '' and userID != 0) then set baseSql=CONCAT(baseSql," AND a.user_id = ",userID); END if;
if(beginTime is not null and beginTime != '' ) then set baseSql=CONCAT(baseSql," AND r.query_time >= '",beginTime,"'"); END if;
if(endTime is not null and endTime != '' ) then set baseSql=CONCAT(baseSql," AND r.query_time <= '",date_format(endTime,'%Y-%m-%d 23:59:59'),"'"); END if;
-- select baseSql;
set @baseSql=baseSql; -- 注意很重要,将连成成的字符串赋值给一个变量(可以之前没有定义,但要以@开头)
prepare stmt from @baseSql; -- 预处理需要执行的动态SQL,其中stmt是一个变量
EXECUTE stmt; -- 执行SQL语句
deallocate prepare stmt; -- 释放掉预处理段
from baseTable ;
truncate baseTable;
END
BEGIN
#根据日期分组,计算某一页的数据
DECLARE beginS int DEFAULT 0;
DECLARE endS int DEFAULT 10;
DECLARE baseSql VARCHAR(5000) DEFAULT '';
DECLARE totalCount int DEFAULT 0;-- 总条数
DECLARE beginTimePage TIMESTAMP;-- 分页开始时间
DECLARE endTimePage TIMESTAMP;-- 分页结束时间
set beginS=(pageNo-1)*pageSize;
set endS=pageSize;
DROP table if EXISTS baseTable;
set baseSql=CONCAT(baseSql,"create temporary table baseTable select '合计' as total ");
set baseSql=CONCAT(baseSql," from table_a where 1=1 ");
if(companyID is not null and companyID != '' and companyID != 0) then set baseSql=CONCAT(baseSql," AND a.company_id = ",companyID); END if;
if(apixID is not null and apixID != '' and apixID != 0) then set baseSql=CONCAT(baseSql," AND a.apix_id = ",apixID); END if;
if(userID is not null and userID != '' and userID != 0) then set baseSql=CONCAT(baseSql," AND a.user_id = ",userID); END if;
if(beginTime is not null and beginTime != '' ) then set baseSql=CONCAT(baseSql," AND r.query_time >= '",beginTime,"'"); END if;
if(endTime is not null and endTime != '' ) then set baseSql=CONCAT(baseSql," AND r.query_time <= '",date_format(endTime,'%Y-%m-%d 23:59:59'),"'"); END if;
-- select baseSql;
set @baseSql=baseSql; -- 注意很重要,将连成成的字符串赋值给一个变量(可以之前没有定义,但要以@开头)
prepare stmt from @baseSql; -- 预处理需要执行的动态SQL,其中stmt是一个变量
EXECUTE stmt; -- 执行SQL语句
deallocate prepare stmt; -- 释放掉预处理段
-- 处理好的数据与其他表进行关联等操作
select *from baseTable ;
truncate baseTable;
END