sql - Oracle SQL : Finding common subjects taken by students in each class

Question

下面是学生表 DDL 和 DML

CREATE TABLE STUDENTS
(
  CLASS NUMBER,
  STUDENT_ROLLNO  NUMBER,
  SUBJECT VARCHAR2(50)
);

INSERT INTO STUDENTS(CLASS, STUDENT_ROLLNO, SUBJECT) VALUES(1, 1,'ENGLISH');
INSERT INTO STUDENTS(CLASS, STUDENT_ROLLNO, SUBJECT) VALUES(1, 1,  'MATHS');
INSERT INTO STUDENTS(CLASS, STUDENT_ROLLNO, SUBJECT) VALUES(1, 1,  'SCIENCE');
INSERT INTO STUDENTS(CLASS, STUDENT_ROLLNO, SUBJECT) VALUES(1, 1,  'ART');
INSERT INTO STUDENTS(CLASS, STUDENT_ROLLNO, SUBJECT) VALUES(1, 2,  'ENGLISH');
INSERT INTO STUDENTS(CLASS, STUDENT_ROLLNO, SUBJECT) VALUES(1, 2,  'MATHS');
INSERT INTO STUDENTS(CLASS, STUDENT_ROLLNO, SUBJECT) VALUES(1, 2,  'FRENCH');
INSERT INTO STUDENTS(CLASS, STUDENT_ROLLNO, SUBJECT) VALUES(1, 2,  'POETRY');
INSERT INTO STUDENTS(CLASS, STUDENT_ROLLNO, SUBJECT) VALUES(1, 3,  'ENGLISH');
INSERT INTO STUDENTS(CLASS, STUDENT_ROLLNO, SUBJECT) VALUES(1, 3,  'MATHS');

现在我需要找到该类(class)每个学生选择的共同科目。
对于这种情况，结果是 ENGLISH 和 MATHS。

我需要编写查询来获取所需的结果，因为它们是更多类，结果应该是每个类。

谢谢。

Answer 1

尝试这个

 WITH SUB1 AS (
SELECT  
CLASS,SUBJECT,COUNT(*) NMB  FROM
STUDENTS
GROUP BY CLASS,SUBJECT
),SUB2 AS(
SELECT CLASS, MAX(NMB)MAX_NMB FROM
(SELECT  
CLASS,COUNT(*) NMB  FROM
STUDENTS
GROUP BY CLASS,SUBJECT
)
GROUP BY CLASS
)
SELECT SUB1.CLASS,SUB1.SUBJECT
FROM SUB1
INNER JOIN SUB2
ON SUB1.NMB=SUB2.MAX_NMB  AND SUB1.CLASS=SUB2.CLASS