如何将查询/子查询的结果传递给另一个查询，并将结果合并在一起

Question

已回答！CTE是最好的方法，使用描述性虚拟变量名称

我有两个相关的表concept，它描述了来自不同词汇表(vocabulary_id)的所有数据库范围的概念(concept_code)，并将它们与唯一的标准化IDconcept_id和只有三列的concept_relationship联系起来，即concept_id_1、concept_id_2和relationship_id。关系ID描述ID 1与ID 2的关系。数据库团队试图使用OMOP标准尽可能多地将概念标准化为一个ID，因此使用"；映射到"；关系我有一组代码，它们对应于主题专家在特定词汇表中定义的ID，这些词汇表不一定包括数据库团队选择的标准化概念。我想要的最终产品是一个表，表示SME定义的代码/ID和它们"；映射到"；。

########################################################################

新代码似乎按预期运行，使用了WITH语句：

WITH query1 AS (
SELECT 
* 
FROM 
concept 
WHERE 
concept_code IN ("398.4", "I45")
AND (
vocabulary_id IN ("ICD9","ICD10")
)
)
SELECT *
FROM concept
WHERE concept_id IN (
SELECT concept_id_2
FROM concept_relationship cr
INNER JOIN query1 q1 ON cr.concept_id_1 = q1.concept_id
WHERE relationship_id = "Maps to"
)
UNION 
SELECT * FROM query1

########################################################################

我已经尝试了以下查询：

SELECT 
foo.* 
FROM 
(
SELECT 
* 
FROM 
concept 
WHERE 
concept_code IN ("398.4", "I45") 
AND (
vocabulary_id IN ("ICD9", "ICD10")
)
) AS foo 
UNION 
SELECT 
* 
FROM 
concept 
WHERE 
concept_id IN (
SELECT 
concept_id_2 
FROM 
concept_relationship cr 
INNER JOIN foo ON cr.concept_id_1 = foo.concept_id 
WHERE 
relationship_id = "Maps to"
)

但它返回一个"；无效的争论"；错误，我猜是因为我无法将UNION下面的子查询foo传递给其他查询。有更顺畅的方法吗？我在下面包含了一些伪表，以尝试再现性，这似乎在数据库端起作用，但为了简洁起见，没有包含所有列。

概念：

| concept_id  | concept_code | vocabulary_id |
| ----------- | ------------ |---------------|
| 123         | 398.4        | ICD9          |
| 111         | I45          | ICD10         |
| 145         | 45155841     | SNOMED        |

概念_关系：

| concept_id_1 | concept_id_2 | relationship_id|
| -----------  | ------------ |--------------- |
| 123          | 145          | Maps to        |
| 111          | 145          | Maps to        |
| 145          | 145          | Maps to        |
| 145          | 111          | Maps from      |
| 145          | 123          | Maps from      |
| 145          | 145          | Maps from      |

CREATE TABLE `concept` (
`concept_id` VARCHAR NOT NULL, 
`concept_code` VARCHAR NOT NULL, 
`vocabulary_id` VARCHAR NOT NULL, 
PRIMARY KEY (`concept_id`)
);
INSERT INTO concept (
concept_id, concept_code, vocabulary_id
) 
VALUES 
("123", "388.4", "ICD9"), 
("111", "I45", "ICD10"), 
("145", "45155841 ", "SNOMED");

CREATE TABLE `concept` (
`concept_id_1` VARCHAR NOT NULL, 
`concept_id_2` VARCHAR NOT NULL, 
`relationship_id` VARCHAR NOT NULL, 
PRIMARY KEY (`concept_id_1`)
);
INSERT INTO concept_relationship (
concept_id_1, concept_id_2, relationship_id
) 
VALUES 
("123", "145", "Maps to"), 
("111", "145", "Maps to"), 
("145", "145", "Maps to"), 
("145", "111", "Maps from"), 
("145", "123", "Maps from"), 
("145", "145", "Maps from");

Answer 1

问题是你在并集的另一边使用foo，它看不到它。但CTE可以像这个一样提供帮助

WITH foo AS (
SELECT * 
FROM concept 
WHERE concept_code IN ("398.4", "I45") AND
vocabulary_id IN ("ICD9", "ICD10")
)
SELECT *
FROM foo
UNION 
SELECT * 
FROM concept 
WHERE concept_id IN (
SELECT concept_id_2 
FROM concept_relationship cr 
JOIN foo ON cr.concept_id_1 = foo.concept_id 
WHERE relationship_id = "Maps to"
)

注意，UNION之后的第二个查询可以重写为

SELECT * 
FROM concept 
JOIN concept_relationship cr ON cr.concept_id_2 = concept.conecpt_id
AND cr.relationship_id = "Maps to"
JOIN foo on cr.concept_id_1 = foo.concept_id

使用联接。