mysql动态存储_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;

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值