sql - 如何同时进行两个计数查询？

Question

表结构：

CREATE TABLE followers (
  user_id int not null REFERENCES users(id) on DELETE CASCADE,
  following int not null REFERENCES users(id) on DELETE CASCADE,
  PRIMARY key (user_id, following)
);

从这里我想获取关注者和关注者的数量。目前我正在这样做：

-- user 1 following
SELECT count(*) from followers f
WHERE f.user_id = 1;

-- user 1 followers
SELECT count(*) from followers f
WHERE f.following = 1;

有什么方法可以将其作为单个查询来执行吗？

期望的输出：

 ~~~~~~~~~~~~~~~~~~~~~~~
| followers | following |
 ~~~~~~~~~~~~~~~~~~~~~~~
| 2         | 1         |
 ~~~~~~~~~~~~~~~~~~~~~~~

标签： sqlpostgresql

Answer 1

您可以在 COUNT() 中使用FILTER ：

SELECT  
        COUNT(*)    FILTER(WHERE user_id = 1)   AS followers
    ,   COUNT(*)    FILTER(WHERE following = 1) AS following
FROM    followers
WHERE   user_id = 1
OR      following = 1;