mysql使用小整理

本文介绍了一个MySQL存储过程的实际应用案例,该过程根据不同的时间段计算收费率。文章详细展示了如何通过前台参数调用存储过程,以及如何处理不同条件下的SQL查询。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

今天,使用mysql,使用知识点有:

1.批量导入测试数据,即同一条数据重复插入若干遍,写了一个存储过程。

2.使用函数,自动创建不重复的主键id

遇到的问题,

1.写存储过程的时候,遇到了mysql的保留字,name,code,需要进行转义,使用反引号,位于键盘esc的下面`````这类符号

2.使用uuid()函数生成主键时,需要方法来去掉中间连接符-,使用replace()函数

3.使用spring mvc进行前台数据交互,使用百度API解析经纬度,无法进入action,2处地方修改,第一为web.xml的过滤器问题,第二是需要在xml文件中配置action文件。


今天使用mysql的存储过程,因为需要前台传参数(开始月份和截止月份),确定所计算的收费率,并且不同的参数所执行的sql是不一样的,所以前台使用拼接sql的方式调用存储过程:

String timeType=(String) parameter.get("timeType");
		String beginMonth=(String) parameter.get("beginMonth");
		String endMonth=(String) parameter.get("endMonth");
		String companyId=(String) parameter.get("companyId");
		String treeCode=(String) parameter.get("treeCode");
		String dictItemName=(String) parameter.get("dictItemName");
		String sql="call pro_month_rate ('"+timeType+"','"+beginMonth+"','"+endMonth+"','"+companyId+"','"+treeCode+"','"+dictItemName+"')";

使用一个list的object[]数组接收

List<Object[]> list=viewReportRateMonthDao.findListBySql(sql);

遍历集合,存入entity

for (Object[] objects : list) {
				ViewReportRateMonth entity=new ViewReportRateMonth();
				entity.setReceivableDetailId(objects[0].toString());
				entity.setCompanyId(objects[1].toString());
				entity.setCode(objects[2].toString());
				entity.setLevel2Name(objects[3].toString());
				entity.setThisMouth(objects[6].toString());
				entity.setDictItemName(objects[7].toString());
				entity.setDictItemCode(objects[8].toString());
				entity.setAmountReceivable(new BigDecimal(objects[9].toString()));
				entity.setActuallyAmount(new BigDecimal(objects[10].toString()));
				entity.setReceivableRate(objects[11].toString());
				entity.setTreeCode(objects[13].toString());
				listEntity.add(entity);
			}

上面是后台代码,

接下来是创建存储过程代码:

demo是

create procedure pro_month_rate(in timeType varchar(32), in beginMonth varchar(32),in endMonth varchar(32),in companyId varchar(32) ,in treeCode varchar(32),in dictItemName varchar(32))
begin 
if timeType='month' THEN 
SELECT
	`vrr`.`receivable_detail_id` AS `receivable_detail_id`,
	`vrr`.`company_id` AS `company_id`,
vrr.house_name as `code`,
	`vrr`.`level2_name` AS `level2_name`,
vrr.level3_name,
vrr.level4_name,
	`vrr`.`this_mouth` AS `this_mouth`,
	`vrr`.`dict_item_name` AS `dict_item_name`,
	`vrr`.`dict_item_code` AS `dict_item_code`,
	sum(`vrr`.`fee_amount`) AS `amount_receivable`,
	sum(

		IF (
			(`vrr`.`state` = '1'),
			(
				`vrr`.`fee_amount` + `vrr`.`pref_amount`
			),
			0
		)
	) AS `actually_amount`,
	concat(
		format(
			(
				(
					sum(

						IF (
							(`vrr`.`state` = '1'),
							(
								`vrr`.`fee_amount` + `vrr`.`pref_amount`
							),
							0
						)
					) / sum(
						(
							`vrr`.`fee_amount` + `vrr`.`payment_amount`
						)
					)
				) * 100
			),
			0
		),
		'%'
	) AS `receivable_rate`,
	`vrr`.`collect_date` AS `collect_date`,
vrr.tree_code
FROM
	`view_report_base_rate` `vrr`
where vrr.tree_code like CONCAT(treeCode,'%') and vrr.company_id=companyId and vrr.this_mouth= beginMonth and FIND_IN_SET(vrr.dict_item_name,dictItemName)
GROUP BY
	`vrr`.`dict_item_name` ,
vrr.company_id ,
vrr.this_mouth ,
vrr.level3_name ;
ELSEIF timeType='year' THEN 
SELECT
	`vrr`.`receivable_detail_id` AS `receivable_detail_id`,
	`vrr`.`company_id` AS `company_id`,
vrr.house_name as `code`,
	`vrr`.`level2_name` AS `level2_name`,
vrr.level3_name,
vrr.level4_name,
	`vrr`.`this_mouth` AS `this_mouth`,
	`vrr`.`dict_item_name` AS `dict_item_name`,
	`vrr`.`dict_item_code` AS `dict_item_code`,
	sum(`vrr`.`fee_amount`) AS `amount_receivable`,
	sum(

		IF (
			(`vrr`.`state` = '1'),
			(
				`vrr`.`fee_amount` + `vrr`.`pref_amount`
			),
			0
		)
	) AS `actually_amount`,
	concat(
		format(
			(
				(
					sum(

						IF (
							(`vrr`.`state` = '1'),
							(
								`vrr`.`fee_amount` + `vrr`.`pref_amount`
							),
							0
						)
					) / sum(
						(
							`vrr`.`fee_amount` + `vrr`.`payment_amount`
						)
					)
				) * 100
			),
			0
		),
		'%'
	) AS `receivable_rate`,
	`vrr`.`collect_date` AS `collect_date`,
vrr.tree_code
FROM
	`view_report_base_rate` `vrr`
where vrr.tree_code like CONCAT(treeCode,'%') and vrr.company_id=companyId and vrr.this_mouth like CONCAT(beginMonth,'%') and FIND_IN_SET(vrr.dict_item_name,dictItemName)
GROUP BY
	`vrr`.`dict_item_name` ,
vrr.company_id ,
vrr.this_mouth ,
vrr.level3_name ;
else 
SELECT
	`vrr`.`receivable_detail_id` AS `receivable_detail_id`,
	`vrr`.`company_id` AS `company_id`,
vrr.house_name as `code`,
	`vrr`.`level2_name` AS `level2_name`,
vrr.level3_name,
vrr.level4_name,
	`vrr`.`this_mouth` AS `this_mouth`,
	`vrr`.`dict_item_name` AS `dict_item_name`,
	`vrr`.`dict_item_code` AS `dict_item_code`,
	sum(`vrr`.`fee_amount`) AS `amount_receivable`,
	sum(

		IF (
			(`vrr`.`state` = '1'),
			(
				`vrr`.`fee_amount` + `vrr`.`pref_amount`
			),
			0
		)
	) AS `actually_amount`,
	concat(
		format(
			(
				(
					sum(

						IF (
							(`vrr`.`state` = '1'),
							(
								`vrr`.`fee_amount` + `vrr`.`pref_amount`
							),
							0
						)
					) / sum(
						(
							`vrr`.`fee_amount` + `vrr`.`payment_amount`
						)
					)
				) * 100
			),
			0
		),
		'%'
	) AS `receivable_rate`,
	`vrr`.`collect_date` AS `collect_date`,
vrr.tree_code
FROM
	`view_report_base_rate` `vrr`
where vrr.tree_code like CONCAT(treeCode,'%') and vrr.company_id=companyId and vrr.this_mouth >= beginMonth and vrr.this_mouth<= endMonth and FIND_IN_SET(vrr.dict_item_name,dictItemName)
GROUP BY
	`vrr`.`dict_item_name` ,
vrr.company_id ,
vrr.this_mouth ,
vrr.level3_name ;
end if;
end;

看着复杂,其实就是一个创建过程以及简单的if ,else逻辑

create procedure pro_month_rate()

begin
if timeType='month' THEN
select * from...

ELSEIF timeType='year' THEN
select * from ..   ; //另一段sql

else
select * from ...    ; //最后sql

end if;

end;

使用了一个函数FIND_IN_SET(table.field,'数组'),多个参数适用in和都好分开。


根据b表数据更新a表某个字段

update a inner join b on a.bid=b.id set a.x=b.x,a.y=b.y ;





评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值