本文共 5038 字，大约阅读时间需要 16 分钟。

DELIMITER $$DROP PROCEDURE IF EXISTS `drugConsumptionStatistics`$$CREATE PROCEDURE drugConsumptionStatistics(  -- 输入参数	IN hosCode VARCHAR(128),-- 机构编码	IN prescriptionSource VARCHAR(128),-- 处方来源	IN deptCode VARCHAR(128),-- 科室编码	IN startTime VARCHAR(128),-- 开始时间	IN endTime VARCHAR(128),-- 结束时间	IN curPage INT, -- 当前页	IN pageSize INT, -- 每页的数据量		#输出参数	OUT totalCount INT,  #总记录数	OUT pageCount INT    #总页数)BEGIN	-- 计算起始行号	SET @startRow = pageSize * (curPage - 1);	SET @pageSize = pageSize;	SET @rowindex = 0;		SET @whereStr = '';	-- 机构编码	IF (hosCode<>'') THEN	   SET @whereStr = CONCAT(@whereStr, ' and tp.HosCode = ', hosCode);  END IF;		-- 处方来源	IF (prescriptionSource<>'') THEN	   SET @whereStr = CONCAT(@whereStr, ' and tp.PrescriptionSource = ', prescriptionSource);  END IF;		-- 科室	IF (deptCode<>'') THEN	   SET @whereStr = CONCAT(@whereStr, ' and tp.DeptCode = ', deptCode);  END IF;		-- 开始时间	IF (startTime<>'') THEN	   SET @whereStr = CONCAT(@whereStr, ' and tp.PrescriptionTime >= ', startTime);  END IF;		-- 结束时间	IF (endTime<>'') THEN	   SET @whereStr = CONCAT(@whereStr, ' and tp.PrescriptionTime <= ', endTime);  END IF;		SET @whereStr = CONCAT(@whereStr, ' group by tp.HosCode, tp.PrescriptionSource , tp.DeptCode ');	-- 抗菌药物相关数据统计	SET @AntiBacter = CONCAT('SELECT 	tp.HosCode as hosCode, 	tp.PrescriptionSource as prescriptionSource, 	tp.DeptCode as deptCode, 	count(DISTINCT(tp.JZTClaimNo)) as preOfAntiBacter, 	count(DISTINCT(tp.ClientCardCode)) as patOfAntiBacter   FROM t_prescription_drugs tpd   left join t_prescription tp   on tpd.JZTClaimNo = tp.JZTClaimNo 	where tpd.IsAntiBacter = 1 ', @whereStr);	-- 基药相关数据统计	SET @Basic = CONCAT('SELECT tp.HosCode as hosCode, tp.PrescriptionSource as prescriptionSource, tp.DeptCode as deptCode, count(DISTINCT(tp.JZTClaimNo)) as preOfBasic, count(DISTINCT(tp.ClientCardCode)) as patOfBasic FROM t_prescription_drugs tpd left join t_prescription tp on tpd.JZTClaimNo = tp.JZTClaimNo where tpd.IsBasic = 1 ', @whereStr);	-- 注射药相关数据统计	SET @Injection = CONCAT('SELECT 	tp.HosCode as hosCode, 	tp.PrescriptionSource as prescriptionSource, 	tp.DeptCode as deptCode, 	count(DISTINCT(tp.JZTClaimNo)) as preOfInjection, 	count(DISTINCT(tp.ClientCardCode)) as patOfInjection   FROM t_prescription_drugs tpd   left join t_prescription tp 	on tpd.JZTClaimNo = tp.JZTClaimNo 	where tpd.IsInjection = 1 ', @whereStr);	-- 处方信息	SET @Prescription = CONCAT('SELECT 	tp.HosCode as hosCode, 	tp.PrescriptionSource as prescriptionSource, 	tp.DeptCode as deptCode, 	tp.HosName as hosName, 	tp.DeptName as deptName, 	count(DISTINCT(tp.jZTClaimNo)) as totalOfPrescription,  count(DISTINCT(tp.ClientCardCode)) as totalOfPatient,             convert(count(DISTINCT(tpd.DrugCode))/count(tp.jZTClaimNo),decimal(10,2)) as aveOfPrescriptionDrugs, convert(count(DISTINCT(tpd.DrugCode))/count(DISTINCT(tp.ClientCardCode)),decimal(10,2)) as aveOfPatientDrugs 	from t_prescription_drugs tpd 	left join t_prescription tp 	on tpd.JZTClaimNo = tp.JZTClaimNo 	where 1 = 1 ', @whereStr);		SET @strSql = CONCAT(	#'select sql_calc_found_rows  @rowindex:=@rowindex+1 as rownumber,' #记录行号	'select sql_calc_found_rows ',	' x.*, 	  IFNULL(y.preOfAntiBacter, 0) as preOfAntiBacter, 		IFNULL(convert((y.preOfAntiBacter/x.totalOfPrescription),decimal(10,2)), 0) as preProportionsOfAnt, 		IFNULL(y.patOfAntiBacter, 0) as patOfAntiBacter, 		IFNULL(convert((y.patOfAntiBacter/x.totalOfPatient),decimal(10,2)), 0) as patProportionsOfAnt, 		IFNULL(z.preOfBasic, 0) as preOfBasic, 		IFNULL(convert((z.preOfBasic/x.totalOfPrescription),decimal(10,2)), 0) as preProportionsOfBasic, 		IFNULL(z.patOfBasic, 0) as patOfBasic, 		IFNULL(convert((z.patOfBasic/x.totalOfPatient),decimal(10,2)), 0) as patProportionsOfBasic,		IFNULL(w.preOfInjection, 0) as preOfInjection, 		IFNULL(convert((w.preOfInjection/x.totalOfPrescription),decimal(10,2)), 0) as preProportionsOfInjection, 		IFNULL(w.patOfInjection, 0) as patOfInjection, 		IFNULL(convert((w.patOfInjection/x.totalOfPatient),decimal(10,2)), 0) as patProportionsOfInjection		FROM (', @Prescription, ') x 		left join (', @AntiBacter, ') y 		on x.HosCode = y.HosCode and x.PrescriptionSource = y.PrescriptionSource and x.DeptCode = y.DeptCode 		left join (', @Basic, ') z 		on x.HosCode = z.HosCode and x.PrescriptionSource = z.PrescriptionSource and x.DeptCode = z.DeptCode 		left join (', @Injection, ') w 		on x.HosCode = w.HosCode and x.PrescriptionSource = w.PrescriptionSource and x.DeptCode = w.DeptCode limit '		,@startRow		,',' 		,@pageSize		);	  PREPARE strSql FROM @strSql;#定义预处理语句 	EXECUTE strSql;							#执行预处理语句 	DEALLOCATE PREPARE strSql;	#删除定义 	#通过 sql_calc_found_rows 记录没有使用 limit 语句的记录，使用 found_rows() 获取行数	SET totalCount = FOUND_ROWS();		#计算总页数	IF (totalCount <= pageSize) THEN		SET pageCount = 1;	ELSE IF (totalCount % pageSize > 0) THEN		SET pageCount = totalCount / pageSize + 1;	ELSE		SET pageCount = totalCount / pageSize;	END IF;	END IF;END;-- 测试CALL drugConsumptionStatistics('','','','','',1 ,10 ,@totalcount #输出总记录数,@pagecount #输出用页数);SELECT @totalcount,@pagecount;

转载地址：http://iwiti.baihongyu.com/