MYSQL Left从多个表中加入COUNTS个 [英] MYSQL Left Join COUNTS from multiple tables
问题描述
我想添加表示其他表计数的列。
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
< h2> 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
这篇关于MYSQL Left从多个表中加入COUNTS个的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!