为了这个问题,让我们假设这个表结构:

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

Places:
PlaceID int PK
Name varchar(50)


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

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


此查询将忽略没有人居住的地方。有什么办法可以使它计数为0吗?

(我的目标是SQL Server 2005,这很可能很重要)

编辑:
在尝试改编史蒂夫的解决方案后,这是我的真实(匿名)查询:

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->第三张桌子,以获取更多乐趣)
我可以使用Fosco的解决方案来完成这项工作,但我更喜欢Steve的解决方案。

最佳答案

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表的过程中包括额外的条件:

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)-因此我们必须执行以下操作:

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

07-26 03:29