来自另一个表的 SQL 计数列

Question

我在一个数据库中有两个表

第一个是 people，它有列

id: int,
name: varchar(10)

还有一个是relationships，代表一种单向跟随

me: int
following: int

me 和 following 是外键，与 people 表中的人的 id 主键相匹配.

我想运行一个查询，给定一个人的 id 返回他们的名字和他们关注的人数以及关注他们的人数。

我目前的尝试是

SELECT *, COUNT(following.me), COUNT(following.following) FROM people
WHERE id = 3
JOIN following f1 on f1.me = id
JOIN following f2 on f2.following = id;

但是它会抛出有关 where 语法的错误。我想我需要在某处使用 group by 但我正在努力解决它如何在多个表上工作。

假设给定 id=2 它将返回 [{name: "sam", followers: 4, following: 3}]

Answer 1

这可以简单地通过内联相关子查询来解决，例如:

select 
    p.name,
    (select count(*) from relationships r where r.following = p.id) followers,
    (select count(*) from relationships r where r.me = p.id) following
from people p
where p.id = 3

这应该是一个非常有效的选择。

否则，从您现有的查询开始，您还可以left join 和聚合:

select 
    p.name,
    count(distinct r.following) followers,
    count(distinct r.me) following
from people p
left join relationships r on p.id in (r.followers, r.me)
where p.id = 2
group by p.id, p.name