mysql存储过程传入参数 实现动态列查询

mysql存储过程传入参数 实现动态列查询

DROP PROCEDURE IF EXISTS work_order_num;
CREATE PROCEDURE work_order_num(
    IN proId VARCHAR(255),
    IN userName VARCHAR(64)
)
BEGIN
SET @EE='';
SET @proId = proId;
SET @userName = userName;
SELECT b.* into @EE from ( SELECT @EE:=CONCAT(@EE,'SUM(IF(month=''',update_date1,'''',',work_order_num,0)) AS ''',update_date1,''',') col
FROM (
    SELECT DATE_FORMAT(date_add(curdate(),interval -6 month),'%Y-%m') update_date1
    UNION
    SELECT DATE_FORMAT(date_add(curdate(),interval -5 month),'%Y-%m') update_date1
    UNION
    SELECT DATE_FORMAT(date_add(curdate(),interval -4 month),'%Y-%m') update_date1
) A) b ORDER BY b.col desc limit 0,1 ;

SET @EE1='';
SELECT b.* into @EE1 from ( SELECT @EE1:=CONCAT(@EE1,'SUM(IF(month=''',update_date1,'''',',work_order_num,0)) AS ''',update_date1,''',') col
FROM (
     SELECT DATE_FORMAT(date_add(curdate(),interval -3 month),'%Y-%m') update_date1
     UNION
     SELECT DATE_FORMAT(date_add(curdate(),interval -2 month),'%Y-%m') update_date1
     UNION
     SELECT DATE_FORMAT(date_add(curdate(),interval -1 month),'%Y-%m') update_date1
 ) A) b ORDER BY b.col desc limit 0,1 ;
if @userName != 'admin' THEN
if @proId = '' THEN
SET @QQ=CONCAT('SELECT tb.project_name,
sm.id manufactor_id,
sm.name manufactor_name,',CONCAT(@EE,LEFT(@EE1,LENGTH(@EE1)-1)),'
FROM tb_maintain_log_statistics_month tb
INNER JOIN sys_manufactor sm on sm.id = tb.manufactor_id
WHERE tb.project_id =\'\'
GROUP BY tb.manufactor_id,tb.project_id');
ELSE
SET @QQ=CONCAT('SELECT tb.project_name,
sm.id manufactor_id,
sm.name manufactor_name,',CONCAT(@EE,LEFT(@EE1,LENGTH(@EE1)-1)),'
FROM tb_maintain_log_statistics_month tb
INNER JOIN sys_manufactor sm on sm.id = tb.manufactor_id
WHERE tb.project_id in (',proId,')
GROUP BY tb.manufactor_id,tb.project_id');
END if;
ELSE
SET @QQ=CONCAT('SELECT tb.project_name,
sm.id manufactor_id,
sm.name manufactor_name,',CONCAT(@EE,LEFT(@EE1,LENGTH(@EE1)-1)),'
FROM tb_maintain_log_statistics_month tb
INNER JOIN sys_manufactor sm on sm.id = tb.manufactor_id
GROUP BY tb.manufactor_id,tb.project_id');
END if;
PREPARE stmt2 FROM @QQ;
EXECUTE stmt2;

END;