如何查询架构中没有成员的团队

Question

在我的数据库模式中，我有俱乐部、球队和球员，我想创建一个报告，统计俱乐部中的球队总数，以及没有球员的球队数量。输出如下：

CLUB      #TEAMS  #EMPTYTEAMS
Cobras    5       0
Eagles    7       2

我的架构在以下伪代码中进行了描述：

TABLE CLUB
  ID int
  NAME string

TABLE TEAM
  ID int
  NAME string

TABLE PLAYER
  ID int
  NAME string

TABLE CLUB_TEAM
  ID int
  CLUB_ID int   -- foreign key into CLUB
  TEAM_ID int   -- foreign key into TEAM

TABLE MEMBERSHIP
  ID int
  PLAYER_ID int -- foreign key into PLAYER
  TEAM_ID int   -- foreign key into TEAM

一些示例数据：

CLUB
0  Eagles
1  Cobras

TEAM
0  Eagles U10
1  Eagles U11
2  Eagles U12
...
7  Cobras U11
8  Cobras U12
9  Cobras U13
...

PLAYER
0 John Doe
1 Jane Doe
...

CLUB_TEAM
0 0 0  - The Eagles Club has an U10 team
1 0 1
2 0 2
...
7 1 7
8 1 8
9 1 9

MEMBERSHIP
0 0 7  - John plays for Cobras U11
1 1 9  - His older sister, Jane, plays for Cobras U13

我已经成功开发了一个查询，它将给出每个俱乐部的球队数量:

  SELECT CLUB.ID, CLUB.NAME, COUNT(CLUB_TEAM.ID) AS "#TEAMS"
  FROM CLUB
  INNER JOIN CLUB_TEAM
    ON CLUB_TEAM.CLUB_ID = CLUB.ID
  INNER JOIN TEAM
    ON CLUB_TEAM.TEAM_ID = TEAM.ID
  GROUP BY CLUB.ID

然而，我还不能将这个查询扩展到包括球队成员数据和没有球员的球队。有什么建议吗？

Answer 1

也许这会有所帮助。由于没有示例数据，我无法对其进行测试，因此很可能存在语法错误（或两个！），但希望您能理解这个想法。基本上，您将成员资格表汇总为有一行给出每个团队的成员计数，然后将其留给原始查询。当团队没有成员时，COALESCE 处理这种情况，因此在左连接上返回一个 NULL 值，该值被转换为零。我相信PostgreSQL支持常见的表表达式（即我在这里使用的WITH子句），这在当今的SQL数据库中是非常标准的。

WITH team_membership AS (
     SELECT team_id, COUNT(*) AS membercount 
       FROM membership
)
SELECT club.id,
       club.name,
       COUNT(club_team.id) AS "#TEAMS",
       SUM(CASE WHEN COALESCE(membercount, 0) = 0 THEN 1 ELSE 0 END) AS "#EMPTYTEAMS"   
FROM   club
       INNER JOIN club_team
               ON club_team.club_id = club.id
       INNER JOIN team
               ON club_team.team_id = team.id
       LEFT JOIN team_membership
              ON club_team.team_id = team_membership.team_id
GROUP  BY club.id,
          club.name 

Answer 2

我设法找到了如何追踪空团队，我的解决方案类似于@user9601310的第二部分。

SELECT CLUB.ID, CLUB.NAME, COUNT(MEMBERSHIP.TEAM_ID) AS "#MEMBERS"
FROM CLUB
INNER JOIN CLUB_TEAM
  ON CLUB_TEAM.CLUB_ID = CLUB.ID
INNER JOIN TEAM
  ON CLUB_TEAM.TEAM_ID = TEAM.ID
LEFT JOIN MEMBERSHIP -- LEFT JOIN to capture teams with no members
  ON MEMBERSHIP.TEAM_ID = TEAM.ID
GROUP BY CLUB.ID, TEAM.ID
HAVING COUNT(MEMBERSHIP.TEAM_ID) = 0

从那里，我用以下方式包装了这个查询（…）：

SELECT CLUB.ID, COUNT(#MEMBERS) AS "#EMPTYTEAMS"
FROM ( ... ) AS "QUERY"
GROUP BY CLUB.ID

这给了我每个俱乐部的空队数量。

然后，基于这个问题中的想法，我以如下方式组合了我的两个查询——上面的一个和问题中的一个:

SELECT SET1.ID, CLUB.NAME, "#TEAMS", "#EMPTYTEAMS"
FROM ( query 1 ) SET1
INNER JOIN ( query 2 ) SET2
ON SET1.ID = SET2.ID

这给了我想要的结果。不太优雅，但是内心的疑问相当直截了当，它们的组合也是如此。