获取两个联接表的唯一记录计数

topics , sentences ,和 vocabulary . 句子和词汇都有各自的特点 belongsTo topic_id 但并不是所有的话题都有词汇和句子。我想统计一下所有既有句子又有词汇的话题。

select
    *
from (
    select 
        t.id as topic_id,
        count(v.id) total_vocabulary
    from topics t
    left join vocabulary v on (v.topic_id = t.id)
    where v.locale_id = 1
    group by t.id
    order by t.id
) as topics_with_vocabulary
where total_vocabulary > 0

输出准确:

但我想在句子和词汇上都表现出来。

如果我用下面的方法来计算的话,它会同时计算句子和词汇的#词汇量(这是有意义的,因为它计算的是总行数),但不会分别计算句子和词汇的#总数。

select
    *
from (
    select 
        t.id as topic_id,
        count(s.id) as total_sentences,
        count(v.id) as total_vocabulary
    from topics t
    left join sentences s on (s.topic_id = t.id)
    left join vocabulary v on (v.topic_id = t.id)
    where s.locale_id = 1
    and v.locale_id = 1
    group by t.id
    order by t.id
) as topics_with_sentences
where total_sentences > 0
or total_vocabulary > 0