Sql multiple conditions in two tables return wrong data

Question

I have two MySql tables with the following 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)

Now I have to find all (RNO, year, Sem) triples so that the student has roll number RNO in the semester As defined by year, Sem has taken/enrolled in at least three or more different courses, each offered by a different department.

My code is as follows

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;

(I used the Row_Number to apply the Distinct keyword on one of the columns) However, this code will fail if the student is enrolled in more than three courses in two different semesters and the output is 1 row instead of 2 rows. Why does this happen and how to fix it

This is the SQL code for the sample data and schema

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

Here is the link to 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;