Sql连接多个表，获取某些行的计数，并检查一些符合条件的行。

Question

我有动物园，每个动物园有很多笼子，每个笼子有很多动物。

动物园：

+----+
| Id |
+----+
|  1 |
|  2 |
+----+

笼子：

+----+-------+
| Id | ZooId |
+----+-------+
|  1 |     1 |
|  2 |     1 |
|  3 |     2 |
|  4 |     2 |
|  5 |     2 |
+----+-------+

动物：

+----+--------+----------+
| Id | CageId | IsHungry |
+----+--------+----------+
|  1 |      1 |        0 |
|  2 |      1 |        0 |
|  3 |      1 |        0 |
|  4 |      2 |        1 |
|  5 |      3 |        0 |
|  6 |      4 |        0 |
|  7 |      5 |        0 |
+----+--------+----------+

我试图设计一个查询来显示每个动物园，动物园里笼子的数量，以及动物园里是否有饥饿的动物。

以下是我所期望的结果：

+-------+-----------+--------------+
| ZooID | CageCount | AnyoneHungry |
+-------+-----------+--------------+
|     1 |         2 |            1 |
|     2 |         3 |            0 |
+-------+-----------+--------------+

我可以知道动物园里笼子的数量：

SELECT 
    [c].[ZooId],
    COUNT(*) AS [NumCages]
FROM [Cage] [c]
GROUP BY [c].[ZooId]
ORDER BY [NumCages] DESC

我可以确定笼子里是否有饥饿的动物：

SELECT CASE WHEN EXISTS (
    SELECT NULL
    FROM [Animal] [a]
    WHERE [a].[CageId] = @CageId AND [a].[IsHungry] = 1
) THEN 1 ELSE 0 END

但我很难将这两个查询合并成一个高效运行的查询(在这个宇宙中，动物园非常受欢迎，有数百万只笼子和动物)。

SELECT 
    [c].[ZooId], 
    COUNT(*) AS [CageCount], 
    MAX(CONVERT(INT, [x].[AnyoneHungry])) AS [AnyoneHungry]
FROM [Cage] [c]
INNER JOIN  (
    SELECT [a].[CageId], MAX(CONVERT(INT, [a].[IsHungry])) AS [AnyoneHungry]
    FROM [Animal] [a]
    GROUP BY [a].[CageId]
) [x] on [x].[CageId] = [c].[Id]
GROUP BY [c].[ZooId]

我觉得我遗漏了一些东西，应该可以使用更简单的语句来运行这个查询。

Answer 1

如果你只需要动物园的身份证和饥饿的动物：

SELECT c.zooid,
       COUNT(DISTINCT C.Id) as CageCount,
       COALESCE(MAX(CONVERT(int, a.IsHungry)), 0) AS AnyHungry
FROM Cage C LEFT JOIN
     Animal A
     ON c.Id = a.CageId AND a.IsHungry = 1
GROUP BY c.zooid;

Answer 2

这应该可以

SELECT
    Z.Id,
    COUNT(DISTINCT C.Id) AS CageCount,
    COALESCE(MAX(CAST(A.IsHungry AS INT)), 0) AS AnyHungry /*The cast is only required if A.IsHungry is BIT and not INT*/
FROM Zoo Z
    LEFT JOIN Cage C ON Z.Id = C.ZooId
    LEFT JOIN Animal A ON C.Id = A.CageId
GROUP BY Z.Id