我有兩個具有以下架構的 MySql 表
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)
現在我必須找到所有（RNO，Year，Sem）三元組，以便在由 Year, Sem 定義的學期中具有 RNO 的學生已經參加/注冊了至少三個或更多不同的課程，每個課程由不同的部門提供.
我的代碼如下
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;
（我使用Row_Number在其中一列上應用Distinct關鍵字）但是，如果學生在兩個不同的學期注冊了三門以上的課程并且輸出是 1 行而不是 2 行，則此代碼將失敗. 為什么會發生以及如何解決它
這是示例資料和模式的 sql 代碼
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");
這是一個 DB Fiddle 的鏈接。
uj5u.com熱心網友回復：
如果我做對了
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;
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/422482.html
標籤：
上一篇：NetCore2.1無法啟動，因為缺少Microsoft.AspNetCore.Mvc.Razor.RuntimeCompilation？