sql - 使用计数加入/合并 3 个表

Question

我有 3 张 table :

人们:

pid    name
1      Cal
2      Example
3      Another
4      Person

谈话要点:

tid    pid    talkingPoint
1      1      "..."
2      1      "..."
3      2      "..."

事实:

fid    pid    fact
1      3      "..."
2      2      "..."

我正在尝试将talkPoints 和事实的计数与“人”结合起来，例如:

pid    name     talkingPoints  facts
1      Cal      2              null
2      Example  1              1
3      Another  null           1
4      Person   null           null

(按talkPoints desc 排序，然后按字母顺序排列，包括没有任何计数值的“人”行)

我设法将“人”与另一张 table 结合起来:

SELECT a.pid,a.name,
count(b.tid)  
FROM people a, talkingPoints b  
WHERE a.pid=b.pid  
GROUP BY b.pid;

但该查询会忽略计数为零的行(例如“人”行)

我破解了这个适用于“talkingPoints”的查询，但我无法调整它来组合“事实”，就像我上面的示例表一样。

select people.pid, people.name, x.talkingPoints from people left join 
(select pid, name, count(name) talkingPoints from 
(select people.pid, people.name from people
join talkingPoints on talkingPoints.pid = people.pid)
 as talkingPoints group by talkingPoints.pid)
 as x on x.pid = people.pid order by talkingPoints desc, people.name asc;

(可能是一种可怕的方式，但同时它也奏效了)

我怎样才能调整我的查询，以便他们输出一个像我的例子一样的表格？

Answer 1

SELECT  a.pid,
        a.name,
        COUNT(DISTINCT b.tid) talkingpoints,
        COUNT(DISTINCT c.fid) facts
FROM    people a
        LEFT JOIN talkingPoints b
            ON a.pid = b.pid
        LEFT JOIN facts c
            ON a.pid = c.pid
GROUP   BY a.pid, a.name
ORDER   BY a.pid

SQLFiddle Demo