mysql 存储过程 为encharts 提供的serise 数据

mysql复杂查询可以将查询中间结果放到中间表中

/*带参数的 数据统计 */

/*dateFormat  为必须传递的参数 
参数示例userids：'1','402881a859aa10940159aa2e022f0017'
startDate：2015-01 ，endDate：2017-03, dateFormat:%Y-%m */


CREATE PROCEDURE `llftest1`(IN `userids` VARCHAR(300), IN `startDate` VARCHAR(50), IN `endDate` VARCHAR(50), IN `dateFormat` VARCHAR(50))
BEGIN
DECLARE where_SQL VARCHAR(1000) DEFAULT '';
/*DECLARE V_SQL2 VARCHAR(300) DEFAULT '';*/
/*清空表*/
truncate table tempData;
/*插入暂存数据*/
SET @V_SQL = concat('insert into tempData select ins.creator, count(ins.id),  date_format(ins.createTime,"', dateFormat, '") from ins_item ins where 1=1');
if  startDate is not null then 
set where_SQL = concat(where_SQL, ' and date_format(ins.createTime,"', dateFormat, '") >= "', startDate, '"');
end if;
if  endDate is not null then 
set where_SQL = concat(where_SQL, ' and date_format(ins.createTime,"', dateFormat, '") <= "', endDate, '"');
end if;
if  userids is not null then 
set where_SQL = concat(where_SQL, ' and ins.creator in (', userids, ')');
end if;


set @sentence = concat(@V_SQL , where_SQL,  ' group by date_format(ins.createTime,"', dateFormat, '"), ins.creator');     
  /*select @sentence;*/
  prepare stmt from @sentence;                  -- 预编释一下。 “stmt”预编释变量的名称，
  execute stmt;                                 -- 执行SQL语句
  deallocate prepare stmt; -- 释放资源 


/*根据暂存数据组装出需要的数据*/
SELECT group_concat(tempData.typeDate, ':', tempData.data) as datas, uu.name as usname FROM tempData, user uu where tempData.userid = uu.id group by tempData.userid;    
END
/*带参数的 数据统计 ------------- end */