PostgreSQL - 相关子查询失败? [英] PostgreSQL - Correlated Sub-Query Fail?

问题描述

我有一个这样的查询:

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.但是，我收到此错误:

The goal is to get the id of all the elements that appear 5 times in the other table. However, I get this error:

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

我可能在这里做一些愚蠢的事情，因为我对数据库有些陌生.有没有办法修复这个查询，以便我可以访问 num_things?或者，如果没有，有没有其他方法可以达到这个结果?

I'm probably doing something silly here, as I'm somewhat new to databases. Is there a way to fix this query so I can access num_things? Or, if not, is there any other way of achieving this result?

推荐答案

我认为你可以像这样重写你的查询:

I think you could just rewrite your query like so:

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

这篇关于PostgreSQL - 相关子查询失败?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！