Zwracanie TOP1 danej Grupy w Access SQL

Zwracanie TOP1 danej Grupy w Access SQL
brus97
B9
brus97
B9
  • Rejestracja:ponad 7 lat
  • Ostatnio:dzień
  • Postów:65
0

Siema. Chciałbym się zapytać jak w Access SQL zwrócić TOP1 danej grupy. Mam taki kod:

Kopiuj
SELECT imie, nazwisko, rok, Sum(punkty)
FROM wyniki, kierowcy, wyscigi
WHERE wyscigi.id_wyscigu=wyniki.id_wyscigu AND wyniki.id_kierowcy=kierowcy.id_kierowcy
GROUP BY imie, nazwisko, rok
HAVING rok LIKE 2000 OR rok LIKE 2006 OR rok LIKE 2010
ORDER BY rok DESC , sum(punkty) DESC;

Pogrupowałem tak że dla każdego zawodnika (imie, nazwisko) z lat 2000,2006,2010 jest wyliczona suma punktów jakie w tych latach uzyskał. Lata posortowane są malejąco, tak samo jak punkty. Oczywiście mogę odpalić kwerendę i odczytać sobie samemu TOP 1 z danego roku i pewnie byłoby szybciej, ale ciekawi mnie jak wyciągnąć pierwszy wiersz dla każdego roku?

Zadanie jest z matury 2015, a dokładnie 6.3. Link do arkusza:

cke.gov.pl/images/_EGZAMIN_MATURALNY_OD_2015/Arkusze_egzaminacyjne/2015/formula_od_2015/MIN-R2_1P-152.pdf

Udostępnij
Kopiuj link do postu Kopiuj link do postu jako Markdown
Komentuj
caderda
CA
caderda
CA
  • Rejestracja:około 9 lat
  • Ostatnio:około 6 lat
  • Postów:12
0
Kopiuj
SELECT TOP 1 imie, nazwisko, rok, Sum(punkty)
FROM wyniki, kierowcy, wyscigi
WHERE wyscigi.id_wyscigu=wyniki.id_wyscigu AND wyniki.id_kierowcy=kierowcy.id_kierowcy
GROUP BY imie, nazwisko, rok
HAVING rok LIKE 2000 OR rok LIKE 2006 OR rok LIKE 2010
ORDER BY rok DESC , sum(punkty) DESC;

https://www.w3schools.com/sqL/sql_top.asp

edytowany 1x, ostatnio: caderda
PA
To zwróci tylko jeden rekord...
Udostępnij
Kopiuj link do postu Kopiuj link do postu jako Markdown
Komentuj
Panczo
PA
Panczo
PA
  • Rejestracja:ponad 22 lata
  • Ostatnio:7 minut
  • Postów:3866
1

Nie ma gotowej składni do osiągnięcia tego, musiałbyś najpierw policzyć punkty funkcją sum, później użyć max do wyciagnięcia wartości największych w danym roku i z joinować ze sobą by wyciągnąć pierwszych w danym sezonie, jak dasz jakieś dane to Ci to mogę napisać, bo z głowy w Accessowej składni nie napiszę...

Tu jednak poszedłbym na łatwiznę i uzył uniona:

Kopiuj
SELECT 
     imie
     , nazwisko
     , rok
     , Sum(punkty)
FROM 
    wyniki, kierowcy, wyscigi
WHERE 
    wyscigi.id_wyscigu=wyniki.id_wyscigu 
    AND wyniki.id_kierowcy=kierowcy.id_kierowcy
    AND ROK = 2000
GROUP BY 
    imie
   , nazwisko
   , rok
ORDER BY 
     sum(punkty) DESC
union 
SELECT 
     imie
     , nazwisko
     , rok
     , Sum(punkty)
FROM 
    wyniki, kierowcy, wyscigi
WHERE 
    wyscigi.id_wyscigu=wyniki.id_wyscigu 
    AND wyniki.id_kierowcy=kierowcy.id_kierowcy
    AND ROK = 2006
GROUP BY 
    imie
   , nazwisko
   , rok
ORDER BY 
     sum(punkty) DESC
union
SELECT 
     imie
     , nazwisko
     , rok
     , Sum(punkty)
FROM 
    wyniki, kierowcy, wyscigi
WHERE 
    wyscigi.id_wyscigu=wyniki.id_wyscigu 
    AND wyniki.id_kierowcy=kierowcy.id_kierowcy
    AND ROK = 2012
GROUP BY 
    imie
   , nazwisko
   , rok
ORDER BY 
     sum(punkty) DESC
Udostępnij
Kopiuj link do postu Kopiuj link do postu jako Markdown
Komentuj
brus97
B9
brus97
B9
  • Rejestracja:ponad 7 lat
  • Ostatnio:dzień
  • Postów:65
0

Wiele rozjaśniło mi to. W twoim kodzie jest problem, że order by nie można umieścić w każdym pytaniu tylko na końcu. Zrobiłem coś takiego:

Kopiuj
SELECT TOP 1  imie, nazwisko, rok, Sum(punkty)
FROM wyniki, kierowcy, wyscigi
WHERE wyscigi.id_wyscigu=wyniki.id_wyscigu AND wyniki.id_kierowcy=kierowcy.id_kierowcy
GROUP BY imie, nazwisko, rok
HAVING rok LIKE 2010


union

SELECT TOP 1 imie, nazwisko, rok,  Sum(punkty)
FROM wyniki, kierowcy, wyscigi
WHERE wyscigi.id_wyscigu=wyniki.id_wyscigu AND wyniki.id_kierowcy=kierowcy.id_kierowcy
GROUP BY imie, nazwisko, rok
HAVING  rok LIKE 2006


union 

SELECT TOP 1 imie, nazwisko, rok, sum(punkty)
FROM wyniki, kierowcy, wyscigi
WHERE wyscigi.id_wyscigu=wyniki.id_wyscigu AND wyniki.id_kierowcy=kierowcy.id_kierowcy
GROUP BY imie, nazwisko, rok
HAVING rok LIKE 2000
ORDER BY 3 DESC , 4 DESC

Wszystko było by fajnie, tylko najpierw bierze TOP 1, a dopiero potem grupuje po roku i sumie punktów :/ Wiece może jak to obejść?

Tutaj link do pliku access

https://files.fm/u/b362fxuv#_

Udostępnij
Kopiuj link do postu Kopiuj link do postu jako Markdown
Komentuj
Panczo
PA
Panczo
PA
  • Rejestracja:ponad 22 lata
  • Ostatnio:7 minut
  • Postów:3866
1

Ach ten access ;)

Kopiuj
select * from  (
SELECT top 1
     imie
     , nazwisko
     , rok
     , SUM(punkty)
FROM 
    wyniki, kierowcy, wyscigi
WHERE 
    wyscigi.id_wyscigu=wyniki.id_wyscigu 
    AND wyniki.id_kierowcy=kierowcy.id_kierowcy
    AND ROK = 2000
GROUP BY 
    imie
   , nazwisko
   , rok
ORDER BY 
     SUM(punkty) DESC) as d1
UNION 
select * from (
SELECT top 1
     imie
     , nazwisko
     , rok
     , SUM(punkty)
FROM 
    wyniki, kierowcy, wyscigi
WHERE 
    wyscigi.id_wyscigu=wyniki.id_wyscigu 
    AND wyniki.id_kierowcy=kierowcy.id_kierowcy
    AND ROK = 2006
GROUP BY 
    imie
   , nazwisko
   , rok
ORDER BY 
     SUM(punkty) DESC) as d2
UNION
select * from (
SELECT top 1
     imie
     , nazwisko
     , rok
     , SUM(punkty)
FROM 
    wyniki, kierowcy, wyscigi
WHERE 
    wyscigi.id_wyscigu=wyniki.id_wyscigu 
    AND wyniki.id_kierowcy=kierowcy.id_kierowcy
    AND ROK = 2012
GROUP BY 
    imie
   , nazwisko
   , rok
ORDER BY 
     SUM(punkty) DESC) as d3

To przejdzie

Udostępnij
Kopiuj link do postu Kopiuj link do postu jako Markdown
Komentuj
brus97
B9
brus97
B9
  • Rejestracja:ponad 7 lat
  • Ostatnio:dzień
  • Postów:65
0

Dzięki wielkie. Nie wiedziałem że trzeba to aż tak przekombinować :/ To taki problem tylko w access jest?

Udostępnij
Kopiuj link do postu Kopiuj link do postu jako Markdown
Komentuj
Panczo
PA
Panczo
PA
  • Rejestracja:ponad 22 lata
  • Ostatnio:7 minut
  • Postów:3866
1

Skoro dałeś wersje z danymi, to masz rozwiązanie bez uniona:

Kopiuj
SELECT i.Rok,
       Kierowcy.Imie,
       Kierowcy.Nazwisko,
       Kierowcy.Kraj,
       i.pkt
FROM(
(
    SELECT Rok,
           Id_kierowcy,
           SUM(Punkty) AS pkt
    FROM Wyniki
         INNER JOIN Wyscigi ON Wyniki.Id_wyscigu = Wyscigi.Id_wyscigu
    GROUP BY rok,
             id_kierowcy
) AS i
INNER JOIN
(
    SELECT rok,
           MAX(pkt) AS pm
    FROM
(
    SELECT Rok,
           Id_kierowcy,
           SUM(Punkty) AS pkt
    FROM Wyniki
         INNER JOIN Wyscigi ON Wyniki.Id_wyscigu = Wyscigi.Id_wyscigu
    GROUP BY rok,
             id_kierowcy
)
    GROUP BY rok
) AS m ON(i.pkt = m.pm)
         AND (i.rok = m.rok))
INNER JOIN Kierowcy ON i.Id_kierowcy = Kierowcy.Id_kierowcy
WHERE i.rok IN(2000, 2006, 2012);

W innych bazach masz inne możliwości jak numer wiersza, który to bardzo ułatwia i wtedy to samo osiągasz tak:

Kopiuj
select
    imie,
    nazwisko,
    rok,
    pkt
from (
    select
	   *
	   ,row_number() over (partition by rok order by pkt desc) r
    from (select
		  k.imie
		  ,k.nazwisko
		  ,gp.rok
		  ,sum(punkty) pkt
	   from 
		  kierowcy k
		  inner join wyniki w on k.id_kierowcy = w. id_kierowcy
		  inner join wyscigi gp on gp.id_wyscigu=w.id_wyscigu
	   group by  
		  k.imie
		  ,k.nazwisko
		  ,gp.rok) dt
    ) rs
where
    r=1
    and rok in (2000,2006,2012)
Udostępnij
Kopiuj link do postu Kopiuj link do postu jako Markdown
Komentuj
brus97
B9
brus97
B9
  • Rejestracja:ponad 7 lat
  • Ostatnio:dzień
  • Postów:65
0

Trochę bardziej to pokomplikowane, a nie wiele krótsze. Będę musiał to powoli przeanalizować bo to dopiero początki z sql.

Udostępnij
Kopiuj link do postu Kopiuj link do postu jako Markdown
Komentuj
Panczo
PA
Panczo
PA
  • Rejestracja:ponad 22 lata
  • Ostatnio:7 minut
  • Postów:3866
0

Nie miało być krótsze... Bez imiona łatwiej zmienić warunek na rok, niż zmieniać ilość podzapytań.

Udostępnij
Kopiuj link do postu Kopiuj link do postu jako Markdown
Komentuj
brus97
B9
brus97
B9
  • Rejestracja:ponad 7 lat
  • Ostatnio:dzień
  • Postów:65
0

Trochę nie rozumiem tego po dłuższej analizie. Zaczynając od końca:

Kopiuj
SELECT Rok, Id_kierowcy,  SUM(Punkty) AS pkt
FROM Wyniki          
INNER JOIN Wyscigi ON Wyniki.Id_wyscigu = Wyscigi.Id_wyscigu          
GROUP BY rok, id_kierowcy

Bierzemy rok, id_kierowcy, suma z punktów łączymy dwie tabele wyniki i wyscigi na podstawie id wyscigu i grupujemy przez rok i id_kierowcy.

Kopiuj
(
 SELECT rok,   MAX(pkt) AS pm
 FROM

(
SELECT Rok, Id_kierowcy,  SUM(Punkty) AS pkt
FROM Wyniki          
INNER JOIN Wyscigi ON Wyniki.Id_wyscigu = Wyscigi.Id_wyscigu          
GROUP BY rok, id_kierowcy     
    
            
)   GROUP BY rok

Z tej tabeli co przed chwilą opisałem bierzemy rok i największą wartość punktów, nazywamy ją jako "pkt" i grupujemy po roku. Aktualnie mamy wszystkie lata i wartość z maksymalną ilością punktów dla nich.

Kopiuj
(
  SELECT Rok,   Id_kierowcy,    SUM(Punkty) AS pkt
  FROM Wyniki
  INNER JOIN Wyscigi ON Wyniki.Id_wyscigu = Wyscigi.Id_wyscigu       
  GROUP BY rok, id_kierowcy
) AS i   
  
         
INNER JOIN

Do tej tabeli co wyżej opisałem dołączamy tą z której wyciągamy rok, id_kierowcy i sume punktów grupujemy po roku i id i zapisujemy ją jako "i". Tutaj trochę nie rozumiem bo przecież wcześniej taką samą tabele stworzyliśmy i wyciągnęliśmy z niej rok i max pkt. Chodzi o to że w kolejnym punkcie wyciągamy imie, nazwisko itd. na podstawie tej tabeli z id?

Kopiuj
AS m ON(i.pkt = m.pm)    AND (i.rok = m.rok))
INNER JOIN Kierowcy ON i.Id_kierowcy = Kierowcy.Id_kierowcy
WHERE i.rok IN(2000, 2006, 2012);

Trochę nie czaje tego

Kopiuj
AS m ON(i.pkt = m.pm)    AND (i.rok = m.rok))

Nie spotkałem się z takim zapisem jeszcze bo to w sumie pierwszy tydzie nauki. Prosiłbym o drobna pomoc.

Udostępnij
Kopiuj link do postu Kopiuj link do postu jako Markdown
Komentuj
Panczo
PA
Panczo
PA
  • Rejestracja:ponad 22 lata
  • Ostatnio:7 minut
  • Postów:3866
1

Dobrze kombinujesz. Podzapytanie i bierze indywidualne sumy punktów w ramach roku dla kierowcy. Podzapytanie m bierze maksymalną ilość punktów w ramach roku. Najważniejszy jest warunek po którym łączymy ze sobą te 2 podzapytanie, po dwóch kolumnach:rok i ilość punktów. To powoduje, że dostaniemy w wyniku tylko kierowców z maksymalną ilością punktów.
Na przykładzie, jeżeli i ma w 3 kierowców w roku 2000 z sumami punktów 1,2,3 to m będzie miało jeden zapis z rokiem 2000 z wartością 3. To połączenie tabel zwróci tylko kierowcę z ilością 3 punktów.
To służy do wyciągnięcia kierowców z największą ilością punktów w ramach roku.

Udostępnij
Kopiuj link do postu Kopiuj link do postu jako Markdown
Komentuj
brus97
B9
brus97
B9
  • Rejestracja:ponad 7 lat
  • Ostatnio:dzień
  • Postów:65
0

Dobra już czaje wszytko. Źle spojrzałem na nawiasy i dlatego nic mi się nie zgadzało. A te inner joiny można zastąpić WHERE. Chodzi mi o te

Kopiuj
 INNER JOIN Wyscigi ON Wyniki.Id_wyscigu = Wyscigi.Id_wyscigu

Tylko wtedy do from trzeba przenieść wyscigi. Nie stosuje się tego z jakiegoś względu, czy tylko dlatego że gorzej się to czyta?

Udostępnij
Kopiuj link do postu Kopiuj link do postu jako Markdown
Komentuj
Marcin.Miga
Marcin.Miga
Marcin.Miga
Marcin.Miga
  • Rejestracja:prawie 17 lat
  • Ostatnio:2 dni
  • Postów:2792
2

Prosta zasada - do łączenia JOIN, do filtrowania WHERE.

Udostępnij
Kopiuj link do postu Kopiuj link do postu jako Markdown
Komentuj
Panczo
PA
Panczo
PA
  • Rejestracja:ponad 22 lata
  • Ostatnio:7 minut
  • Postów:3866
0

Jak pisał Marcin taki zapis inner joina jest nieczytelny, szczególnie jak dochodzą inne warunki. To podstawa, żeby podczas analizowania zapytania widzieć od razu po czym łączone są tabele, a po czym filtrowane

Udostępnij
Kopiuj link do postu Kopiuj link do postu jako Markdown
Komentuj
Treść
Podgląd
Kliknij, aby dodać treść...
Kliknij, aby dodać załącznik lub wklej ze schowka.
Instrukcja obsługi Markdown
Pomoc 1.18.8

Typografia

Edytor obsługuje składnie Markdown, w której pojedynczy akcent *kursywa* oraz _kursywa_ to pochylenie. Z kolei podwójny akcent **pogrubienie** oraz __pogrubienie__ to pogrubienie. Dodanie znaczników ~~strike~~ to przekreślenie.

Możesz dodać formatowanie komendami , , oraz .

Ponieważ dekoracja podkreślenia jest przeznaczona na linki, markdown nie zawiera specjalnej składni dla podkreślenia. Dlatego by dodać podkreślenie, użyj <u>underline</u>.

Komendy formatujące reagują na skróty klawiszowe: Ctrl+B, Ctrl+I, Ctrl+U oraz Ctrl+S.

Linki

By dodać link w edytorze użyj komendy lub użyj składni [title](link). URL umieszczony w linku lub nawet URL umieszczony bezpośrednio w tekście będzie aktywny i klikalny.

Jeżeli chcesz, możesz samodzielnie dodać link: <a href="link">title</a>.

Wewnętrzne odnośniki

Możesz umieścić odnośnik do wewnętrznej podstrony, używając następującej składni: [[Delphi/Kompendium]] lub [[Delphi/Kompendium|kliknij, aby przejść do kompendium]]. Odnośniki mogą prowadzić do Forum 4programmers.net lub np. do Kompendium.

Wspomnienia użytkowników

By wspomnieć użytkownika forum, wpisz w formularzu znak @. Zobaczysz okienko samouzupełniające nazwy użytkowników. Samouzupełnienie dobierze odpowiedni format wspomnienia, zależnie od tego czy w nazwie użytkownika znajduje się spacja.

Znaczniki HTML

Dozwolone jest używanie niektórych znaczników HTML: <a>, <b>, <i>, <kbd>, <del>, <strong>, <dfn>, <pre>, <blockquote>, <hr/>, <sub>, <sup> oraz <img/>.

Skróty klawiszowe

Dodaj kombinację klawiszy komendą notacji klawiszy lub skrótem klawiszowym Alt+K.

Reprezentuj kombinacje klawiszowe używając taga <kbd>. Oddziel od siebie klawisze znakiem plus, np <kbd>Alt+Tab</kbd>.

Indeks górny oraz dolny

Przykład: wpisując H<sub>2</sub>O i m<sup>2</sup> otrzymasz: H2O i m2.

Składnia Tex

By precyzyjnie wyrazić działanie matematyczne, użyj składni Tex.

<tex>arcctg(x) = argtan(\frac{1}{x}) = arcsin(\frac{1}{\sqrt{1+x^2}})</tex>

Kod źródłowy

Krótkie fragmenty kodu

Wszelkie jednolinijkowe instrukcje języka programowania powinny być zawarte pomiędzy obróconymi apostrofami: `kod instrukcji` lub ``console.log(`string`);``.

Kod wielolinijkowy

Dodaj fragment kodu komendą . Fragmenty kodu zajmujące całą lub więcej linijek powinny być umieszczone w wielolinijkowym fragmencie kodu. Znaczniki ``` lub ~~~ umożliwiają kolorowanie różnych języków programowania. Możemy nadać nazwę języka programowania używając auto-uzupełnienia, kod został pokolorowany używając konkretnych ustawień kolorowania składni:

```javascript
document.write('Hello World');
```

Możesz zaznaczyć również już wklejony kod w edytorze, i użyć komendy  by zamienić go w kod. Użyj kombinacji Ctrl+`, by dodać fragment kodu bez oznaczników języka.

Tabelki

Dodaj przykładową tabelkę używając komendy . Przykładowa tabelka składa się z dwóch kolumn, nagłówka i jednego wiersza.

Wygeneruj tabelkę na podstawie szablonu. Oddziel komórki separatorem ; lub |, a następnie zaznacz szablonu.

nazwisko;dziedzina;odkrycie
Pitagoras;mathematics;Pythagorean Theorem
Albert Einstein;physics;General Relativity
Marie Curie, Pierre Curie;chemistry;Radium, Polonium

Użyj komendy by zamienić zaznaczony szablon na tabelkę Markdown.

Lista uporządkowana i nieuporządkowana

Możliwe jest tworzenie listy numerowanych oraz wypunktowanych. Wystarczy, że pierwszym znakiem linii będzie * lub - dla listy nieuporządkowanej oraz 1. dla listy uporządkowanej.

Użyj komendy by dodać listę uporządkowaną.

1. Lista numerowana
2. Lista numerowana

Użyj komendy by dodać listę nieuporządkowaną.

* Lista wypunktowana
* Lista wypunktowana
** Lista wypunktowana (drugi poziom)

Składnia Markdown

Edytor obsługuje składnię Markdown, która składa się ze znaków specjalnych. Dostępne komendy, jak formatowanie , dodanie tabelki lub fragmentu kodu są w pewnym sensie świadome otaczającej jej składni, i postarają się unikać uszkodzenia jej.

Dla przykładu, używając tylko dostępnych komend, nie możemy dodać formatowania pogrubienia do kodu wielolinijkowego, albo dodać listy do tabelki - mogłoby to doprowadzić do uszkodzenia składni.

W pewnych odosobnionych przypadkach brak nowej linii przed elementami markdown również mógłby uszkodzić składnie, dlatego edytor dodaje brakujące nowe linie. Dla przykładu, dodanie formatowania pochylenia zaraz po tabelce, mogłoby zostać błędne zinterpretowane, więc edytor doda oddzielającą nową linię pomiędzy tabelką, a pochyleniem.

Skróty klawiszowe

Skróty formatujące, kiedy w edytorze znajduje się pojedynczy kursor, wstawiają sformatowany tekst przykładowy. Jeśli w edytorze znajduje się zaznaczenie (słowo, linijka, paragraf), wtedy zaznaczenie zostaje sformatowane.

  • Ctrl+B - dodaj pogrubienie lub pogrub zaznaczenie
  • Ctrl+I - dodaj pochylenie lub pochyl zaznaczenie
  • Ctrl+U - dodaj podkreślenie lub podkreśl zaznaczenie
  • Ctrl+S - dodaj przekreślenie lub przekreśl zaznaczenie

Notacja Klawiszy

  • Alt+K - dodaj notację klawiszy

Fragment kodu bez oznacznika

  • Alt+C - dodaj pusty fragment kodu

Skróty operujące na kodzie i linijkach:

  • Alt+L - zaznaczenie całej linii
  • Alt+, Alt+ - przeniesienie linijki w której znajduje się kursor w górę/dół.
  • Tab/⌘+] - dodaj wcięcie (wcięcie w prawo)
  • Shit+Tab/⌘+[ - usunięcie wcięcia (wycięcie w lewo)

Dodawanie postów:

  • Ctrl+Enter - dodaj post
  • ⌘+Enter - dodaj post (MacOS)