笔记——》记录一个mysql存储过程

前言

大部分情况下，不推荐写太复杂的sql语句，所有的业务逻辑应写到代码中。此篇文章只为记录一下这个存储过程，方便以后在忘记存储过程或一些函数的时候拿过来参考

代码

存储过程代码如下：

CREATE DEFINER=`myname`@`%` PROCEDURE `md5撞库详细（时间段）`(IN beginDateStr varchar(50),IN endDateStr varchar(50))
BEGIN
		IF beginDateStr = '' then
			SET beginDateStr= CONCAT(date_sub(curdate(),interval 1 day),' 00:15:00');
		end IF; 
		
		IF endDateStr = '' then
			SET endDateStr=CONCAT(date_sub(STR_TO_DATE(beginDateStr,'%Y-%m-%d'),interval -1 day),' 00:15:00');
		else 
			SET endDateStr=CONCAT(date_sub(STR_TO_DATE(endDateStr,'%Y-%m-%d'),interval -1 day),' 00:15:00');
		end IF;

		select 
			a.dateStr as '时间',
			a.channel_count as '渠道入库量',
			a.md5_up_count as '撞库上传量',
			a.md5_success_count as '撞库成功量',
			concat(convert(a.md5_success_count/a.md5_up_count*100,decimal(10,2)) ,'%') as '成功撞库占比',
			a.success_count as '投保成功量',
			concat(convert(a.success_count/a.md5_success_count*100,decimal(10,2)) ,'%') as '投保成功占比',
			b.name as '投保订单',
			b.product_count as '订单数量',
			concat(convert(b.product_count/a.success_count*100,decimal(10,2)) ,'%') as '订单占比'
		from 
		 (
				select 
					DATE_FORMAT(mc.create_time,'%Y-%m-%d')  as dateStr,
					count(id) channel_count,
					count(IF(mc.`status`!='MD5_DISCARD',true,null)) md5_up_count ,
					count(IF(mc.`status`!='MD5_DISCARD'&&mc.`status`!='MD5_FAIL',true,null)) md5_success_count,
					count(IF(mc.`status`='INSURANCE_SUCCESS',true,null)) success_count  
				from x_md5_compare mc 
				where 1=1
				and mc.create_time > STR_TO_DATE(beginDateStr,'%Y-%m-%d %H:%i:%s') 
				and mc.create_time <= STR_TO_DATE(endDateStr,'%Y-%m-%d %H:%i:%s')
				GROUP BY DATE_FORMAT(mc.create_time,'%Y-%m-%d')
		 ) a
		left join 
			(
				SELECT
					DATE_FORMAT( mc.create_time, '%Y-%m-%d' ) AS dateStr,
					cr.name,
					count( cr.name ) product_count 
				FROM
					x_policy_leads pl
					LEFT JOIN x_company_requirements cr ON pl.company_requirements_id = cr.id
					LEFT JOIN x_md5_compare mc ON pl.id = mc.new_policy_id 
				WHERE
					1 = 1 
					AND mc.`status` = 'INSURANCE_SUCCESS' 
					AND mc.create_time > STR_TO_DATE( beginDateStr, '%Y-%m-%d %H:%i:%s' ) 
					AND mc.create_time <= STR_TO_DATE( endDateStr, '%Y-%m-%d %H:%i:%s' ) 
				GROUP BY
					DATE_FORMAT( mc.create_time, '%Y-%m-%d' ),
					cr.name
			) b
		on a.dateStr=b.dateStr
		order by a.dateStr ,CONVERT(b.name USING gbk);
END

存储过程调用代码如下：

CALL md5撞库详细（时间段）('2023-05-31','2023-06-07');

重要函数记录

1. CONCAT(date_sub(curdate(),interval 1 day),' 00:15:00')：将 当前日期 减去 1天，并在末尾拼接字符串 00:15:00
2. IF 1=1 then '回答正确' else '回答错误' end IF ：if else语句
3. concat(convert(a.md5_success_count/a.md5_up_count*100,decimal(10,2)) ,'%') ：将两个字段相除的结果保留两位小数，并在后面拼接百分号
4. STR_TO_DATE(beginDateStr,'%Y-%m-%d %H:%i:%s') : 将字符串转为date类型
5. DATE_FORMAT( mc.create_time, '%Y-%m-%d' ) : 将date类型转为字符串
6. count(IF(mc.status!='MD5_DISCARD'&&mc.status!='MD5_FAIL',true,null))：查询条件，统计字段状态值不为MD5_DISCARD和MD5_FAIL的数量

结语

一篇小笔记，仅记录而已，没什么值得学习的