I want to add columns that represent counts from other tables.
我想添加表示来自其他表的计数的列。
I have 3 tables.
我有3张桌子。
Messages
消息
MessageID User Message Topic
1 Tom Hi ball
2 John Hey book
3 Mike Sup book
4 Mike Ok book
Topics
主题
Topic Title Category1 Category2
ball Sports Action Hot
book School Study Hot
Stars_Given
Stars_Given
starID Topic
1 ball
2 book
3 book
4 book
I want to end up with:
我想最终得到:
Topic_Review
Topic_Review
Topic Title StarCount UserCount MessageCount
ball Sports 1 1 1
book school 3 2 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).
因此,基本上我想附加3列,其中包含唯一值的计数(每个主题中给出的星号数,在主题中包含消息的唯一用户,以及每个主题中唯一消息的数量)。
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.
我已经尝试过调整其他人的答案,我无法让它正常工作。
1 个解决方案
#1
69
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
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
Sql Fiddle
#1
69
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
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