Heim > Fragen und Antworten > Hauptteil
Ich habe zwei MySql-Tabellen mit dem folgenden Schema
Courses(CNO:CHAR(10),CName:VARCHAR(50), DeptId:CHAR(3)): Each course is given a unique ID called CNO Register(RNO:CHAR(10), CNO:CHAR(10), Year:INTEGER, Sem:INTEGER, Grade:CHAR)
Jetzt muss ich alle Tripel (RNO, Jahr, Sem) finden, damit der Student im Semester die Rollennummer RNO hat Je nach Jahr hat Sem mindestens drei oder mehr verschiedene Kurse belegt/eingeschrieben, die jeweils von einer anderen Abteilung angeboten werden.
Mein Code lautet wie folgt
With tmp AS( SELECT Register.RNO,Register.CNO,Register.Year,Register.Sem,courses.DeptId, ROW_NUMBER() OVER (PARTITION BY DeptId) AS rn from Register inner join courses on courses.CNO=Register.CNO ) select RNO, Year, Sem from tmp where rn=1 GROUP BY RNO,Year,Sem having COUNT(CNO)>=3;
(Ich habe Row_Number verwendet, um das Schlüsselwort Distinct auf eine der Spalten anzuwenden) Dieser Code schlägt jedoch fehl, wenn der Student in mehr als drei Kursen in zwei verschiedenen Semestern eingeschrieben ist und die Ausgabe 1 Zeile statt 2 Zeilen ist. Warum passiert das und wie kann man es beheben
Dies ist der SQL-Code für die Beispieldaten und das Beispielschema
CREATE TABLE courses ( CNO CHAR(10), CName VARCHAR(50), DeptId CHAR(3), PRIMARY KEY (CNO) ); INSERT INTO courses (CNO,CName,DeptId) VALUES ("CS1","database","CSE"); INSERT INTO courses (CNO,CName,DeptId) VALUES ("CS2","deep","CSE"); INSERT INTO courses (CNO,CName,DeptId) VALUES ("CS3","prob","CSE"); INSERT INTO courses (CNO,CName,DeptId) VALUES ("MBA4","consume","IME"); INSERT INTO courses (CNO,CName,DeptId) VALUES ("MBA5","Model","IME"); INSERT INTO courses (CNO,CName,DeptId) VALUES ("MBA7","consume","IME"); INSERT INTO courses (CNO,CName,DeptId) VALUES ("MBA8","Model","IME"); INSERT INTO courses (CNO,CName,DeptId) VALUES ("SOC6","Social","HSS"); INSERT INTO courses (CNO,CName,DeptId) VALUES ("MBA10","consume","IME"); INSERT INTO courses (CNO,CName,DeptId) VALUES ("MBA11","Model","IME"); INSERT INTO courses (CNO,CName,DeptId) VALUES ("SOC12","Social","HSS"); INSERT INTO courses (CNO,CName,DeptId) VALUES ("SOC9","Social","HSS"); CREATE TABLE Register ( RNO CHAR(10), CNO CHAR(10), Year INTEGER, Sem INTEGER, Grade CHAR, FOREIGN KEY (CNO) REFERENCES courses(CNO) ); INSERT INTO Register (RNO,CNO,Year,Sem,Grade) VALUES ("1","CS1",1,1,"B"); INSERT INTO Register (RNO,CNO,Year,Sem,Grade) VALUES ("1","CS2",1,1,"A"); INSERT INTO Register (RNO,CNO,Year,Sem,Grade) VALUES ("1","CS3",1,1,"B"); INSERT INTO Register (RNO,CNO,Year,Sem,Grade) VALUES ("1","MBA4",1,1,"B"); INSERT INTO Register (RNO,CNO,Year,Sem,Grade) VALUES ("1","MBA5",1,1,"A"); INSERT INTO Register (RNO,CNO,Year,Sem,Grade) VALUES ("1","SOC6",1,1,"A"); INSERT INTO Register (RNO,CNO,Year,Sem,Grade) VALUES ("1","MBA7",2,2,"B"); INSERT INTO Register (RNO,CNO,Year,Sem,Grade) VALUES ("1","MBA8",2,2,"A"); INSERT INTO Register (RNO,CNO,Year,Sem,Grade) VALUES ("1","SOC9",2,2,"A"); INSERT INTO Register (RNO,CNO,Year,Sem,Grade) VALUES ("1","MBA10",3,3,"B"); INSERT INTO Register (RNO,CNO,Year,Sem,Grade) VALUES ("1","MBA11",4,3,"A"); INSERT INTO Register (RNO,CNO,Year,Sem,Grade) VALUES ("1","SOC12",5,3,"A");
Hier ist der Link zu DB Fiddle.
P粉9377693562024-02-26 10:50:57
如果我没猜错的话
select Register.RNO, Register.Year, Register.Sem from Register inner join courses on courses.CNO=Register.CNO group by RNO,Year,Sem having COUNT(distinct courses.CNO)>=3 and COUNT(distinct courses.DeptId) >=3;