sql - 加入多个表，从不同的表中选择计数并在一个查询中按一列分组

Question

我需要加入多个表，从不同的表中选择计数并在一个查询中按一列分组。这就是我将分别执行此操作的方式:

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

或者干脆

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

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

Answer 1

如果我正确理解了您的问题，那么您正在寻找社区名称以及帖子、博客、事件等计数。

当您的查询单独计数时，请在 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看起来像

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

所以，你可以 GROUP BY命名并总结计数以获得您的结果

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