sql - 连接问题——有人可以向我解释我做错了什么吗？

Question

我有两个表member 和team，其结构如下所示。我正在努力实现这两个结果:

1. 每支球队的球员人数加上每支球队收取的费用总额(即 PlayerFee 乘以球员人数)。

架构:

CREATE TABLE member 
(
     MemberID int NOT NULL,
     LastName varchar(20) NOT NULL,
     FirstName varchar(20) NOT NULL,
     MemberType varchar(10) NOT NULL DEFAULT 'Social',
     Phone varchar(20) DEFAULT NULL,
     Handicap int DEFAULT NULL,
     JoinDate date NOT NULL,
     MemberTeam varchar(10) DEFAULT NULL,
     Gender char(1) NOT NULL,
     LifeMember bit(1) NOT NULL DEFAULT b'0',
     PRIMARY KEY (MemberID)
);

INSERT INTO member 
VALUES (118, 'James', 'Melissa', 'Junior', '027-075-0712', 30, '2004-05-08', 'TeamB', 'F', '0');

CREATE TABLE team 
(
     TeamName varchar(10) NOT NULL,
     PracticeNight varchar(10) NOT NULL,
     PlayerFee decimal(10,2) NOT NULL,
     PRIMARY KEY (TeamName)
);

INSERT INTO team 
VALUES ('TeamA', 'Monday', 25.75),
       ('TeamB', 'Tuesday', 16.75); 

我已经针对问题 1 尝试了以下代码，但我不确定我得到的结果是否正确。

SELECT
    MemberTeam, Handicap, PracticeNight 
FROM
    member 
RIGHT JOIN 
    team ON member.MemberTeam = team.TeamName 
ORDER BY
    PracticeNight;

问题 1 已解决，但不确定如何使用 JOIN 来使用 COUNT 函数。任何帮助将不胜感激。

Answer 1

我喜欢使用 TDQD(测试驱动查询设计)来解决复杂的 SQL 查询。这些并不是特别困难的查询，但这个想法仍然可以让您获得正确的结果。

查询 1 — 每周参加练习的成员数量

查询必须确定每个球员在哪一晚参加训练，然后计算在给定晚上参加训练的球员人数。

第 1 步 - 成员在哪一天晚上参加练习？

SELECT m.Member, t.TeamName, t.PracticeNight
  FROM member AS m
  JOIN team AS t ON m.MemberTeam = t.TeamName

第 2 步 - 每周有多少球员参加训练？

SELECT p.PracticeNight, COUNT(*) AS NumberOfPlayers
  FROM (SELECT m.Member, t.TeamName, t.PracticeNight
          FROM member AS m
          JOIN team AS t ON m.MemberTeam = t.TeamName
       ) AS p
 GROUP BY p.PracticeNight

请注意，这不保证输出的任何顺序。添加ORDER BY p.PracticeNight将产生序列中的数据 Friday , Monday , Saturday , Sunday , Thursday , Tuesday , Wednesday因为它会按字母顺序排列。你必须引入一个 DayOfWeekNumber对于一周中的每一天，例如，星期日为 0，星期六为 6，或者星期一为 1，星期日为 7，具体取决于您的偏好。

请注意，对于没有团队练习的日子，或者对于当天唯一练习的团队没有成员的日子，此查询不会生成零。解决这个问题要困难得多，并且需要在某处列出一周中可能的几天，以便可以确保所有日期都在输出中表示。

查询 2——每支球队的球员人数和每支球队收取的总费用

“总费用”部分就是每支球队的球员人数乘以球队的球员费用。

第 1 步 - 每支球队都有哪些球员？

SELECT m.Member, t.TeamName
  FROM member AS m
  JOIN team AS t ON m.MemberTeam = t.TeamName

第 2 步——每队有多少球员？

SELECT t.TeamName, COUNT(*) AS NumberOfPlayers
  FROM team AS t
  JOIN member AS m ON t.TeamName = m.MemberTeam
 GROUP BY t.TeamName

第 3 步 - 总费用？

SELECT t.TeamName, COUNT(*) AS NumberOfPlayers, COUNT(*) * t.PlayerFee AS TotalFees
  FROM team AS t
  JOIN member AS m ON t.TeamName = m.MemberTeam
 GROUP BY t.TeamName

第 4 步 - 没有球员的球队？

没有成员的团队将不会包含在输出中。要为此类团队获取零，请使用 LEFT OUTER JOIN(可以缩写为 LEFT JOIN):

SELECT t.TeamName,
       COUNT(m.MemberID) AS NumberOfPlayers,
       COUNT(m.MemberID) * t.PlayerFee AS TotalFees
  FROM team AS t
  LEFT JOIN member AS m ON t.TeamName = m.MemberTeam
 GROUP BY t.TeamName

当您指定列名时，COUNT 聚合不计算 NULL 值。当团队没有成员时(TeamA 在样本数据中遭受这种侮辱)，LOJ 为m.MemberID 生成一个 NULL。 , 所以 COUNT(m.MemberID)生成 0。

^{警告:以上SQL均未经过测试；可能有错误!}