两张表中，根据关系表的关键字查询主表信息

迷南。 2023-10-04 09:19 6阅读 0赞

## 1、需求背景： ##

1需要根据概念的描述名称来查询概念信息  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzgxMTA1Nw_size_16_color_FFFFFF_t_70]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzgxMTA1Nw_size_16_color_FFFFFF_t_70 1]

![在这里插入图片描述][20210325172450794.png]  
![在这里插入图片描述][20210325172455583.png]  
2、解决步骤  
第一步，将关系表的概念描述名称（CONCEPTUAL\_DETAIL\_NAME），拼接到概念表中

SELECT
    	KC.*,
    	( SELECT wm_concat ( KCD.CONCEPTUAL_DETAIL_NAME ) FROM KNW_CONCEPTUAL_DETAIL KCD WHERE KCD.CONCEPTUAL_SOID = KC.CONCEPTUAL_SOID AND KCD.CONCEPTUAL_DETAIL_TYPE = 350 ) AS CONCEPTUAL_DETAIL_NAME 
    FROM
    	KNW_CONCEPTUAL KC

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzgxMTA1Nw_size_16_color_FFFFFF_t_70 2]  
第二步，将第一步查询的结果单独作为一张表，添加概念fsn模糊查询条件

SELECT
    	a.* 
    FROM
    	(
    	SELECT
    		KC.*,
    		( SELECT wm_concat ( KCD.CONCEPTUAL_DETAIL_NAME ) FROM KNW_CONCEPTUAL_DETAIL KCD WHERE KCD.CONCEPTUAL_SOID = KC.CONCEPTUAL_SOID AND KCD.CONCEPTUAL_DETAIL_TYPE = 350 ) AS CONCEPTUAL_DETAIL_NAME 
    	FROM
    		KNW_CONCEPTUAL KC 
    	) a 
    WHERE
    	a.CONCEPTUAL_DETAIL_NAME LIKE CONCAT( CONCAT( '%', UPPER( '动脉' ) ), '%' ) 
    	OR a.PY_CODE LIKE CONCAT( CONCAT( '%', UPPER( '动脉' ) ), '%' )

成功查询  
![在这里插入图片描述][2021032517363797.png]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzgxMTA1Nw_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20210325173106259.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzgxMTA1Nw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzgxMTA1Nw_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20210325173014474.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzgxMTA1Nw==,size_16,color_FFFFFF,t_70
[20210325172450794.png]: https://img-blog.csdnimg.cn/20210325172450794.png
[20210325172455583.png]: https://img-blog.csdnimg.cn/20210325172455583.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzgxMTA1Nw_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20210325173521464.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzgxMTA1Nw==,size_16,color_FFFFFF,t_70
[2021032517363797.png]: https://img-blog.csdnimg.cn/2021032517363797.png