PostgreSQL - 相关子查询失败？

我有这样的查询:

SELECT t1.id,
    (SELECT COUNT(t2.id)
     FROM t2
     WHERE t2.id = t1.id
          ) as num_things
FROM t1
WHERE num_things = 5;

目标是获取在另一个表中出现5次的所有元素的id.但是,我收到此错误:

ERROR: column "num_things" does not exist
SQL state: 42703

我可能在这里做些傻事,因为我对数据库有些新意.有没有办法修复此查询,以便我可以访问num_things？或者,如果没有,是否还有其他方法可以实现这一结果？

关于使用SQL的一些要点:

您不能在WHERE子句中使用列别名,但可以在HAVING子句中使用.这就是你得到错误的原因.

您可以使用JOIN和GROUP BY比使用相关子查询更好地计算.它会快得多.

使用HAVING子句筛选组.

这是我写这个查询的方式:

SELECT t1.id, COUNT(t2.id) AS num_things
FROM t1 JOIN t2 USING (id)
GROUP BY t1.id
HAVING num_things = 5;

我意识到这个查询可以跳过JOINt1,就像Charles Bretana的解决方案一样.但我假设您可能希望查询包含来自t1的其他一些列.

回复:评论中的问题:

区别在于WHERE,在GROUP BY将组减少到每个组的单个行之前,对行进行评估.HAVING在组成立后对该条款进行评估.所以你不能,例如,COUNT()通过使用改变组HAVING; 您只能排除该组本身.

SELECT t1.id, COUNT(t2.id) as num
FROM t1 JOIN t2 USING (id)
WHERE t2.attribute = 
GROUP BY t1.id
HAVING num > 5;

在上面的查询中,WHERE过滤了与条件匹配的行,并HAVING过滤了至少有五个计数的组.

导致大多数人困惑的一点是,当他们没有一个GROUP BY条款,所以它看起来像HAVING和WHERE可以互换.

WHERE在select-list中的表达式之前计算.这可能并不明显,因为SQL语法首先放置select-list.因此,您可以通过使用WHERE限制行来节省大量昂贵的计算.

SELECT 
FROM t1
HAVING primaryKey = 1234;

如果使用如上所述的查询,则会为每一行计算select-list中的表达式,仅因为HAVING条件而丢弃大部分结果.但是,下面的查询仅计算与条件匹配的单个行的表达式WHERE.

SELECT 
FROM t1
WHERE primaryKey = 1234;

因此,回顾一下,数据库引擎根据一系列步骤运行查询:

从表生成一组行,包括生成的任何行JOIN.

WHERE根据行集评估条件,过滤掉不匹配的行.

在行集中为每个行计算select-list中的表达式.

应用列别名(请注意,这是一个单独的步骤,这意味着您不能在选择列表中的表达式中使用别名).

根据GROUP BY条款,将每个组的压缩组分成一行.

HAVING针对组评估条件,过滤掉不匹配的组.

根据ORDER BY条款对结果进行排序.