来自多个表的MYSQL左联接COUNTS

来自多个表的MYSQL左联接COUNTS

select  t.Topic,  t.Title,  count(distinct s.starID) as StarCount,  count(distinct m.User) as UserCount,  count(distinct m.messageID) as MessageCountfrom  Topics t  left join Messages m ON m.Topic = t.Topic  left join Stars_Given s ON s.Topic = t.Topicgroup by  t.Topic,  t.Title

SQL小提琴

或者，您可以在子查询中执行聚合，如果表中有大量数据，这可能会更有效：

select  t.Topic,  t.Title,  s.StarCount,  m.UserCount,  m.MessageCountfrom  Topics t  left join (    select       Topic,       count(distinct User) as UserCount,      count(*) as MessageCount    from Messages    group by Topic  ) m ON m.Topic = t.Topic  left join (    select      Topic,       count(*) as StarCount    from Stars_Given     group by Topic  ) s ON s.Topic = t.Topic

SQL小提琴