如何将查询/子查询结果传递给另一查询并执行UNION合并

涉及核心表说明 #

• concept表：存储全库所有词表（字段vocabulary_id）下的概念编码（字段concept_code），每个概念分配全局唯一的标准化IDconcept_id
• concept_relationship表：仅包含concept_id_1、concept_id_2、relationship_id三个字段，记录两个概念ID的关联关系。当relationship_id值为"Maps to"时，代表concept_id_1映射到concept_id_2。数据库团队基于OMOP标准做概念标准化时，会将源词表ID通过该关系映射到对应的父级标准ID。

需求说明 #

领域专家（SME）在特定词表下维护了一批自定义编码，这批编码没有完全覆盖数据库团队选定的标准概念，需要查询返回同时包含SME原始编码/ID、以及这些编码映射到的对应标准ID的合并结果集。

问题与解决方案 #

最初实现时尝试在UNION的前半段定义子查询foo，在UNION后半段直接关联foo，执行时触发INVALID ARGUMENT报错。原因是UNION拼接的多个查询块作用域相互独立，后半段无法引用前半段定义的子查询别名。
经测试验证，最优实现方案是使用WITH语法定义CTE（公共表表达式），搭配语义清晰的变量名即可稳定满足需求。

错误写法示例 #

-- 执行报错：UNION后半段无法识别前半段定义的子查询别名foo
(
SELECT 
  c.concept_id AS original_concept_id,
  c.concept_code AS original_concept_code,
  c.concept_id AS final_concept_id
FROM concept c
WHERE c.vocabulary_id = 'YOUR_SME_VOCAB_ID'
  AND c.concept_code IN ('YOUR_SME_CODE_1','YOUR_SME_CODE_2')
) foo
UNION ALL
SELECT 
  foo.original_concept_id,
  foo.original_concept_code,
  cr.concept_id_2 AS final_concept_id
FROM foo
JOIN concept_relationship cr 
  ON foo.original_concept_id = cr.concept_id_1
WHERE cr.relationship_id = 'Maps to';

正确写法（CTE实现） #

-- 提前通过CTE统一定义SME原始编码集合，全局可引用
WITH sme_raw_concepts AS (
  SELECT 
    c.concept_id AS original_concept_id,
    c.concept_code AS original_concept_code
  FROM concept c
  WHERE c.vocabulary_id = 'YOUR_SME_VOCAB_ID' -- 替换为实际SME自定义词表ID
    AND c.concept_code IN ('YOUR_SME_CODE_1','YOUR_SME_CODE_2') -- 替换为实际SME定义的编码列表
)
-- 第一部分结果：SME原始编码本身
SELECT 
  original_concept_id,
  original_concept_code,
  original_concept_id AS final_concept_id
FROM sme_raw_concepts
UNION ALL
-- 第二部分结果：原始编码映射到的标准概念
SELECT 
  src.original_concept_id,
  src.original_concept_code,
  cr.concept_id_2 AS final_concept_id
FROM sme_raw_concepts src
JOIN concept_relationship cr 
  ON src.original_concept_id = cr.concept_id_1
WHERE cr.relationship_id = 'Maps to';

扩展说明：如果业务上存在多级映射（即映射到的概念仍存在向上的"Maps to"关系），可以将上述CTE改写为递归CTE拉取全链路映射结果，常规单级映射场景下上述写法可直接使用。

内容的提问来源于stack exchange，提问作者Moss_and_Bones