Zacząłem od tego prostego zapytania, który daje mi trzech mężczyzn z najlepszymi czasami (1, 2 i trzecimi) w wyścigu 125 km.

SELECT *
FROM Coureurs
WHERE Genre=’M’ AND Epreuve='125km' AND TempsPassage IS NOT NULL
ORDER BY TempsPassage
LIMIT 3;

Jednak potrzebowałbym również trzech kobiet (genre = f) z najlepszym czasem na tej odległości. Oraz najlepszych trzech mężczyzn i trzech kobiet dla każdego z pozostałych odległości (tempspassage = 80 km / 65km / 40 km ,,,).

Jest to drogę obok mojego poziomu ... i naprawdę chcę uniknąć budynku oddzielnych zapytań "Hardcodowanych". Z góry dziękuję, Pierre

1
Pierre Tremblay 20 marzec 2021, 05:53

2 odpowiedzi

Najlepsza odpowiedź

Możesz użyć funkcji okna ROW_NUMBER(), aby zdobyć wyniki dla każdego wyścigu / gatunku, a następnie filtrować, aby zwrócić tylko rankingi 1-3:

SELECT *
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY Epreuve, Genre ORDER BY TempsPassage) rn
  FROM Coureurs
  WHERE TempsPassage IS NOT NULL
)
WHERE rn <= 3
ORDER BY (Epreuve + 0), Genre, rn

Jeśli jest przypadek więzi, może spróbuj funkcję okna RANK() zamiast ROW_NUMBER().

0
forpas 20 marzec 2021, 07:41

Dokonywanie następujących założeń / zmian

  • Że kolumna Epreuve jest numeryczna (Store 125, 80 .... zamiast 125 km, 80 km ....)

    • 125km i 80 km, jeśli sortowany będzie można zobaczyć 80 km niż 125 km
    • Wartości tekstowe będą kosztować trochę dodatkowych przechowywania
    • Łatwo jest wyodrębnić wartość z km dołączony, jeśli potrzebuje np. SELECT genre, Epreuve||'km', TempsPassage, Name FROM Coureurs
  • Kolumna TemppSpassage przechowuje czas (przykład używa sekund, ale patrz Wartości czasowe)

Zakładając następujące dane: -

enter image description here

Zakładając również, że chcesz stosunkowo prosty, aby zrozumieć pojedyncze zapytanie, a następnie można odpowiedzieć: -

WITH
     m125 AS (SELECT * FROM Coureurs WHERE genre = 'M' AND Epreuve = 125 ORDER BY TempsPassage ASC LIMIT 3),
     f125 AS (SELECT * FROM Coureurs WHERE genre = 'F' AND Epreuve = 125 ORDER BY TempsPassage ASC LIMIT 3),
     m80 AS (SELECT * FROM Coureurs WHERE genre = 'M' AND Epreuve = 80 ORDER BY TempsPassage ASC LIMIT 3),
     f80 AS (SELECT * FROM Coureurs WHERE genre = 'F' AND Epreuve = 80 ORDER BY TempsPassage ASC LIMIT 3),
     m65 AS (SELECT * FROM Coureurs WHERE genre = 'M' AND Epreuve = 65 ORDER BY TempsPassage ASC LIMIT 3),
     f65 AS (SELECT * FROM Coureurs WHERE genre = 'F' AND Epreuve = 65 ORDER BY TempsPassage ASC LIMIT 3),
     m40 AS (SELECT * FROM Coureurs WHERE genre = 'M' AND Epreuve = 40 ORDER BY TempsPassage ASC LIMIT 3),
     f40 AS (SELECT * FROM Coureurs WHERE genre = 'F' AND Epreuve = 40 ORDER BY TempsPassage ASC LIMIT 3)
SELECT * FROM m125 
    UNION SELECT * FROM f125
    UNION SELECT * FROM m80
    UNION SELECT * FROM f80
    UNION SELECT * FROM m65
    UNION SELECT * FROM f65
    UNION SELECT * FROM m40
    UNION SELECT * FROM f40
    ORDER BY Epreuve DESC,Genre DESC, TempsPassage ASC
;

Wykorzystuje to, co nazywa się CTE (wspólne wyrażenia tabeli), które są zasadniczo tymczasowymi tabelami.

Jeden CTE jest stosowany na permutację gatunku i ePreuve (2 * 4 = 8) jest zbudowany z podobnym zapytaniem.

Po utworzeniu wszystkich CTE 8 oddzielnych tabel Temp są łączone przy użyciu Union.

Korzystanie z danych powyżej wyniku jest (oczywiście możesz sortować wyniki inaczej): -

enter image description here

Poniżej znajduje się SQL używany do testowania powyższego i tworzenia wyników: -

DROP TABLE IF EXISTS Coureurs;
CREATE TABLE IF NOT EXISTS Coureurs (Genre TEXT, Epreuve int, TempsPassage int, name TEXT);
INSERT INTO Coureurs VALUES
    ('M',125,600,'Fred'),('M',125,610,'Bert'),('M',125,630,'Harry'),('M',125,620,'Albert'),('M',125,575,'David')
    
    ,('F',125,615,'Mary'),('F',125,625,'Anne'),('F',125,601,'Betty'),('F',125,625,'Sue'),('F',125,670,'Shelia')
    
    ,('F',80,450,'Louise'),('F',80,460,'Celia'),('F',80,425,'Debra'),('F',80,475,'Diana')
    
    ,('F',65,350,'Zara'),('F',65,360,'Yvonne'),('F',65,325,'Wilma'),('F',65,375,'Ursurla')
    
    ,('F',40,250,'Tracy'),('F',40,260,'Rhona'),('F',40,225,'Samantha'),('F',40,275,'Karen')
    
    ,('M',80,450,'Lou'),('M',80,460,'Colin'),('M',80,425,'Danny'),('M',80,475,'Eddy')
    
    ,('M',65,350,'Zed'),('M',65,360,'Mark'),('M',65,325,'William'),('M',65,375,'Tom')
    
    ,('M',40,250,'Jim'),('M',40,260,'Larry'),('M',40,225,'Peter'),('M',40,275,'Ronald')
;

SELECT * FROM Coureurs ORDER BY Random();
/* Easiest to understand - combining individual queries as CTE
    CTE = Common Table Expression (equates to temporary table) 
*/
WITH
     m125 AS (SELECT * FROM Coureurs WHERE genre = 'M' AND Epreuve = 125 ORDER BY TempsPassage ASC LIMIT 3),
     f125 AS (SELECT * FROM Coureurs WHERE genre = 'F' AND Epreuve = 125 ORDER BY TempsPassage ASC LIMIT 3),
     m80 AS (SELECT * FROM Coureurs WHERE genre = 'M' AND Epreuve = 80 ORDER BY TempsPassage ASC LIMIT 3),
     f80 AS (SELECT * FROM Coureurs WHERE genre = 'F' AND Epreuve = 80 ORDER BY TempsPassage ASC LIMIT 3),
     m65 AS (SELECT * FROM Coureurs WHERE genre = 'M' AND Epreuve = 65 ORDER BY TempsPassage ASC LIMIT 3),
     f65 AS (SELECT * FROM Coureurs WHERE genre = 'F' AND Epreuve = 65 ORDER BY TempsPassage ASC LIMIT 3),
     m40 AS (SELECT * FROM Coureurs WHERE genre = 'M' AND Epreuve = 40 ORDER BY TempsPassage ASC LIMIT 3),
     f40 AS (SELECT * FROM Coureurs WHERE genre = 'F' AND Epreuve = 40 ORDER BY TempsPassage ASC LIMIT 3)
SELECT * FROM m125 
    UNION SELECT * FROM f125
    UNION SELECT * FROM m80
    UNION SELECT * FROM f80
    UNION SELECT * FROM m65
    UNION SELECT * FROM f65
    UNION SELECT * FROM m40
    UNION SELECT * FROM f40
    ORDER BY Epreuve DESC,Genre DESC, TempsPassage ASC
;

DROP TABLE IF EXISTS Coureurs;
0
MikeT 20 marzec 2021, 04:59