SQL组通过包含空行 [英] SQL Group By including empty rows

问题描述

为了这个问题，我们假设这个表结构：

For the sake of this question, let's suppose this table structure:

People:
PersonID int PK
Name varchar(50)
Place int NULL FK -> Places.PlaceID
MovedIn datetime

Places:
PlaceID int PK
Name varchar(50)

我想确定每个地方有多少人住：

I want to determine how many people live at each place:

SELECT pla.PlaceID, COUNT(*)
FROM Places AS pla
LEFT JOIN People as peo ON peo.PlaceID = pla.PlaceID
GROUP BY pla.PlaceID

该查询将省略没有人居住的地方。有没有什么办法让它计数0呢？

This query will omit places that have no people living there. Is there any way to make it count 0 instead?

（我的目标是SQL Server 2005，因为它很重要）

(I'm targetting SQL Server 2005, in the off chance that it matters)

编辑：
这是我尝试调整Steve的解决方案后的真实（匿名）查询：

Here's my real (anonymized) query, after trying to adapt Steve's solution:

SELECT
    ft.FooTypeID, COUNT(f.FooID)
FROM FooType as ft
LEFT OUTER JOIN Foo f ON ft.FooTypeID = f.FooTypeID
LEFT JOIN FooConfig fc ON ft.NotificationConfigID = fc.FooConfigID
WHERE
    DateDiff(day, GetDate(), f.Date) > 0 AND
    DateDiff(day, GetDate(), f.Date) < fc.Days
GROUP BY ft.FooTypeID

（我最初的例子和这个是：Foo - > People，FooType - > Places，FooConfig - >第三个表，额外的乐趣）
我可以使这个工作与福斯科的解决方案，但我更喜欢史蒂夫的。

(The translation between my initial example and this is: Foo -> People, FooType -> Places, FooConfig -> A third table, for extra fun) I can make this work with Fosco's solution, but I'd prefer Steve's.

推荐答案

SELECT pla.PlaceID, COUNT(peo.PersonID)
FROM Places AS pla LEFT OUTER JOIN People as peo ON peo.PlaceID = pla.PlaceID
GROUP BY pla.PlaceID

编辑问题：

假设总是有一个 FooConfig ，我们将把 LEFT JOIN 放到该表中（因为它总是在那里）。然后我们可以在 Foo 表中加入额外的条件：

Assuming there is always a FooConfig entry, we'll drop the LEFT JOIN to that table (as it'll always be there). We can then include the extra criteria in the join to the Foo table:

SELECT
    ft.FooTypeID, COUNT(f.FooID)
FROM FooType as ft
  JOIN FooConfig fc ON ft.NotificationConfigID = fc.FooConfigID
  LEFT OUTER JOIN Foo f ON ft.FooTypeID = f.FooTypeID AND
    DateDiff(day, GetDate(), f.Date) > 0 AND
    DateDiff(day, GetDate(), f.Date) < fc.Days
GROUP BY ft.FooTypeID

如果 FooConfig 表是可选的，那么额外的日期标准就不能使用（因为它们总是会计算为false） - 所以我们必须做一些事情：

If the FooConfig table is optional, then the extra date criteria can't be used (as they would always evaluate to false) - so we'd have to do something like:

SELECT
    ft.FooTypeID, COUNT(f.FooID)
FROM FooType as ft
  LEFT OUTER JOIN FooConfig fc ON ft.NotificationConfigID = fc.FooConfigID
  LEFT OUTER JOIN Foo f ON ft.FooTypeID = f.FooTypeID AND
    (
      (DateDiff(day, GetDate(), f.Date) > 0 AND
       DateDiff(day, GetDate(), f.Date) < fc.Days) 
      OR
      (fc.Days IS NULL)
    )
GROUP BY ft.FooTypeID

这篇关于SQL组通过包含空行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！