另一个表中的SQL计数列

Question

我在数据库中有两个表

第一个是具有列的people

id: int,
name: varchar(10)

另一个是代表单向跟随的CCD_ 2

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

Answer 2

您的尝试将不起作用(即使使用正确的语法(，因为COUNT()返回了非NULL值的数量。在JOIN之后，任何没有NULL值的列都将返回相同的结果。

尽管可以用COUNT(DISTINCT)修复它，但我建议使用相关的子查询：

select p.*,
(select count(*)
from relationships r
where r.me = p.id
) as num_followed,
(select count(*)
from relationships r
where r.following = p.id
) as num_following
from people p;

您也可以通过预聚合来做到这一点：

select p.*, fe.num_followed, fi.num_following
from people p left join
(select r.me, count(*) as num_followed
from relationships r
group by r.me
) fe
on fe.me = p.id left join
(select r.following, count(*) as num_followed
from relationships r
group by r.follwoing
) fi
on fi.following = p.id ;

Answer 3

Group by id, name表people到表relationships的双联接，并计算不同的me和relationships0:

SELECT p.id, p.name,
COUNT(DISTINCT f1.following) counterfollowers, 
COUNT(DISTINCT f2.me) counterfollowing
FROM people p
LEFT JOIN relationships f1 on f1.me = p.id
LEFT JOIN relationships f2 on f2.following = p.id
WHERE p.id = 3
GROUP BY p.id, p.name

请参阅演示
结果：

> id | name | counterfollowers | counterfollowing
> -: | :--- | ---------------: | ---------------:
>  3 | c    |                2 |                1

Answer 4

我们可以尝试使用两个单独的子查询来查找以下两个计数：

SELECT
p.name,
COALESCE(r1.me_cnt, 0) AS following,
COALESCE(r2.following_cnt, 0) AS followers
FROM people p
LEFT JOIN
(
SELECT me, COUNT(*) AS me_cnt
FROM relationships
GROUP BY me
) r1
ON r1.me = p.id
LEFT JOIN
(
SELECT following, COUNT(*) AS following_cnt
FROM relationships
GROUP BY following
) r2
ON r2.following = p.id;