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 */