月账单SQL

问题描述：有两张表 明细表和登记表，两个表之间用就诊编号做关联；

我需要从明细表中查找ylfze（医疗费总额），zlze（自理总额），zfze（自费总额），JJZFZE（基金支付总额），XJZFZE（现金支付总额），DBZF（大病支付），CBRLB（参保人类别）=>月结人员类别 ，type（就诊类型）=>月结医疗类别 ， 根据转换的月结人员类别和月结医疗类别做分组统计transtype （就医人次）。 CBRLB字段对应以下映射表中的【人员待遇类别】将其转换为月结人员类别，如果存在一个人员待遇类别对应两个月结医疗类别的，则需要根据明细表中的就诊编号和关联登记表查询出HMLB字段，根据HMLB字段判断月结人员类别，type字段则是对应一下映射表中的【交易结算类别】表，

问题补充：以下是两个表的结构及字段说明。

映射表：

分析及结果：

分析：

第一步：关联两张表 查询出基础所需字段：

SELECT
	t1.ylfze ylfze,
	t1.zlze zlze,
	t1.zfze zfze,
	t1.JJZFZE JJZFZE,
	t1.XJZFZE XJZFZE,
	t1.DBZF DBZF,
	t2.CBRLB CBRLB,
	t1. TYPE yjyllb,
	t1.transtype rc
FROM
	lenovo_mis.mis_settlement_info_jh t1,
	MIS_REGISTER_INFO_JH t2
WHERE
	t1.jzbh = t2.jzbh

第二步：使用casewhen并联系映射表做映射；将CBRLB 和 TYPE 转换成月结人员类别，和月结医疗类别

		SELECT
			t1.ylfze ylfze,
			t1.zlze zlze,
			t1.zfze zfze,
			t1.JJZFZE JJZFZE,
			t1.XJZFZE XJZFZE,
			t1.DBZF DBZF,
			(
				CASE
				WHEN t2.CBRLB IN (
					'10',
					'11',
					'13',
					'14',
					'20',
					'21',
					'23',
					'24'
				) THEN
					'10'
				WHEN t2.CBRLB IN ('12', '22') THEN
					'20'
				WHEN t2.CBRLB = '31' THEN
					'70'
				WHEN t2.CBRLB IN ('33', '34', '32') THEN
					'71'
				WHEN t2.CBRLB = '60' THEN
					'30'
				WHEN t2.CBRLB = '50' THEN
					'60'
				WHEN t2.CBRLB = '80' THEN
					'40'
				WHEN t2.CBRLB = '30'
				AND T2.CBRLB = '31' THEN
					'40'
				WHEN t2.CBRLB = '30'
				AND T2.CBRLB = '38' THEN
					'41'
				WHEN t2.hmlb = '40'
				AND T2.CBRLB = '36' THEN
					'50'
				WHEN t2.hmlb = '40'
				AND T2.CBRLB = '45' THEN
					'51'
				END
			) yjrylb,
			(
				CASE
				WHEN t1. TYPE IN ('1', '2', '13', '14') THEN
					'1'
				WHEN t1. TYPE IN ('5', '12') THEN
					'2'
				WHEN t1. TYPE IN ('3', '4') THEN
					'3'
				WHEN t1. TYPE IN ('6', '7', '8') THEN
					'4'
				WHEN t1. TYPE IN ('10', '9') THEN
					'6'
				WHEN t1. TYPE = '11' THEN
					'5'
				END
			) yjyllb,
			t1.transtype rc
		FROM
			lenovo_mis.mis_settlement_info_jh t1,
			MIS_REGISTER_INFO_JH t2
		WHERE
			t1.jzbh = t2.jzbh
		AND SUBSTR (JSSJ, 1, 6) = '201901'

第三步：按照第二部查询出来的yjrylb和yjyllb做分组，计算 rc

SELECT
	n1.YJRYLB,
	n1.YJYLLB,
	SUM (n1.ylfze) ylfze,
	SUM (n1.zlze) zlze,
	SUM (n1.zfze) zfze,
	SUM (n1.JJZFZE) JJZFZE,
	SUM (n1.XJZFZE) XJZFZE,
	SUM (n1.DBZF) DBZF,
	SUM (
		CASE
		WHEN n1.rc = '1' THEN
			- 1
		WHEN n1.rc = '0' THEN
			1
		END
	) rc
FROM
	(
		SELECT
			t1.ylfze ylfze,
			t1.zlze zlze,
			t1.zfze zfze,
			t1.JJZFZE JJZFZE,
			t1.XJZFZE XJZFZE,
			t1.DBZF DBZF,
			(
				CASE
				WHEN t2.CBRLB IN (
					'10',
					'11',
					'13',
					'14',
					'20',
					'21',
					'23',
					'24'
				) THEN
					'10'
				WHEN t2.CBRLB IN ('12', '22') THEN
					'20'
				WHEN t2.CBRLB = '31' THEN
					'70'
				WHEN t2.CBRLB IN ('33', '34', '32') THEN
					'71'
				WHEN t2.CBRLB = '60' THEN
					'30'
				WHEN t2.CBRLB = '50' THEN
					'60'
				WHEN t2.CBRLB = '80' THEN
					'40'
				WHEN t2.CBRLB = '30'
				AND T2.CBRLB = '31' THEN
					'40'
				WHEN t2.CBRLB = '30'
				AND T2.CBRLB = '38' THEN
					'41'
				WHEN t2.hmlb = '40'
				AND T2.CBRLB = '36' THEN
					'50'
				WHEN t2.hmlb = '40'
				AND T2.CBRLB = '45' THEN
					'51'
				END
			) yjrylb,
			(
				CASE
				WHEN t1. TYPE IN ('1', '2', '13', '14') THEN
					'1'
				WHEN t1. TYPE IN ('5', '12') THEN
					'2'
				WHEN t1. TYPE IN ('3', '4') THEN
					'3'
				WHEN t1. TYPE IN ('6', '7', '8') THEN
					'4'
				WHEN t1. TYPE IN ('10', '9') THEN
					'6'
				WHEN t1. TYPE = '11' THEN
					'5'
				END
			) yjyllb,
			t1.transtype rc
		FROM
			lenovo_mis.mis_settlement_info_jh t1,
			MIS_REGISTER_INFO_JH t2
		WHERE
			t1.jzbh = t2.jzbh
		AND SUBSTR (JSSJ, 1, 6) = '201901'
	) n1
GROUP BY
	n1.YJRYLB,
	n1.YJYLLB