sql - 合并两个Postgresql表中的选择

我有两个表users和followers，因此我需要获取以下用户的数量和followers的数量：
用户：

 id | name
----+------
  1 | a
  7 | b
  2 | c
  3 | d
  4 | e
  5 | f
  6 | g

追随者：

 fid | uid
-----+-----
   1 |   7
   1 |   2
   1 |   6
   1 |   3
   7 |   1

我想做的是：

SELECT id, name, count(fid) as following, count(uid) as followers
FROM users
INNER JOIN followers on users.id=followers.fid
group by id;

得到错误的结果：

 id | name | following | followers
----+------+-----------+-----------
  1 |   a  |         4 |         4
  7 |   b  |         1 |         1

注意，我是一个新来的干净的sql语法，请修复我的查询。

最佳答案

如果我正确理解，你需要这样的东西：

select users.*, t1.following, t2.followers from users
left join (select fid, count(*) as following from followers group by fid) t1
on users.id = t1.fid
left join (select uid, count(*) as followers from followers group by uid) t2
on users.id = t2.uid
-- where following is not null or  followers is not null

如果您需要排除没有跟随者且没有跟随者的用户，则取消注释最后一行WHERE

关于sql - 合并两个Postgresql表中的选择，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/44269519/