Sql berbilang syarat dalam dua jadual mengembalikan data yang salah
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.
