sql - 如何计算SQL中不同项目的数量

Question

数据库结构:

Clubs: ID, ClubName
Teams: ID, TeamName, ClubID
Players: ID, Name
Registrations: PlayerID, TeamID, Start_date, End_date, SeasonID

俱乐部拥有几支球队。球员可能会在一年内注册到多支球队(在同一俱乐部内或不同俱乐部内)。我必须生成一个查询以列出在一个赛季中已注册到不同俱乐部的所有球员。因此，如果球员交换了属于同一俱乐部的球队，则不算在内。

到目前为止我的尝试:

SELECT
c.short_name,
p.surname,
r.start_date,
r.end_date,
(select count(r2.id) from ejl_registration as r2
    where r2.player_id=r.player_id and r2.season=r.season) as counter
FROM
ejl_registration AS r
left Join ejl_players AS p ON p.id = r.player_id
left Join ejl_teams AS t ON r.team_id = t.id
left Join ejl_clubs AS c ON t.club_id = c.id
WHERE
r.season =  '2008'
having counter >1

我无法弄清楚如何计算并仅显示不同的俱乐部......(现在已经太晚了，无法清晰地思考)。我使用 MySQL。

报告应该是这样的:球员姓名，俱乐部名称，Start_date，End_date

Answer 1

这是对该答案的第二次尝试，将其简化为仅计算不同的俱乐部，而不是报告俱乐部名称列表。

SELECT p.surname, r.start_date, r.end_date, COUNT(DISTINCT c.id) AS counter
FROM ejl_players p
 JOIN ejl_registration r ON (r.player_id = p.id)
 JOIN ejl_teams t ON (r.team_id = t.id)
 JOIN ejl_clubs c ON (t.club_id = c.id)
WHERE r.season = '2008'
GROUP BY p.id
HAVING counter > 1;

请注意，由于您使用的是 MySQL，因此对于选择列表中的列与 GROUP BY 子句中的列不匹配，您可以非常灵活。其他品牌的 RDBMS 对单值规则更为严格。

没有理由像您的示例那样使用 LEFT JOIN。

---

好的，这是查询的第一个版本:

您有如下关系链:

club1 <-- team1 <-- reg1 --> player <-- reg2 --> team2 --> club2

例如 club1 不能与 club2 相同。

SELECT p.surname,
  CONCAT_WS(',', GROUP_CONCAT(DISTINCT t1.team_name), 
    GROUP_CONCAT(DISTINCT t2.team_name)) AS teams,
  CONCAT_WS(',', GROUP_CONCAT(DISTINCT c1.short_name), 
    GROUP_CONCAT(DISTINCT c2.short_name)) AS clubs
FROM ejl_players p
 -- Find a club where this player is registered
 JOIN ejl_registration r1 ON (r1.player_id = p.id)
 JOIN ejl_teams t1 ON (r1.team_id = t1.id)
 JOIN ejl_clubs c1 ON (t1.club_id = c1.id)
 -- Now find another club where this player is registered in the same season
 JOIN ejl_registration r2 ON (r2.player_id = p.id AND r1.season = r2.season)
 JOIN ejl_teams t2 ON (r2.team_id = t2.id)
 JOIN ejl_clubs c2 ON (t2.club_id = c2.id)
-- But the two clubs must not be the same (use < to prevent duplicates)
WHERE c1.id < c2.id
GROUP BY p.id;