每年的MAXAVG分數-有解無憂

我在 SQL 中的一項練習中遇到了問題。

“每年，找出平均成績最高的學生。”

我需要從我的查詢資訊中獲取 osoba.imie、osoba.nazwisko、YEAR(student.datarekrutacji) AND MAX(AVG(ocena.ocena)) - 所以我需要知道，OCENA 的最大平均值是多少.OCENA 每年，以及誰（OSOBA.IMIE 和 OSOBA.NAZWISKO）獲得了這個最大值。

我嘗試了很多東西，但我能得到的最好的是：

*1 我將 YEARS 與最佳 AVG(OCENA) 分組的情況，這是一個代碼：

SELECT MAX(srednia), YEAR(datarekrutacji) 
FROM student 
INNER JOIN (SELECT idstudent, AVG(ocena) srednia FROM ocena GROUP BY idstudent) X 
ON student.idosoba = X.idstudent 
GROUP BY YEAR(datarekrutacji)

結果_1

雷德尼亞	韓國
4.500000	2011年
4.500000	2012年
4.000000	2013年

*2 情況，我有 IMIE、NAZWISKO 和 YEAR 的所有 AVG(OCENA)，但結果未按 YEARS 分組，因此它們中的每一個都沒有 MAX(AVG(OCENA))，并且代碼：

SELECT ?rednia, imie, nazwisko, YEAR(a.datarekrutacji) "Rocznik rekrutacji"
FROM student a 
INNER JOIN osoba 
ON osoba.idosoba = a.idosoba 
INNER JOIN (SELECT idstudent, AVG(ocena) ?rednia
FROM ocena
GROUP BY idstudent) X
ON X.idstudent = a.idosoba

結果_2：

雷德尼亞	艾米	納茲維斯科	Rocznik rekrutacji
4.333333	艾伯塔省	鳳梨	2011年
4.500000	薩洛米亞	奧利夫卡	2011年
3.666666	粉刺	彭切克	2011年
4.333333	格里塞爾達	格魯什卡	2011年
3.333333	泰莫圖斯	蒂米亞內克	2012年
4.500000	克拉拉	科佩雷克	2012年
2.666666	梅爾基奧爾	瓜	2012年
4.000000	耶羅尼姆	卡普斯塔	2013年
4.000000	布倫希爾達	巴南	2013年
4.000000	所羅門	塞勒	2013年
3.666666	博尼法西	鮑勃	2013年

我應該用子查詢來做這個練習。期望的結果是：

雷德尼亞	艾米	納茲維斯科	Rocznik rekrutacji
4.500000	薩洛米亞	奧利夫卡	2011年
4.500000	克拉拉	科佩雷克	2012年
4.000000	耶羅尼姆	卡普斯塔	2013年
4.000000	布倫希爾達	巴南	2013年
4.000000	所羅門	塞勒	2013年

所以 MAX(AVG(ocena) 在每個韓國，誰得到了那個 MAX(AVG(ocena))。

我沒有更多的想法，所以我需要你的幫助。謝謝：）

@edit：MS SQL Server 版本 - Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64)

資料庫架構：每年的 MAX AVG 分數

創建資料庫的DDL代碼：

-------------------------------------
--SKRYPT ZAK?ADAJ?CY SCHEMAT UCZELNIA
----------Wersja 2021-----------
--DDL MS SQL Server dla schematu UCZELNIA w wersji wyj?ciowej

--Rozpoczynamy tworzenie tabel
/* RokAkademicki.*/
CREATE TABLE RokAkademicki ( 
    IdRokAkademicki char(7) not null,
    Data_rozp date not null,
    Data_zak date not null)  
Go

ALTER TABLE RokAkademicki
    ADD CONSTRAINT RokAkademicki_PK PRIMARY KEY (IdRokAkademicki)   
Go

/* StudentGrupa.*/  
CREATE TABLE StudentGrupa ( 
    IdOsoba int not null,
    IdGrupa int not null)  
Go

ALTER TABLE StudentGrupa
    ADD CONSTRAINT StudentGrupa_PK PRIMARY KEY (IdGrupa, IdOsoba)   
Go

/* Grupa.*/  
CREATE TABLE Grupa (
    IdGrupa Int Identity not null,
    NrGrupy char(10) not null,
    SemestrNauki int not null,
    IdRokAkademicki char(7) not null)  
Go

ALTER TABLE Grupa
    ADD CONSTRAINT Grupa_PK PRIMARY KEY (IdGrupa)   
Go

/* PrzedmiotPoprzedzajacy.*/  
CREATE TABLE PrzedmiotPoprzedzajacy ( 
    IdPoprzednik int not null,
    IdPrzedmiot int not null)  
Go

ALTER TABLE PrzedmiotPoprzedzajacy
    ADD CONSTRAINT PrzedmiotPoprzedzajacy_PK PRIMARY KEY (IdPoprzednik, IdPrzedmiot)   
Go

/* Przedmiot.*/  
CREATE TABLE Przedmiot ( 
    IdPrzedmiot int identity not null,
    Przedmiot varchar(128) not null,
    Symbol char(3) not null
    --,IdKatedra int null
    )  
Go

ALTER TABLE Przedmiot
    ADD CONSTRAINT Przedmiot_PK PRIMARY KEY (IdPrzedmiot)   
Go

/* StopnieTytuly.*/  
CREATE TABLE StopnieTytuly ( 
    IdStopien int identity not null,
    Stopien varchar(32) not null,
    Skrot varchar(10) not null)  
Go

ALTER TABLE StopnieTytuly
    ADD CONSTRAINT StopnieTytuly_PK PRIMARY KEY (IdStopien)   
Go

/* Ocena.*/  
CREATE TABLE Ocena ( 
    IdStudent int not null,
    IdPrzedmiot int not null,
    DataWystawienia date not null,
    IdDydaktyk int not null,
    Ocena decimal(2,1) not null)
Go

ALTER TABLE Ocena
    ADD CONSTRAINT Ocena_PK PRIMARY KEY (IdStudent, DataWystawienia, IdPrzedmiot)   
Go

/* Panstwo.*/  
CREATE TABLE Panstwo ( 
    IdPanstwo int identity not null,
    Panstwo varchar(64) not null)  
Go

ALTER TABLE Panstwo
    ADD CONSTRAINT Panstwo_PK PRIMARY KEY (IdPanstwo)   
Go

/* Osoba.*/  
CREATE TABLE Osoba ( 
    IdOsoba int identity not null,
    Nazwisko varchar(62) not null,
    Imie varchar(32) not null,
    DataUrodzenia date null
    )  
Go

ALTER TABLE Osoba
    ADD CONSTRAINT Osoba_PK PRIMARY KEY (IdOsoba)   
Go

/* Student.*/  
CREATE TABLE Student ( 
    IdOsoba int not null,
    NrIndeksu char(10) not null,
    DataRekrutacji date not null)  
Go

ALTER TABLE Student
    ADD CONSTRAINT Student_PK PRIMARY KEY (IdOsoba)   
Go

/* Dydaktyk.*/  
CREATE TABLE Dydaktyk ( 
    IdOsoba int not null
    ,IdStopien int null
    ,Podlega int null
    )  
Go

ALTER TABLE Dydaktyk
    ADD CONSTRAINT Dydaktyk_PK PRIMARY KEY (IdOsoba)   
Go

/* Add foreign key constraints to table StudentGrupa.*/
ALTER TABLE StudentGrupa
    ADD CONSTRAINT Student_StudentGrupa_FK1 foreign key (IdOsoba)
     references Student (IdOsoba) on update no action on delete no action  
Go

ALTER TABLE StudentGrupa
    ADD CONSTRAINT Grupa_StudentGrupa_FK1 foreign key (IdGrupa)
     references Grupa (IdGrupa) on update no action on delete no action  
Go

/* Add foreign key constraints to table Grupa.*/
ALTER TABLE Grupa
    ADD CONSTRAINT RokAkad_GrupaStud_FK1 foreign key (IdRokAkademicki)
     references RokAkademicki (IdRokAkademicki) on update no action on delete no action  
Go

ALTER TABLE Grupa
    ADD CONSTRAINT UQ_Rok_Nr UNIQUE (NrGrupy, IdRokAkademicki)
Go

/* Add foreign key constraints to table PrzedmiotPoprzedzajacy.*/
ALTER TABLE PrzedmiotPoprzedzajacy
    ADD CONSTRAINT Przedmiot_PrzedmiotPop_FK1 foreign key (IdPoprzednik)
     references Przedmiot (IdPrzedmiot) on update no action on delete no action  
Go

ALTER TABLE PrzedmiotPoprzedzajacy
    ADD CONSTRAINT Przedmiot_PrzedmiotPop_FK2 foreign key (IdPrzedmiot)
     references Przedmiot (IdPrzedmiot) on update no action on delete no action  
Go

/* Add foreign key constraints to table Ocena.*/
ALTER TABLE Ocena
    ADD CONSTRAINT Dydaktyk_Ocena_FK1 foreign key (IdDydaktyk)
     references Dydaktyk (IdOsoba) on update no action on delete no action  
Go

ALTER TABLE Ocena
    ADD CONSTRAINT Student_Ocena_FK1 foreign key (IdStudent)
     references Student (IdOsoba) on update no action on delete no action  
Go

ALTER TABLE Ocena
    ADD CONSTRAINT Przedmiot_Ocena_FK1 foreign key (IdPrzedmiot)
     references Przedmiot (IdPrzedmiot) on update no action on delete no action  
Go

/* Add foreign key constraints to table Student.*/
ALTER TABLE Student
    ADD CONSTRAINT Osoba_Student_FK1 foreign key (IdOsoba)
     references Osoba (IdOsoba) on update no action on delete no action  
Go

/* Add foreign key constraints to table Dydaktyk.*/
ALTER TABLE Dydaktyk
    ADD CONSTRAINT Osoba_Dydaktyk_FK1 foreign key (IdOsoba)
     references Osoba (IdOsoba) on update no action on delete no action  
Go

ALTER TABLE Dydaktyk
    ADD CONSTRAINT StopnieTytuly_Dydaktyk_FK1 foreign key (IdStopien)
     references StopnieTytuly (IdStopien) on update no action on delete no action  
Go

ALTER TABLE Dydaktyk
    ADD CONSTRAINT Dydaktyk_Dydaktyk_FK1 foreign key (Podlega)
     references Dydaktyk (IdOsoba) on update no action on delete no action  
Go

/* This is the end of the Microsoft Visual Studio generated SQL DDL script.*/
-------------------------------------------------------------------------------
INSERT INTO RokAkademicki (IdRokAkademicki, Data_rozp, Data_zak)
VALUES  ('2011_12', '2011-10-01', '2012-08-31'),
        ('2012_13', '2012-10-01', '2013-08-31'),
        ('2013_14', '2013-10-01', '2014-08-31'),
        ('2014_15', '2014-10-01', '2015-08-31');
-------------------------------------------------------------------------------
INSERT INTO Grupa (NrGrupy, SemestrNauki, IdRokAkademicki)
VALUES  ('WIs I.1', 1, '2011_12'),
        ('WIs I.2', 1, '2011_12'),
        ('WIs II.1', 2, '2011_12'),
        ('WIs II.2', 2, '2011_12'),

        ('WIs I.1', 1, '2012_13'),
        ('WIs I.2', 1, '2012_13'),
        ('WIs II.1', 2, '2012_13'),
        ('WIs II.2', 2, '2012_13'),
        ('WIs III.1', 3, '2012_13'),
        ('WIs III.2', 3, '2012_13'),
        ('WIs IV.1', 4, '2012_13'),
        ('WIs IV.2', 4, '2012_13'),

        ('WIs I.1', 1, '2013_14'),
        ('WIs I.2', 1, '2013_14'),
        ('WIs II.1', 2, '2013_14'),
        ('WIs II.2', 2, '2013_14'),
        ('WIs III.1', 3, '2013_14'),
        ('WIs III.2', 3, '2013_14'),
        ('WIs IV.1', 4, '2013_14'),
        ('WIs IV.2', 4, '2013_14'),
        ('WIs V.1', 5, '2013_14'),
        ('WIs VI.1', 6, '2013_14'),

        ('WIs I.1', 1, '2014_15'),
        ('WIs I.2', 1, '2014_15'),
        ('WIs II.1', 2, '2014_15'),
        ('WIs II.2', 2, '2014_15');
-------------------------------------------------------------------------------
SET Identity_insert Przedmiot ON 
Go

INSERT INTO Przedmiot (IdPrzedmiot, Przedmiot, Symbol)
VALUES  (1,'Systemy baz danych', 'SBD'),
        (2,'Relacyjne bazy danych', 'RBD'),
        (3,'Algebra liniowa i geometria', 'ALG'),
        (4,'Matematyka dyskretna', 'MAD'),
        (5,'Systemy operacyjne', 'SOP'),
        (6,'Analiza matematyczna I', 'AM1'),
        (7,'In?ynieria oprogramowania', 'INO'),
        (8,'Projektowanie baz danych', 'BDA'),
        (9,'Administrowanie baz? danych', 'ADM'),
        (10,'Analiza matematyczna II', 'AM2'),
        (11,'Algorytmy i struktury danych', 'ASD'),
        (12,'Administracja systemów operacyjnych', 'ASO');
SET Identity_insert Przedmiot OFF
Go
INSERT INTO PrzedmiotPoprzedzajacy (IdPoprzednik, IdPrzedmiot)
VALUES (2,1),(3,4), (6, 10), (4, 11), (5,12);
go
-------------------------------------------------------------------------------
SET Identity_insert StopnieTytuly ON 
Go

alter table Stopnietytuly alter column skrot varchar(16)
INSERT INTO StopnieTytuly (IdStopien, Skrot, Stopien)
VALUES  (1, 'Prof. Dr hab.', 'Profesor Doktor habilitowany')
        ,(2, 'Dr hab.', 'Doktor habilitowany')
        ,(3, 'Dr', 'Doktor')
        ,(4, 'Mgr', 'Magister')
        ,(5, 'In?', 'In?ynier');

SET Identity_insert StopnieTytuly OFF
Go
-------------------------------------------------------------------------------
SET Identity_insert Panstwo ON 
Go

INSERT INTO Panstwo (IdPanstwo, Panstwo)
VALUES (14, 'Bia?oru?'),
        (4, 'Czechy'),
        (15, 'Francja'),
        (10, 'Niemcy'),
        (3, 'Polska'),
        (12, 'Rosja'),
        (18, 'Rumunia'),
        (11, 'S?owacja'),
        (16, 'S?owenia'),
        (13, 'Ukraina'),
        (2, 'USA'),
        (19,'Hiszpania'),
        (23,'Turcja'),
        (21,'Finlandia'),
        (22,'Wlochy'),
        (24, 'Grecja');

SET Identity_insert Panstwo OFF
Go

--Dydaktycy------------------------
SET Identity_insert osoba ON 
Go

INSERT INTO osoba
(IdOsoba,
 Imie,
 Nazwisko,
 DataUrodzenia
)
VALUES
(1,
 'Apolinary',
 'Any?ek',
'1960-12-01'
);
Go
INSERT  INTO Dydaktyk (IdOsoba, IdStopien)
VALUES  (1 ,1);
Go
-----------------------------------------------------------------------------------------------
INSERT INTO osoba
(IdOsoba,
 Imie,
 Nazwisko,
 DataUrodzenia
)
VALUES (2, 'Balbina', 'Bak?a?an', '1991-02-03');
go
INSERT  INTO Dydaktyk (IdOsoba, IdStopien)
VALUES  (2,4);
go
-----------------------------------------------------------------------------------------------
INSERT INTO osoba
(IdOsoba,
 Imie,
 Nazwisko,
 DataUrodzenia
)
VALUES (3, 'Baltazar', 'Bigos','1995-09-04');
go
INSERT  INTO Dydaktyk (IdOsoba, IdStopien)
VALUES  (3,5);
go

-----------------------------------------------------------------------------------------------
INSERT INTO osoba
(IdOsoba,
 Imie,
 Nazwisko,
 DataUrodzenia
)
VALUES (4, 'Cezary', 'Czosnek', '1958-11-11');
go
INSERT  INTO Dydaktyk (IdOsoba, IdStopien)
VALUES  (4, 1);
go
-----------------------------------------------------------------------------------------------
INSERT INTO osoba
(IdOsoba,
 Imie,
 Nazwisko,
 DataUrodzenia
)
VALUES (5, 'Domicella', 'Dynia', '1982-06-30');
go
INSERT  INTO Dydaktyk (IdOsoba, IdStopien)
VALUES  (5 ,3);

-----------------------------------------------------------------------------------------------
INSERT INTO osoba
(IdOsoba,
 Imie,
 Nazwisko,
 DataUrodzenia
)
VALUES (6, 'Bazyli', 'Broku?', '1971-03-08');
go
INSERT  INTO Dydaktyk (IdOsoba, IdStopien)
VALUES  (6,2);
go
-----------------------------------------------------------------------------------------------
INSERT INTO osoba
(IdOsoba,
 Imie,
 Nazwisko,
 DataUrodzenia
)
VALUES (7, 'Kajetan', 'Kalafior', '1989-05-03');
go
INSERT  INTO Dydaktyk (IdOsoba, IdStopien)
VALUES  (7,4);

-----------------------------------------------------------------------------------------------
INSERT INTO osoba
(IdOsoba,
 Imie,
 Nazwisko,
 DataUrodzenia
)
VALUES (8, 'Kunegunda', 'Karp', '1995-10-21');
go
INSERT  INTO Dydaktyk (IdOsoba, IdStopien)
VALUES  (8,5);
go
-----------------------------------------------------------------------------------------------
INSERT INTO osoba
(IdOsoba,
 Imie,
 Nazwisko,
 DataUrodzenia
)
VALUES (9, 'January', 'Jajecznica', '1965-05-22');
go
INSERT  INTO Dydaktyk (IdOsoba, IdStopien)
VALUES  (9,2);
go
-----------------------------------------------------------------------------------------------
INSERT INTO osoba
(IdOsoba,
 Imie,
 Nazwisko,
 DataUrodzenia
)
VALUES (10, 'Archibald', 'Agrest', '1978-09-05');
go  
INSERT  INTO Dydaktyk (IdOsoba, IdStopien)
VALUES  (10,3);
go
-----------------------------------------------------------------------------------------------
INSERT INTO osoba
(IdOsoba,
 Imie,
 Nazwisko,
 DataUrodzenia
)
VALUES (11, 'Kleofas', 'Klops', '1977-11-11');
go
INSERT  INTO Dydaktyk (IdOsoba, IdStopien)
VALUES  (11, 4);
go
-----------------------------------------------------------------------------------------------
INSERT INTO osoba
(IdOsoba,
 Imie,
 Nazwisko
)
VALUES (23, 'Winicjusz', 'W??ymord');
go
INSERT  INTO Dydaktyk (IdOsoba, IdStopien)
VALUES  (23, NULL);
go
-----------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
UPDATE Dydaktyk SET Podlega = 1 WHERE IdOsoba IN (6,10,2,3);
UPDATE Dydaktyk SET Podlega = 4 WHERE IdOsoba IN (5, 9)
UPDATE Dydaktyk SET Podlega = 9 WHERE IdOsoba IN (7, 11);
UPDATE Dydaktyk SET Podlega = 5 WHERE IdOsoba IN (8, 23);
-----------------------------------------------------------------------------------------------
--Studenci
-----------------------------------------------------------------------------------------------
INSERT  INTO osoba (IdOsoba, Imie, Nazwisko, DataUrodzenia) VALUES (12,'Alberta', 'Ananas', '1991-03-05');
go
INSERT  INTO Student (IdOsoba, DataRekrutacji, NrIndeksu)   
VALUES  (12, '2011-09-12','s2121');
INSERT  INTO Dydaktyk (IdOsoba, IdStopien)
VALUES  (12, 5);
go
-----------------------------------------------------------------------------------------------
INSERT  INTO osoba (IdOsoba, Imie, Nazwisko, DataUrodzenia) VALUES (13, 'Salomea', '?liwka', '1992-05-15');
go
INSERT  INTO Student (IdOsoba, DataRekrutacji, NrIndeksu)
VALUES  (13, '2011-09-13','s2126');
go
-----------------------------------------------------------------------------------------------
INSERT  INTO osoba (IdOsoba, Imie, Nazwisko, DataUrodzenia) VALUES (14, 'Pulchernia', 'P?czek', '1993-08-14');
go
INSERT  INTO Student (IdOsoba, DataRekrutacji, NrIndeksu)
VALUES  (14, '2011-08-19','s2101');
go
-----------------------------------------------------------------------------------------------
INSERT  INTO osoba (IdOsoba, Imie, Nazwisko, DataUrodzenia) VALUES (15, 'Gryzelda', 'Gruszka', '1990-12-24');
go
INSERT  INTO Student (IdOsoba, DataRekrutacji, NrIndeksu)
VALUES  (15, '2011-10-01','s2135');
go
-----------------------------------------------------------------------------------------------
INSERT  INTO osoba (IdOsoba, Imie, Nazwisko, DataUrodzenia) VALUES (16, 'Tymoteusz', 'Tymianek', '1993-11-21');
go
INSERT  INTO Student (IdOsoba, DataRekrutacji, NrIndeksu)
VALUES  (16, '2012-08-12','s3162');
go
-----------------------------------------------------------------------------------------------
INSERT  INTO osoba (IdOsoba, Imie, Nazwisko, DataUrodzenia) VALUES (17, 'Klara', 'Koperek', '1994-03-22');
go
INSERT  INTO Student (IdOsoba, DataRekrutacji, NrIndeksu)
VALUES  (17, '2012-09-23','s3177');
go
-----------------------------------------------------------------------------------------------
INSERT  INTO osoba (IdOsoba, Imie, Nazwisko, DataUrodzenia) VALUES (18, 'Melchior', 'Melon', '1995-08-09');
go
INSERT  INTO Student (IdOsoba, DataRekrutacji, NrIndeksu)
VALUES  (18, '2012-07-22','s3045');
go
-----------------------------------------------------------------------------------------------
INSERT  INTO osoba (IdOsoba, Imie, Nazwisko, DataUrodzenia) VALUES (19, 'Hieronim', 'Kapusta', '1994-08-09');
go
INSERT  INTO Student (IdOsoba, DataRekrutacji, NrIndeksu)
VALUES  (19, '2013-08-05','s4120');
go
-----------------------------------------------------------------------------------------------
INSERT  INTO osoba (IdOsoba, Imie, Nazwisko, DataUrodzenia) VALUES (20, 'Brunchilda', 'Banan', '1995-07-07');
go
INSERT  INTO Student (IdOsoba, DataRekrutacji, NrIndeksu)
VALUES  (20, '2013-07-16','s4022');
go
-----------------------------------------------------------------------------------------------
INSERT  INTO osoba (IdOsoba, Imie, Nazwisko, DataUrodzenia) VALUES (21, 'Salomon', 'Seler', '1994-11-05');
go
INSERT  INTO Student (IdOsoba, DataRekrutacji, NrIndeksu)
VALUES  (21, '2013-07-06','s4004');
go
-----------------------------------------------------------------------------------------------
INSERT  INTO osoba (IdOsoba, Imie, Nazwisko, DataUrodzenia) VALUES (22, 'Bonifacy', 'Bób', '1996-03-09');
go
INSERT  INTO Student (IdOsoba, DataRekrutacji, NrIndeksu)
VALUES  (22, '2013-09-22','s4321');
go
-----------------------------------------------------------------------------------------------
INSERT  INTO osoba (IdOsoba, Imie, Nazwisko, DataUrodzenia) VALUES (24, 'Pafnucy', 'Papryka', '1997-02-19');
go
INSERT  INTO Student (IdOsoba, DataRekrutacji, NrIndeksu)
VALUES  (24, '2013-09-22','s4322');
go
-----------------------------------------------------------------------------------------------
INSERT  INTO osoba (IdOsoba, Imie, Nazwisko, DataUrodzenia) VALUES (25, 'Pankracy', 'Por', '1995-07-09');
go
INSERT  INTO Student (IdOsoba, DataRekrutacji, NrIndeksu)
VALUES  (25, '2013-09-22','s4323');
go
-----------------------------------------------------------------------------------------------
INSERT INTO Osoba (IdOsoba, Imie, Nazwisko, DataUrodzenia)
VALUES (26, 'Cecylia', 'Cebula', '1997-12-02');
Go
INSERT INTO Student (Idosoba, NrIndeksu, DataRekrutacji)
VALUES (26, 5122, '2014-06-11');
Go
-----------------------------------------------------------------------------------------------
INSERT INTO Osoba (IdOsoba, Imie, Nazwisko, DataUrodzenia)
VALUES (27, 'Dezydery', 'D?b', '1998-01-22');
Go
INSERT INTO Student (Idosoba, NrIndeksu, DataRekrutacji)
VALUES (27, 5131, '2014-07-17');
Go
-----------------------------------------------------------------------------------------------
INSERT INTO Osoba (IdOsoba, Imie, Nazwisko, DataUrodzenia)
VALUES (28, 'Konstancja', 'Koperek', '1996-02-02');
Go
INSERT INTO Student (Idosoba, NrIndeksu, DataRekrutacji)
VALUES (28, 5138, '2014-07-22');
Go
-----------------------------------------------------------------------------------------------
INSERT INTO Osoba (IdOsoba, Imie, Nazwisko, DataUrodzenia)
VALUES (29, 'Judyta', 'Jarmu?', '1997-08-28');
Go
INSERT INTO Student (Idosoba, NrIndeksu, DataRekrutacji)
VALUES (29, 5141, '2014-08-12');
Go
-----------------------------------------------------------------------------------------------
INSERT INTO Osoba (IdOsoba, Imie, Nazwisko, DataUrodzenia)
VALUES (30, 'Klaudiusz', 'Karczoch', '1996-09-06');
Go
INSERT INTO Student (Idosoba, NrIndeksu, DataRekrutacji)
VALUES (30, 5144, '2014-08-22');
Go
-----------------------------------------------------------------------------------------------
INSERT INTO Osoba (IdOsoba, Imie, Nazwisko, DataUrodzenia)
VALUES (31, 'Sykstus', 'Szczaw', '1997-10-05');
Go
INSERT INTO Student (Idosoba, NrIndeksu, DataRekrutacji)
VALUES (31, 5149, '2014-09-01');
Go
SET Identity_insert osoba OFF
Go
-----------------------------------------------------------------------------------------------
-----------------Studenci w grupach----------------------------------------------------------
-----------------------------------------------------------------------------------------------
INSERT INTO StudentGrupa (IdOsoba, IdGrupa)
VALUES (12, 1), (12, 3), (12,9), (12, 11), (12,21), (12, 22), (13, 1), (13, 3), (13,9), (13, 11), (13,21), (13, 22),
        (14, 1), (14, 3), (14,9), (14, 11), (14,21), (14, 22), (15, 1), (15, 3), (15,9), (15, 11), (15,21), (15, 22),
        (16, 5), (17, 5), (18, 5), (16, 7), (17, 7), (18, 7),(16, 17), (17, 17), (18, 17), (16, 19), (17, 19), (18, 19),
        (19,13), (20,13), (21,13), (22, 14), (24, 14), (25,14), (19,15), (20, 15), (21,15), (22, 16), (24, 16), (25, 16), 
        (26, 23), (27,23), (28, 23), (29, 24), (30, 24),(31, 24), (26, 25), (27, 25), (28, 25), (29, 26),(30, 26), (31, 26);
-----------------------------------------------------------------------------------------------
-----------------Oceny-------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
INSERT  INTO Ocena (IdStudent, IdPrzedmiot, DataWystawienia, Ocena, IdDydaktyk)
VALUES (12, 6, '2012-01-20', 4.0, 1),
        (13, 6, '2012-01-20', 4.5, 1),
        (14, 6, '2012-01-20', 3.0, 1),
        (15, 6, '2012-01-20', 5.0, 1),

        (16, 6, '2013-01-25', 2.0, 1),
        (17, 6, '2013-01-25', 4.5, 1),
        (18, 6, '2013-01-25', 3.0, 1),
        (16, 6, '2013-02-02', 3.0, 1),

        (19, 6, '2014-01-18', 5.0, 1),
        (20, 6, '2014-01-18', 4.0, 1),
        (21, 6, '2014-01-18', 4.5, 1),
        (22, 6, '2014-01-18', 2.0, 1),
        (22, 6, '2014-01-30', 4.0, 1),

        (12, 2, '2012-01-22', 5.0, 9),
        (13, 2, '2012-01-22', 4.5, 9),
        (14, 2, '2012-01-22', 4.0, 9),
        (15, 2, '2012-01-22', 5.0, 9),

        (16, 2, '2013-01-23', 5.0, 9),
        (17, 2, '2013-01-23', 4.5, 9),
        (18, 2, '2013-01-23', 2.0, 9),
        (18, 2, '2013-02-01', 3.0, 9),

        (19, 2, '2014-01-18', 3.0, 9),
        (20, 2, '2014-01-18', 4.0, 9),
        (21, 2, '2014-01-18', 3.5, 9),
        (22, 2, '2014-01-18', 5.0, 9),

        (12, 12, '2014-01-18', 4.0, 8),
        (13, 12, '2014-01-18', 4.5, 8),
        (14, 12, '2014-01-18', 4.0, 8),
        (15, 12, '2014-01-18', 3.0, 8);

uj5u.com熱心網友回復：

ocena 表顯示了等級。它包含學生 ID 和日期。您想找到每個學生和年份的平均成績。這是：

SELECT
  YEAR(datawystawienia) AS year,
  idstudent,
  AVG(ocena) AS avg_grade
FROM ocena
GROUP BY YEAR(datawystawienia), idstudent;

您可以使用它來獲得每年最優秀的學生。最簡單的方法可能是使用視窗函式（MAX OVER在下面的查詢中）：

SELECT year, idstudent, avg_grade
FROM
(
  SELECT
    YEAR(datawystawienia) AS year,
    idstudent,
    AVG(ocena) AS avg_grade,
    MAX(AVG(ocena)) OVER (PARTITION BY YEAR(datawystawienia)) AS max_avg_grade
  FROM ocena
  GROUP BY YEAR(datawystawienia), idstudent
) aggregated
WHERE avg_grade = max_avg_grade
ORDER BY year, idstudent;

您當然可以將學生資料 (osoba) 加入到這個結果中。

uj5u.com熱心網友回復：

感謝 Thorsten Kettner 提供的解決方案。我忘了提到，我需要按 student.datarekrutacji 的年份而不是 ocena.datawystawienia 的年份對結果進行分組，但您的帖子確實幫助了我。我更改了代碼以符合我的要求，最后看起來像這樣：

SELECT year, imie, nazwisko, avg_grade
FROM (SELECT YEAR(datarekrutacji) AS year, idstudent, AVG(ocena) AS avg_grade,
        MAX(AVG(ocena)) OVER (PARTITION BY YEAR(datarekrutacji)) AS max_avg_grade
        FROM ocena
        INNER JOIN student
        ON ocena.idstudent = student.idosoba
        GROUP BY YEAR(datarekrutacji), idstudent) aggregated,
        osoba, student
WHERE avg_grade = max_avg_grade
AND osoba.idosoba = student.idosoba
AND aggregated.idstudent = student.idosoba;

結果是：

年	艾米	納茲維斯科	平均等級
2011年	薩洛米亞	奧利夫卡	4.500000
2012年	克拉拉	科佩雷克	4.500000
2013年	耶羅尼姆	卡普斯塔	4.000000
2013年	布倫希爾達	巴南	4.000000
2013年	所羅門	塞勒	4.000000

但是在更改您的代碼期間，我有了一個想法并嘗試連接我之前的 2 個代碼。最后我創造了那個怪物 :D （我不知道它是否全部正確，但結果是）：

SELECT YEAR(datarekrutacji) rok, imie, nazwisko, AVG(ocena) ocena
FROM student
INNER JOIN osoba
ON student.idosoba = osoba.idosoba
INNER JOIN ocena
ON ocena.idstudent = osoba.idosoba

GROUP BY YEAR(datarekrutacji), imie, nazwisko

HAVING YEAR(datarekrutacji) IN (SELECT YEAR(datarekrutacji)
                                FROM student 
                                INNER JOIN (SELECT idstudent, AVG(ocena) srednia FROM ocena GROUP BY idstudent) X 
                                ON student.idosoba = X.idstudent 
                                GROUP BY YEAR(datarekrutacji))

AND AVG(ocena) IN               (SELECT MAX(srednia)
                                FROM student 
                                INNER JOIN (SELECT idstudent, AVG(ocena) srednia FROM ocena GROUP BY idstudent) X 
                                ON student.idosoba = X.idstudent 
                                GROUP BY YEAR(datarekrutacji))

查詢結果：

韓國	艾米	納茲維斯科	奧塞納
2011年	薩洛米亞	奧利夫卡	4.500000
2012年	克拉拉	科佩雷克	4.500000
2013年	布倫希爾達	巴南	4.000000
2013年	耶羅尼姆	卡普斯塔	4.000000
2013年	所羅門	塞勒	4.000000

轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/402673.html

標籤：

上一篇：使用python自動備份和洗掉Microsoftsql資料庫

下一篇：從多行字串中提取單行值T-SQL