将一个SQL查询的结果添加到另一个查询中

Question

我对SQL查询不是很了解，我有一个问题

SELECT PERSON_REF 
FROM   IMAGES 
WHERE  PERSON_REF IN (SELECT _PERSON_ID 
                      FROM   PERSONS 
                      WHERE  REGION_REF = (SELECT _REGION_ID 
                                           FROM   REGIONS 
                                           WHERE  REGION_ABB = "EU")) 
GROUP  BY PERSON_REF 
HAVING Count(PERSON_REF) >= 3 

这个查询给我返回的结果如下：

2  
4  
5  
6  

现在我想在另一个查询中使用这个结果，应该怎么做呢？需要创建循环吗还是有其他方法？

SELECT PERSON_REF 
FROM   IMAGES 
WHERE  EFFECT_REF = 2 
       AND PERSON_REF IN ( 2, 4, 5, 6 ) 

- sHa Xahid

---

你能不能在开发代码中做这个，或者必须使用SQL？你看过MySQL中的函数了吗？ - Daniel Casserly

好的，但如何在SQL中实现呢？ - sHa Xahid

Answer 1

select Person_Ref from Images  where Person_Ref IN ( 
Select _Person_ID from Persons where
Region_Ref in (select _Region_ID from Regions where Region_Abb = "EU"))
and EFFECT_REF = 2 
group by Person_Ref HAVING COUNT(Person_Ref) >= 3

- Vikram Jain

Answer 2

试试这个：

select Person_Ref from Images where Effect_Ref = 2 and Person_Ref IN 
( your first query which gives 2 4 5 6)

- Joe G Joseph

Answer 3

试试这个：

SELECT PERSON_REF 
FROM   IMAGES 
WHERE  EFFECT_REF = 2 
       AND PERSON_REF IN (SELECT PERSON_REF 
                          FROM   IMAGES 
                          WHERE  PERSON_REF IN (SELECT _PERSON_ID 
                                                FROM   PERSONS 
                                                WHERE 
                                 REGION_REF = (SELECT _REGION_ID 
                                               FROM   REGIONS 
                                               WHERE 
                                 REGION_ABB = "EU")) 
                          GROUP  BY PERSON_REF 
                          HAVING Count(PERSON_REF) >= 3) 

- Gidil

Answer 4

在IT领域中，我们可以使用以下语句来代替这些IN和子查询：连接三个表Images、Persons和Regions：

 SELECT i.Person_Ref 
 FROM Images i
 INNER JOIN Persons p ON i.Person_Ref = p.Person_Ref
 INNER JOIN Regions r ON p.Region_Ref = r.Region_Ref
 WHERE r.Region_Abb = "EU"
   AND i.Effect_Ref = 2 
 GROUP BY i.Person_Ref 
 HAVING COUNT(i.Person_Ref) >= 3

- Mahmoud Gamal

Answer 5

有几种方法可以做到这一点。 您可以像已经回答的那样使用IN示例，但仅在您仅需要一个列的情况下才有效（我知道这是您的情况，但了解您在需要多个列的情况下可以做什么很重要）

最简单的方法是将查询包装到括号中并为其命名别名，以便您可以像使用表格一样使用它：

(SELECT PERSON_REF 
FROM   IMAGES 
WHERE  PERSON_REF IN (SELECT _PERSON_ID 
                      FROM   PERSONS 
                      WHERE  REGION_REF = (SELECT _REGION_ID 
                                           FROM   REGIONS 
                                           WHERE  REGION_ABB = "EU")) 
GROUP  BY PERSON_REF 
HAVING Count(PERSON_REF) >= 3 ) MY_ALIAS

然后你可以将它与其他查询结合起来。但这通常不是一个好的方法。

最好的方法是使用公共表达式（CTEs），你可以这样做：

with MY_FIRST_QUERY as(
SELECT PERSON_REF 
    FROM   IMAGES 
    WHERE  PERSON_REF IN (SELECT _PERSON_ID 
                          FROM   PERSONS 
                          WHERE  REGION_REF = (SELECT _REGION_ID 
                                               FROM   REGIONS 
                                               WHERE  REGION_ABB = "EU")) 
    GROUP  BY PERSON_REF 
    HAVING Count(PERSON_REF) >= 3 
)
select MY_FIRST_QUERY.PERSON_REF  

然后你将它与任何你需要的东西连接起来

- Diego

Answer 6

只需在第一个谓词中添加AND并指定另一个谓词。

SELECT PERSON_REF FROM IMAGES WHERE PERSON_REF IN (SELECT _PERSON_ID FROM PERSONS WHERE REGION_REF = (SELECT _REGION_ID FROM REGIONS WHERE REGION_ABB = "EU")) AND EFFECT_REF=2 GROUP BY PERSON_REF HAVING Count(PERSON_REF) >= 3

- Farfarak