php - SQL - ORDER BY 未正确读取位置

Question

编写了以下数据库并被要求执行以下任务：

8.列出给定成员参加的所有比赛的结果，使用成员的名字和姓氏来指定成员。结果应包括成员的名字、姓氏和等级、系列名称和年份、比赛名称、日期和位置。结果应按其位置排序，最佳位置优先。如果会员已参加比赛但尚未参加，则该比赛不应包含在列表中。不应显示 ID。

DROP DATABASE IF EXISTS canary;
CREATE DATABASE canary;
USE canary;

  CREATE TABLE member(
  memberID INT AUTO_INCREMENT,
  firstName VARCHAR(40),
  lastName VARCHAR(40),
  grade char, -- j, junior, s, senior
  PRIMARY KEY(memberID)
);

  CREATE TABLE series(
  seriesID INT AUTO_INCREMENT,
  seriesName VARCHAR(40),
  seriesYear INTEGER,
  PRIMARY KEY(seriesID)
);

CREATE TABLE race(
  raceID INT AUTO_INCREMENT,
  seriesID INT,
  raceName VARCHAR(40),
  raceDate DATE,
  FOREIGN KEY series_key(seriesID) REFERENCES series(seriesID),
  PRIMARY KEY(raceID),
);
CREATE TABLE competitor(
  competitorID INT AUTO_INCREMENT,
  memberID INT,
  raceID INT,
  position INT,
  FOREIGN KEY race_key(raceID) REFERENCES race(raceID),
  FOREIGN KEY member_key(memberID) REFERENCES member(memberID),
  PRIMARY KEY(competitorID),
);
CREATE TABLE course(
    courseID INT AUTO_INCREMENT,
    courseName VARCHAR(50),
    courseLevel INT,
    PRIMARY KEY(courseID)
);
CREATE TABLE enrolment(
  enrolmentID INT AUTO_INCREMENT,
  memberID INT, 
  courseID INT,
  FOREIGN KEY course_key(courseID) REFERENCES course(courseID),
  FOREIGN KEY member_key(memeberID) REFERENCES memner(memberID)
  PRIMARY KEY(enrolmentID)
);
INSERT INTO series VALUES(NULL, 'BritishSeries', 2020);
INSERT INTO series VALUES(NULL, 'EuropeanSeries', 2020);
INSERT INTO series VALUES(NULL, 'InternationalSeries', 2020);
INSERT INTO course VALUES(NULL, 'WhiteWaterRafting', 2);
INSERT INTO course VALUES(NULL, 'BasicTraining', 1);
INSERT INTO course VALUES(NULL, 'ProfessionalTraining', 3);
INSERT INTO race VALUES (NULL, 1, 'Qualifiers', '2018-04-12')
INSERT INTO race VALUES (NULL, 1, 'Regionals', '2019-05-16')
INSERT INTO race VALUES (NULL, 2, 'Nationals', '2019-07-19')
INSERT INTO race VALUES (NULL, 3, 'Internationals', '2019-09-21')
INSERT INTO member VALUES(NULL, 'Jimmy', 'Smith', senior);
INSERT INTO member VALUES(NULL, 'Macky', 'Gee', junior);
INSERT INTO member VALUES(NULL, 'Tempa', 'T', junior);
INSERT INTO member VALUES(NULL, 'Graeme', 'Maigai', senior);
INSERT INTO member VALUES(NULL, 'Willy', 'Nelson', junior);
SELECT * FROM member;
DELETE FROM race WHERE raceID='3';
INSERT INTO enrolmemnt VALUES(NULL, 1, 2);
 -- Member 1
INSERT INTO competitor VALUES(NULL, 1, 2, 3);
INSERT INTO competitor VALUES(NULL, 1, 1, 1);
INSERT INTO competitor VALUES(NULL, 1, 4, 5);
-- Member 2
INSERT INTO competitor VALUES(NULL, 2, 2, 2);
INSERT INTO competitor VALUES(NULL, 2, 1, 3);
INSERT INTO competitor VALUES(NULL, 2, 4, 8);
-- Member 3
INSERT INTO competitor VALUES(NULL, 3, 2, 1);
INSERT INTO competitor VALUES(NULL, 3, 1, 4);
INSERT INTO competitor VALUES(NULL, 3, 4, 3);
-- Member 4
INSERT INTO competitor VALUES(NULL, 4, 2, 5);
INSERT INTO competitor VALUES(NULL, 4, 1, 2);
SELECT firstName, lastName, grade, seriesName, seriesYear, raceName, raceDate, position FROM member, series, race, competitor
WHERE competitor.memberID = member.memberID ORDER BY position;

但是，以下仅返回 1 和 2 作为位置，而不是我为每个竞争对手输入的位置。

SELECT firstName, lastName, Grade, seriesName, seriesYear, raceName, raceDate, position FROM member, series, race,竞争对手 WHEREpetition.memberID = member.memberID ORDER BY position;

请帮我

标签： phpsqlphpmyadmin