mysql 存储 动态

函数一

DROP PROCEDURE IF EXISTS `rep_vol_age_distribution`;

CREATE DEFINER = `zysadmin`@`%` PROCEDURE `rep_vol_age_distribution`(IN codeLen int,IN districtId varchar(32),In isNotLevel int)
    SQL SECURITY INVOKER
BEGIN 
  -- 4028818811a15abe0111a1a517480004 广州市
 SET @districtId=districtId; 
 SET @codeLen=codeLen;
 
 
 SET @yearCol_15=CONCAT('select COUNT(*) as count,(year(now())- year(SUBSTR(u.idcard_code  FROM 7 FOR 6))) as years ',
   'from  users u where LENGTH(u.idcard_code) = 15 ');
  SET @yearCol_18=CONCAT('select COUNT(*) as count,(year(now())- year(SUBSTR(u.idcard_code FROM 7 FOR 8))) as years ',
   'from  users u where LENGTH(u.idcard_code) = 18 ');

  IF isNotLevel <> 1 AND districtId IS NOT NULL AND districtId <> '' THEN
   SET @p_permCode=(SELECT pd.perm_code from district pd where pd.district_id=districtId);
   SET @yearCol_15=CONCAT(@yearCol_15,'AND u.district_id IN (SELECT d.district_id from district d where d.perm_code like CONCAT(@p_permCode,"%")) ');
   SET @yearCol_18=CONCAT(@yearCol_18,'AND u.district_id IN (SELECT d.district_id from district d where d.perm_code like CONCAT(@p_permCode,"%")) ');
 ELSEIF isNotLevel = 1 AND districtId IS NOT NULL AND districtId <> '' THEN
   SET @yearCol_15=CONCAT(@yearCol_15,'AND u.district_id =@districtId ');
   SET @yearCol_18=CONCAT(@yearCol_18,'AND u.district_id =@districtId ');
  END IF;

 SET @yearCol_15=CONCAT(@yearCol_15,'GROUP BY years HAVING years BETWEEN 0 and 100 ORDER BY years ');
 SET @yearCol_18=CONCAT(@yearCol_18,'GROUP BY years HAVING years BETWEEN 0 and 100 ORDER BY years ');

  SET @v_sql=CONCAT('(',@yearCol_15,') UNION ALL (',@yearCol_18,')'); 
 IF codeLen =15 THEN SET @v_sql=@yearCol_15;
  ELSEIF codeLen =18 THEN SET @v_sql=@yearCol_18;
  END IF;

 SET @v_sql=CONCAT('SELECT CASE WHEN t.years BETWEEN 1 AND 20 THEN \'20岁及以下\' ',
       'WHEN t.years BETWEEN 21 AND 30 THEN \'21-30岁\' ',
       'WHEN t.years BETWEEN 31 AND 40 THEN \'31-40岁\' ',
       'WHEN t.years BETWEEN 41 AND 50 THEN \'41-50岁\' ',
       'WHEN t.years BETWEEN 51 AND 60 THEN \'51-60岁\' ',
       'ELSE \'60岁以上\' END age_stage ,SUM(t.count) as total FROM (',@v_sql,') t GROUP BY age_stage');

 PREPARE stmt from @v_sql;
 EXECUTE stmt ;
 DEALLOCATE PREPARE stmt;
END;

 

 函数二

DROP PROCEDURE IF EXISTS `rep_vol_serviceTime`;

CREATE DEFINER = `zysadmin`@`%` PROCEDURE `rep_vol_serviceTime`(In districtId varchar(32),IN startTime varchar(20) ,IN endTime varchar(20))
    COMMENT '按时间按地区按项目类型查询志愿服务时间分布'
BEGIN 
 
 SET @districtId=districtId; 
 SET @startTime=startTime;
  SET @endTime=endTime;

 SET @v_sql= concat('SELECT mt.type_name,SUM(res.sum_serhour) AS sum_serhour FROM (',
   'SELECT m.mission_type,SUM(msl.service_minute) /60 as sum_serhour ',
   'FROM mission_service_log msl ',
   'LEFT JOIN mission m ON m.mission_id=msl.mission_id WHERE 1=1 ');
 IF districtId IS NOT NULL AND districtId <> '' THEN SET @v_sql= concat(@v_sql,'AND m.district_id = @districtId ');
 END IF;
 IF startTime IS NOT NULL AND startTime <> '' THEN SET @v_sql= concat(@v_sql,'AND msl.check_on_date >= @startTime ');
 END IF;
 IF endTime IS NOT NULL AND endTime <> '' THEN SET @v_sql= concat(@v_sql,'AND msl.check_on_date <= @endTime ');
 END IF;
 SET @v_sql= concat(@v_sql,'GROUP BY msl.mission_id) AS res ',
   'LEFT JOIN mission_type mt ON mt.mission_type_id=res.mission_type ',
   'GROUP BY res.mission_type');
    
 PREPARE stmt from @v_sql;
 EXECUTE stmt ;
 DEALLOCATE PREPARE stmt;
END;