cdss-core/doc/014.20210727提示规则新需求/temp.sql

USE `med_2021`;
-- 临时表
DROP TABLE IF EXISTS `temp_msg`;
CREATE TABLE `temp_msg` (
  `cond_id` bigint(20) DEFAULT NULL COMMENT 'con_id',
  `msg` varchar(255) DEFAULT NULL COMMENT '提示语',
   KEY (`cond_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

-- 更新提示语：原因及建议
update kl_rule_condition a, temp_msg b set a.msg = b.msg where a.id = b.cond_id;

-- 查询已有的开单条目
SELECT
	tt1.cond_id,
	tt1.par_name,
	tt1.par_type,
	tt2.lib_name base_name,
	xx2. NAME base_type,
	CASE tt1.type
WHEN '4' THEN
	'过敏原'
WHEN '5' THEN
	'辅检开单互斥'
ELSE
	''
END other_type,
 tt1.max_value,
 tt1.max_operator,
 tt1.min_value,
 tt1.min_operator,
 tt1.cond_msg
FROM
	(
		SELECT
			c1.lib_name par_name,
			c1.id par_id,
			x1.`name` par_type,
			t1.id rule_id,
			t1.description,
			t1.rule_type,
			t1.has_sub_cond,
			t1.msg,
			t3.type,
			t3.concept_id base_id,
			t3.eq_value,
			t3.min_value,
			t3.min_operator,
			t3.min_unit,
			t3.max_value,
			t3.max_operator,
			t3.max_unit,
			t2.id cond_id,
			t2.msg cond_msg
		FROM
			kl_concept c1,
			kl_lexicon x1,
			kl_rule t1,
			kl_rule_condition t2,
			kl_rule_base t3
		WHERE
			c1.is_deleted = 'N'
		AND t1.is_deleted = 'N'
		AND t2.is_deleted = 'N'
		AND t3.is_deleted = 'N'
		AND c1.`status` = 1
		AND t1.`status` = 1
		AND c1.id = t1.concept_id
		AND t1.id = t2.rule_id
		AND t2.rule_base_id = t3.id
		AND t1.rule_type = 1
		AND x1.`code` = c1.lib_type
	) tt1
LEFT JOIN kl_concept tt2 ON tt2.is_deleted = 'N'
AND tt2.`status` = 1
AND tt1.base_id = tt2.id
LEFT JOIN kl_lexicon xx2 ON tt2.lib_type = xx2. CODE
ORDER BY
	rule_id;