连接多个表，从不同的表中选择计数，并在一个查询中按一列分组

I need to join multiple tables, select counts from different tables and group by one column in one query. This is how I would do this separately:

我需要连接多个表，从不同的表中选择计数，并在一个查询中按一列分组。我就是这样做的：

select      c.CommunityName, SUM(case when m.ListKey = c.ListKey then 1 else 0 end) as Posts
from        Community c with(NOLOCK)
join        messages_ m with(NOLOCK)
on          c.ListKey = m.ListKey
group by    c.CommunityName

select      c.CommunityName, SUM(case when b.CommunityKey = c.CommunityKey then 1 else 0 end) as Blogs
from        Community c with(NOLOCK)
join        Blog b with(NOLOCK)
on          c.CommunityKey = b.CommunityKey
group by    c.CommunityName

select      c.CommunityName, SUM(case when ce.CommunityKey = c.CommunityKey then 1 else 0 end) as Events
from        Community c with(NOLOCK)
join        CalendarEvent ce with(NOLOCK)
on          c.CommunityKey = ce.CommunityKey
where       ce.StartDateTime >= GETDATE()
group by    c.CommunityName

or simply

或简单地说

select      c.CommunityName, COUNT(*)
from        Community c with(NOLOCK)
join        messages_ m with(NOLOCK)
on          c.ListKey = m.ListKey
group by    c.CommunityName

select      c.CommunityName, COUNT(*)
from        Community c with(NOLOCK)
join        Blog b with(NOLOCK)
on          c.CommunityKey = b.CommunityKey
group by    c.CommunityName

select      c.CommunityName, COUNT(*)
from        Community c with(NOLOCK)
join        CalendarEvent ce with(NOLOCK)
on          c.CommunityKey = ce.CommunityKey
where       ce.StartDateTime >= GETDATE()
group by    c.CommunityName

There are more tables, some that require additional joins... Can someone please help?

有更多的表，有些需要额外的连接......有人可以帮忙吗？

2 个解决方案

#1

If I understand your question correctly, you are looking for community name along with the counts such as posts, blogs, event etc..

如果我正确理解您的问题，您正在寻找社区名称以及帖子，博客，活动等计数。

As your queries count individually, add dummy columns in the SELECT for the other counts and then in the end UNION them and get the SUM.

当您的查询单独计数时，在SELECT中为其他计数添加虚拟列，然后在UNION中添加它们并获取SUM。

SELECT CommunityName , SUM(MessageCount), SUM(BlogCount), SUM(EventCount)
FROM (
    SELECT      c.CommunityName CommunityName , COUNT(*) MessageCount, 0 BlogCount, 0 EventCount
    FROM        Community c with(NOLOCK)
    JOIN        messages_ m with(NOLOCK)
    ON          c.ListKey = m.ListKey
    GROUP BY    c.CommunityName

    UNION

    SELECT      c.CommunityName, 0, COUNT(*), 0
    FROM        Community c with(NOLOCK)
    JOIN        Blog b with(NOLOCK)
    ON          c.CommunityKey = b.CommunityKey
    GROUP BY    c.CommunityName

    UNION

    SELECT      c.CommunityName, 0, 0, COUNT(*)
    FROM        Community c with(NOLOCK)
    JOIN        CalendarEvent ce with(NOLOCK)
    ON          c.CommunityKey = ce.CommunityKey
    WHERE       ce.StartDateTime >= GETDATE()
    GROUP BY    c.CommunityName
  ) CountsTable
GROUP BY CountsTable.CommunityName

CountsTable will look like

CountsTable看起来像

| COMMUNITYNAME | MESSAGECOUNT | BLOGCOUNT | EVENTCOUNT |
|---------------|--------------|-----------|------------|
|          Name |           10 |         0 |          0 |
|          Name |            0 |        20 |          0 |
|          Name |            0 |         0 |         30 |

So, you can GROUP BY name and sum up the counts to get your result

因此，您可以GROUP BY名称并总结计数以获得结果

| COMMUNITYNAME | MESSAGECOUNT | BLOGCOUNT | EVENTCOUNT |
|---------------|--------------|-----------|------------|
|          Name |           10 |        20 |         30 |

#2

Have you thought about using LEFT JOIN to connect your tables? Then you can check for NULLs and sum up the non-NULL values.

您是否考虑过使用LEFT JOIN连接表格？然后，您可以检查NULL并总结非NULL值。

SELECT
    c.CommunityName,
    SUM(case when m.ListKey IS NOT NULL then 1 else 0 end) as Posts,
    SUM(case when b.CommunityKey IS NOT NULL then 1 else 0 end) as Blogs,
    SUM(case when ce.CommunityKey IS NOT NULL then 1 else 0 end) as Events
FROM
    Community c WITH(NOLOCK)
        LEFT JOIN
    messages_ m WITH(NOLOCK)
        ON c.ListKey = m.ListKey
        LEFT JOIN
    Blog b WITH(NOLOCK)
        ON c.CommunityKey = b.CommunityKey
        LEFT JOIN
    CalendarEvent ce WITH(NOLOCK)
        ON c.CommunityKey = ce.CommunityKey
WHERE
    ce.StartDateTime >= GETDATE()
GROUP BY
    c.CommunityName

#1