Mehrere SQL-Bedingungen in zwei Tabellen geben falsche Daten zurück

Question

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.

Answer 1

如果我没猜错的话

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;