MYSQL从多个表中加入COUNTS

时间:2022-07-24 11:11:01

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

Sql Fiddle