为了这个问题,让我们假设这个表结构:
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