函数一
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;