在外部查询的where子句中使用select column子查询的结果？

问题描述：

我有一个表User和Company。用户记录是公司记录的孩子，即用户表具有指示用户属于哪个公司的列parent_company_id。在外部查询的where子句中使用select column子查询的结果？

我要选择公司的记录，对如何关联的用户记录的企业多有詹姆斯的名字进行的计数一起，只有在那计数> 0

这是我的第一个念头：

SELECT 
    c.name, 
    (SELECT 
    COUNT(*) 
    FROM [user] u 
    WHERE first_name = 'James' 
    AND u.parent_company_id = company_id) 
    AS james_count 
FROM company c 
WHERE james_count > 0;

这不会编译，因为外部查询不知道james_count列别名。为什么不？

这有效，但不会因为它会运行两个子查询而变慢？

SELECT 
    c.name, 
    (SELECT 
    COUNT(*) 
    FROM [user] u 
    WHERE first_name = 'James' 
    AND u.parent_company_id = company_id) 
    AS james_count 
FROM company c 
WHERE (SELECT 
    COUNT(*) 
FROM [user] u 
WHERE first_name = 'James' 
AND u.parent_company_id = company_id) 
> 0;

'选择（查询）一个地方james_count> 0' –

答

有很多方法可以做到这一点。但是，一种是使用cross apply：

select c.name, j.james_count 
from company c cross apply 
    (select count(*) as james_count 
     from [user] u 
     where first_name = 'James' and u.parent_company_id = company_id 
    ) j 
where james_count > 0;

的更自然的方式就是使用一个join和group by：

select c.name, count(*) as james_count 
from company c join 
    [user] u 
    on u.parent_company_id = c.company_id and u.first_name = 'James' 
group by c.name;

首先查询将有重复'c.name'和第二查询将不会有重复或思念的东西 –

@prdp *。。。如果'company'中存在重复值，第一个查询*可能会*重复'c.name'。这与OP原始查询的行为相同。第二个会按'c.name'进行聚合。 –

是的..另一种方式是'select * from（你的查询）a where james_count> 0' –

答

可以使用CTE这一点。首先选择公司ID与詹姆斯的名字计数。然后将该表与公司表一起加入。

WITH James (parent_company_id, james_count) 
AS  (SELECT parent_company_id, 
       COUNT(*) 
     FROM  [User] 
     WHERE first_name = 'James' 
     GROUP BY parent_company_id) 
SELECT c.*, 
     j.james_count AS no_of_companies_with_user_james 
FROM [Company] AS c 
     INNER JOIN 
     [James] AS j 
     ON c.company_id = j.parent_company_id;

在外部查询的where子句中使用select column子查询的结果？

相关推荐