Sql berbilang syarat dalam dua jadual mengembalikan data yang salah

Question

Saya mempunyai dua jadual MySql dengan skema berikut

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)

Sekarang saya perlu mencari semua (RNO, tahun, Sem) tiga kali ganda supaya pelajar mempunyai nombor daftar RNO pada semester Seperti yang ditakrifkan mengikut tahun, Sem telah mengambil/mendaftar dalam sekurang-kurangnya tiga atau lebih kursus yang berbeza, setiap satu ditawarkan oleh jabatan yang berbeza.

Kod saya adalah seperti berikut

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;

(Saya menggunakan Row_Number untuk menggunakan kata kunci Distinct pada salah satu lajur) Walau bagaimanapun, kod ini akan gagal jika pelajar mendaftar dalam lebih daripada tiga kursus dalam dua semester berbeza dan output adalah 1 baris dan bukannya 2 baris. Mengapa ini berlaku dan bagaimana untuk memperbaikinya

Ini ialah kod SQL untuk data sampel dan skema

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");

Berikut ialah pautan ke DB Fiddle.

Answer 1

Jika saya teka dengan betul

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;