MYSQL Left从多个表中加入COUNTS个

本文介绍了MYSQL Left从多个表中加入COUNTS个的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我想添加表示其他表计数的列。

I want to add columns that represent counts from other tables.

我有3张表。

讯息

MessageID User Message Topic 1 Tom Hi ball 2 John Hey book 3 Mike Sup book 4 Mike Ok book

主题

Topic Title Category1 Category2 ball Sports Action Hot book School Study Hot

Stars_Given

starID Topic 1 ball 2 book 3 book 4 book

我想结束：

Topic_Review

Topic Title StarCount UserCount MessageCount ball Sports 1 1 1 book school 3 2 3

所以基本上我想附加3列数据的唯一值在每个主题内给出的星数，在主题内具有消息的唯一用户，以及每个主题中的唯一消息的数量）。

So basically I want to attach 3 columns with counts of unique values (number of stars given within each topic, unique users who have messages within topic, and the number of unique messages in each topic).

我最终能够过滤类别（同时查看两个列）。

I want to eventually be able to filter on the categories (look in both columns) as well.

此外，我想最终按我加入的数量排序。例如，我将按照升序排列按星号按钮，或按降序排序用户数等。

Also, I want to eventually sort by the counts that I join. Example, I'm going to have a button that sorts by "number of stars" by ascending order, or sort by "number of users" by descending order, etc.

我试过适应其他人的答案，我不能让它正常工作。

I've tried adapting other people's answers and I can't get it to work properly.

谢谢！

推荐答案

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

Sql Fiddle

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

Sql Fiddle

Or, you can perform the aggregation in sub-queries, which will likely be more efficient if you have a substantial amount of data in the tables:

select t.Topic, t.Title, s.StarCount, m.UserCount, m.MessageCount from 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