问查询第一、第二、第三位，使用第二位和第三位打破平局，每次在一行

EN

CREATE TABLE times  (ID int, theTime Decimal(5,2));

INSERT INTO times VALUES (1, 6.45),
(2, 12.43),  
(1, 4.52),
(4, 16.24),
(5, 9.43),
(2, 10.46),
(2, 8.46),
(3, 17.49),
(4, 20.46),
(3, 16.49),
(5, 16.97),
(3, 9.43),
(1, 25.77);


SELECT DISTINCT
    id,
    (SELECT thetime FROM times t2 WHERE t.iD = t2.id 
     order by thetime limit 1) lowesttime,
    (SELECT thetime FROM times t2 WHERE t.iD = t2.id 
     order by thetime limit 1,1) secondlowest,
    (SELECT thetime FROM times t2 WHERE t.iD = t2.id 
     order by thetime limit 2,1) thirdlowest
 FROM 
    times t
 ORDER BY
    lowesttime,
    secondlowest,
    thirdlowest
LIMIT 3   ; 



SELECT t1.id, 
       Min(t1.thetime) lowesttime, 
       Min(t2.thetime) secondlowest, 
       Min(t3.thetime) thirdlowest 
FROM   times t1 
       LEFT JOIN times t2 
         ON t1.id = t2.id 
            AND t1.thetime < t2.thetime 
       LEFT JOIN times t3 
         ON t2.id = t3.id 
            AND t2.thetime < t3.thetime 
GROUP  BY t1.id 
ORDER  BY lowesttime, 
          secondlowest, 
          thirdlowest 
LIMIT  3 ;

ID| Time
--------
1 | 4.52
1 | 4.52
1 | 25.77

ID | lowesttime | secondlowest | thirdlowest
-------------------------------------------
1  | 4.52       | 25.77        | null
2  |8.46        | 10.46        | 12.43
3  |9.43        | 16.49        | 17.49

SELECT r1.ID, MIN(r1.time)
FROM #r r1
LEFT JOIN #r r2 
    on r1.Time = r2.Time
    AND r1.ID <> r2.ID
GROUP BY r1.id
ORDER BY MIN(r1.Time), MIN(CASE WHEN r2.time IS NULL THEN r1.time ELSE NULL END)