Pobieranie najlepszego wyniku konkurencji

0

Witam. Mam problem, który wynika pewnie z braku doświadczenia w sql i Wy mi pomożecie z łatwością :)

Otóż mam bazę danych, która przechowuję między innymi sportowców, starty tych sportowców, konkurencje i rezultaty(wyniki). Tabele wyglądają tak:

 CREATE TABLE sportowcy (
	id					int NOT NULL IDENTITY(1,1) PRIMARY KEY,
	imie				varchar(20) NOT NULL,
	drugie_imie			varchar(20),
	nazwisko			varchar(30) NOT NULL,
	narodowosc			varchar(30) NOT NULL,
	id_miasta			int NOT NULL foreign key references miasta(id)
);

CREATE TABLE starty (
	id					int NOT NULL IDENTITY(1,1) PRIMARY KEY,
	godzina				time NOT NULL,
	id_sportowca		int NOT NULL FOREIGN KEY REFERENCES sportowcy(id),
	nazwa_konkurencji	varchar(255) NOT NULL FOREIGN KEY REFERENCES konkurencje(nazwa),
	nazwa_etapu			varchar(255) NOT NULL FOREIGN KEY REFERENCES etapy(nazwa_etapu),
);

CREATE TABLE rezultaty (
	id					int NOT NULL IDENTITY(1,1) PRIMARY KEY,
	rezultat			float NOT NULL,
	numer_miejsca		int FOREIGN KEY REFERENCES miejsca(numer),
	id_startu			int NOT NULL FOREIGN KEY REFERENCES starty(id)
); 

CREATE TABLE konkurencje (
	nazwa				varchar(255) NOT NULL PRIMARY KEY,
	jednostka_wyniku	char(3) NOT NULL
);

Mój problem polega na tym, że nie wiem jak stworzyć zapytanie, które pobierze mi najlepsze wyniki i dane sportowców, którzy je osiągali z poszczególnych konkurencji. Problem też jest w tym, że np. w rzucie młotem najlepszy wynik będzie jak największy. Czyli 40m jest lepsze od 35m, ale za to w biegu na 100m lepszym wynikiem jest 10s niż 11s.

Mam nadzieje, że wytłumaczyłem dość jasno o co mi chodzi i prosiłbym o pomoc

1

Jeśli w bazie nie przechowujesz flagi decydującej czy dla danej konkurencji lepszy jest mniejszy czy większy wynik to nie ma jak w jednym zapytaniu tego zrobić. Ogólnie można napisać zapytanie dla części konkurencji z wybieraniem wyniku MAX() i drugie z wybieraniem wyniku MIN(), a między nimi UNION, ale bez flagi będziesz musiał aktualizować zapytanie/procedurę po każdym dodaniu nowej konkurencji, bo niby na jakiej podstawie można je teraz rozróżnić?.
Proponuje dodać pole bit w tabeli [konkurencje]

edit: I dołóż jeszcze jakiś Id do tabeli konkurencje, bo PK i index na varchar to nie jest za dobry pomysł tak ogólnie :) a jak Ci zależy na unikatowych nazwach konkurencji to załóż constraint'a na tym polu.

edit2: W tabeli konkurencje masz kolumne jednostka_wyniku jako typ char(3) ;) Czy wszystkie jednostki będą miały 3 literowe skróty? np. metr ... [m ] <- m i 2 spacje, tak uważaj na to jak będziesz robił jakieś porównania lub zmień typ na nvarchar/varchar.

0

No tej flagi nie mam i nie będę miał jak dorobić bo to już oddany projekt bazy jest(na studiach na zaliczenie przedmiotu, nie do klienta) i teraz dorabiamy wszystko w SQL do tego i robimy przykładowe zapytania. Czyli bez modyfikacji bazy nie da rady tego zrobić?
Dzięki za podpowiedzi ;)

1

Oczywiście, że się da. Trochę mało optymalne, ale powinno zadziałać:

WITH NajlepszeWyniki AS
(
	SELECT K.nazwa as konkurencja, 
		MAX(R.rezultat) as wynik
		FROM starty S
			inner join rezultaty R on R.id_startu = S.id
			inner join konkurencje K on S.nazwa_konkurencji = K.nazwa 
			inner join sportowcy Sp on S.id_sportowca = Sp.id
		WHERE K.nazwa in ('skok','strzelanie')
		GROUP BY K.nazwa
	UNION	
	SELECT K.nazwa, 
		MIN(R.rezultat)
		FROM starty S
			inner join rezultaty R on R.id_startu = S.id
			inner join konkurencje K on S.nazwa_konkurencji = K.nazwa 
			inner join sportowcy Sp on S.id_sportowca = Sp.id
		WHERE K.nazwa in ('bieg','pływanie')
		GROUP BY K.nazwa
)

SELECT Nw.konkurencja, Nw.wynik, Sp.imie, Sp.nazwisko
FROM NajlepszeWyniki Nw
	inner join konkurencje K on K.nazwa = Nw.konkurencja
	inner join starty S on S.nazwa_konkurencji = K.nazwa
	inner join rezultaty R on R.rezultat = Nw.wynik 
		and R.id_startu = S.id
	inner join sportowcy Sp on Sp.id = S.id_sportowca

Listę konkurencji będziesz musiał uzupełnić według nazw z waszej bazy, odpowiednio przyporządkować czy lepszy znaczy MIN(), czy MAX().

0

Dzięki bardzo :) Działa super i dawno nikt nie podesłał gotowca, więc tym bardziej się zdziwiłem ;)

1 użytkowników online, w tym zalogowanych: 0, gości: 1