Generation

text to sql syntaxFri, 14 Apr 2023

在MySQL5.7中CREATE PROCEDURE `proc_calc_patient_avg_cost`(IN `p_patientID` int,OUT `avg_cost` double) BEGIN DECLARE t_tatalcost FLOAT; #病人总的费用 DECLARE t_visit_count INT; #病人看病的总次数 #计算总金额 SELECT SUM(checkItemCost) into t_totalcost from prescription as pr join checkitem as ch on ch.checkItemID=pr.checkItemID WHERE pr.patientID=p_patientID; #计算总次数 SELECT COUNT(0) into t_visit_cont from ( SELECT COUNT(0) from prescription where patientID=p_patientID GROUP BY examDate )as t; #计算平均金额 set avg_cost=t_totalcost/t_visit_count; END CALL proc_calc_patient_avg_cost(1,@a); SELECT @a ;改进或优化代码

BEGIN DECLARE t_tatalcost FLOAT; #病人总的费用 DECLARE t_visit_count INT; #病人看病的总次数 #计算总金额 SELECT SUM(checkItemCost) into t_totalcost from prescription as pr join checkitem as ch on ch.checkItemID=pr.checkItemID WHERE pr.patientID=p_patientID; #计算总次数 SELECT COUNT(0) into t_visit_cont from ( SELECT COUNT(0) from prescription where patientID=p_patientID GROUP BY examDate )as t; #计算平均金额 set avg_cost=t_totalcost/t_visit_count; END CALL proc_

Questions about programming?Chat with your personal AI assistant