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

I don't now much about SQL queries, I have a problem

我现在不太关注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 

this query gives my result like

这个查询给出了类似的结果

2  
4  
5  
6  

Now I want to use that result in an other query, how to do that, Do I need to create loop or is there any other way

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

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

6 个解决方案

#1

try this:

试试这个:

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

#2

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

#3

Try this:

试试这个:

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) 

#4

JOIN the three tables Images, Persons and Regions instead of these INs and subqueries:

加入三个表格图像、人员和区域，而不是这些INs和子查询:

 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

#5

there are several ways you can do that. You can use the IN example as was already ansered but it will work only if you need only one column (which is your case, I know, but it is good to know what you can do if you face a situation where you need more that oncolumn)

有几种方法可以做到这一点。您可以使用已经分析过的IN示例，但是只有当您只需要一列时才会使用它(我知道，这是您的情况，但是如果您需要更多的oncolumn，那么最好知道您可以做什么)

The simples would be wrap your query into parentesys and give it an alias so you can use it like if it were a table:

simples会将您的查询封装到parentesys中，并给它一个别名，以便您可以像使用表格一样使用它:

(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

then yo ucan join with your other query. This is usually not a good approach though.

然后yo ucan与您的另一个查询进行连接。但这通常不是一个好的方法。

The best apporach, its to use Common Table Expressions (CTEs) so you would do something like this:

最好的apporach是使用公共表表达式(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  

and the you join it wth whatever you need

然后你加入它，你需要什么。

#6

Just add AND to the first predicate and specify another one.

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

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

在PERSON_REF的图像中选择PERSON_REF(从REGION_REF =(从REGION_ABB = "EU"的区域中选择_REGION_ID)和EFFECT_REF=2组中选择_person_ref =2，通过PERSON_REF具有Count(PERSON_REF) >= 3