sql - 使用先前查询的数据

Question

我想使用这样的查询

SELECT personId
FROM Person p 
Inner Join address a on p.personId=a.personId
WHERE(a.created > GETDATE() -10)

然后我想使用这些数据来过滤这个查询

SELECT * 
FROM accounts a
WHERE a.person /*is in the results of the previous query */

我该怎么做？

Answer 1

这应该可以解决问题(假设您的帐户表中有列 personId)。

SELECT * 
FROM accounts a
WHERE a.personId in (
                SELECT DISTINCT personId
                FROM Person p 
                Inner Join address a on p.personId=a.personId
                WHERE(a.created > DATEADD(d,-10,GETDATE())))

或者，您可以直接连接 3 个表而不是使用子查询:

SELECT
    C.*
FROM
    accounts C
INNER JOIN
    Person P ON P.personId = C.personId
INNER JOIN
    [Address] A ON A.personId = P.PersonId
WHERE
    A.created > DATEADD(d,-10,GETDATE())