mysql - 返回比赛中事件的最佳结果

Question

我有这样的问题

select R.EID, name, record, date, TYPE_NAME, METERS
from PLAYER P, REGISTER R, COMPETITION C, EVENT E, TYPE T, DISTANCE D
where P.PID=R.PID 
  and R.CID=C.CID 
  and R.EID=E.EID 
  and T.TYID=E.TYID 
  and D.DID=E.DID 
order by R.EID, record

返回结果是这样的

EID  Name    Record       Date       Type_Name  Meters

1   HUONG   00:07:45    2011-03-22  Freestyle   100
1   NHUNG   00:16:25    2011-03-22  Freestyle   100
1   NULL    00:23:23    2011-03-22  Freestyle   100
1    HUY    00:23:43    2011-03-22  Freestyle   100
1   THAO    00:26:34    2011-03-22  Freestyle   100
1   TRANG   00:34:23    2011-03-22  Freestyle   100
1   HUNG    00:34:23    2011-03-22  Freestyle   100
1   CUONG   00:34:54    2011-03-22  Freestyle   100
1    BON    00:45:21    2011-03-22  Freestyle   100
2   HUNG    00:02:34    2011-03-22  Freestyle   800
2   NHUNG   00:15:25    2011-03-22  Freestyle   800
2    HUY    00:22:33    2011-03-22  Freestyle   800
2   TRANG   00:23:25    2011-03-22  Freestyle   800

对于比赛中的每个项目，它会多次列出参加该项目的每位游泳运动员的 EID。

我想要的只是为每个比赛 (CID) 中的每个项目 (EID)，根据他们的记录获得第一、第二、第三名的竞争对手的水平列表。比赛有多个事件，每个事件可以在多个比赛中发生，所以我通过 EID 和比赛日期定义每个事件。

我试过了

select x.EID, name, date, TYPE_NAME, METERS from 
(select R.EID, name, date, TYPE_NAME, METERS
from PLAYER P, REGISTER R, EVENT E, TYPE T, DISTANCE D
where P.PID=R.PID and R.CID=C.CID 
  and R.EID=E.EID 
  and T.TYID=E.TYID 
  and D.DID=E.DID 
order by R.EID, record) x
group by x.EID

然后为第一个、第二个、第三个创建 3 个 View 并使用左连接。

我有一个想法，通过使用 where record not in (1st record) 对记录进行排序，并在第二和第三次继续这样做，但我认为如果我有相同的记录，它将消除一些结果。

我想要这样的结果

 EID    Date         First   Second  Third

    1   2013-03-22  HUONG   NHUNG   NULL
    2   2013-03-22  HUNG    NHUNG   HUY
    3   2013-11-13  TRANG   DOAN    LINH

Answer 1

您可以使用子查询和 LIMIT 关键字来实现。使用 LIMIT 如果它们具有相同的记录时间，您将不会错过任何条目。

SQL

SELECT   r.CID, r.EID, c.date,
         (SELECT   p.name 
          FROM     REGISTER rr 
          JOIN     PLAYER p on rr.PID = p.PID 
          WHERE    rr.CID = r.CID and rr.EID = r.EID
          ORDER BY rr.record LIMIT 0,1) as 'First',
         (SELECT   p.name 
          FROM     REGISTER rr 
          JOIN     PLAYER p on rr.PID = p.PID 
          WHERE    rr.CID = r.CID and rr.EID = r.EID
          ORDER BY rr.record LIMIT 1,1) as 'Second',
         (SELECT   p.name 
          FROM     REGISTER rr 
          JOIN     PLAYER p on rr.PID = p.PID 
          WHERE    rr.CID = r.CID and rr.EID = r.EID
          ORDER BY rr.record LIMIT 2,1) as 'Third'
FROM     REGISTER r
JOIN     COMPETITION c on r.CID = c.CID
GROUP BY r.CID, r.EID, c.date

架构:

CREATE TABLE COMPETITION
(
    CID INT AUTO_INCREMENT PRIMARY KEY,
    date DATETIME
);

CREATE TABLE TYPE
(
    TYID INT AUTO_INCREMENT PRIMARY KEY,
    TYPE_NAME VARCHAR(255)
);

CREATE TABLE DISTANCE
(
    DID INT AUTO_INCREMENT PRIMARY KEY,
    METERS INT
);

CREATE TABLE EVENT
(
    EID INT AUTO_INCREMENT PRIMARY KEY,
    TYID INT REFERENCES TYPE(TYID),
    DID INT REFERENCES DISTANCE(DID)
);

CREATE TABLE PLAYER
(
    PID INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) 
);

CREATE TABLE REGISTER
(
    PID INT REFERENCES PLAYER(PID),
    CID INT REFERENCES COMPETITION(CID),
    EID INT REFERENCES EVENT(EID),
    record INT
);

INSERT INTO COMPETITION (date) values ('2011-03-22');
INSERT INTO TYPE (TYPE_NAME) values ('Freestyle');
INSERT INTO DISTANCE (METERS) values (100), (800);
INSERT INTO EVENT (TYID, DID) values (1,1), (1,2);
INSERT INTO PLAYER (name) values ('HUONG'), ('NHUNG'), ('NULL'), ('HUY'), ('THAO'), ('TRANG'), ('HUNG'), ('CUONG'), ('BON');
INSERT INTO REGISTER (PID, CID, EID, record) values 
(1, 1, 1, 465),
(2, 1, 1, 985),
(3, 1, 1, 1403),
(4, 1, 1, 1423),
(5, 1, 1, 1594),
(6, 1, 1, 2063),
(7, 1, 1, 2063),
(8, 1, 1, 2094),
(9, 1, 1, 2721),
(7, 1, 2, 154),
(2, 1, 2, 925),
(4, 1, 2, 1353),
(6, 1, 2, 1405);