SQL:一组结果中有两个查询？

Question

我正在使用 Postgres 9.6。我有三个表，如下所示:

Table public.user
id      integer
name    character varying
email   character varying

Table public.project
id      integer
user_id integer 

Table public.sale
id      integer
user_id integer 

user_id 是 project 和 sale 表中的外键。

有没有一种方法可以将所有用户 ID 及其附加的项目数量和销售数量作为单个查询返回？

所以我想要这样的最终数据:

user_id,num_projects,num_stories
121,28,1
122,43,6
123,67,2

我只知道如何做项目的数量:

SELECT "user".id, COUNT(*) AS num_visualisations 
JOIN project ON project.user_id="user".id
GROUP BY "user".id
ORDER BY "user".id DESC

但我也不知道如何在单个查询中也获得销售数量。

Answer 1

使用子查询进行聚合和左连接:

select u.*, p.num_projects, s.num_sales
from user u left join
     (select p.user_id, count(*) as num_projects
      from projects p
      group by p.user_id
     ) p
     on p.user_id = u.id left join
     (select s.user_id, count(*) as num_sales
      from sales s
      group by s.user_id
     ) s
     on s.user_id = u.id;