2. 技术
    3. 查询和“s002”号的同学学习的课程完全相同的其他同学的个人信息

    查询和“s002”号的同学学习的课程完全相同的其他同学的个人信息

    技术2023-09-12  87

    相关表创建以及初始化

    学生成绩表

      create table SC (   SNO   VARCHAR2(10) not null,   CNO   VARCHAR2(10) not null,   SCORE NUMBER(4,2) )

    学生基本信息表

    create table STUDENT (   SNO   VARCHAR2(10) not null,   SNAME VARCHAR2(20),   SAGE  NUMBER(2),   SSEX  VARCHAR2(5) )

    插入案例数据

    插入sc：

    insert into sc (SNO, CNO, SCORE) values ('s001', 'c001', 78.90);

    insert into sc (SNO, CNO, SCORE) values ('s002', 'c001', 80.90);

    insert into sc (SNO, CNO, SCORE) values ('s003', 'c001', 81.90);

    insert into sc (SNO, CNO, SCORE) values ('s004', 'c001', 60.90);

    insert into sc (SNO, CNO, SCORE) values ('s001', 'c002', 82.90);

    insert into sc (SNO, CNO, SCORE) values ('s002', 'c002', 72.90);

    insert into sc (SNO, CNO, SCORE) values ('s005', 'c003', 78.90);

    insert into sc (SNO, CNO, SCORE) values ('s006', 'c004', 50.90);

    insert into sc (SNO, CNO, SCORE) values ('s007', 'c005', 81.90);

    insert into sc (SNO, CNO, SCORE) values ('s008', 'c006', 50.90);

    insert into sc (SNO, CNO, SCORE) values ('s005', 'c007', 42.90);

    insert into sc (SNO, CNO, SCORE) values ('s006', 'c008', 72.90);

    insert into sc (SNO, CNO, SCORE) values ('s005', 'c009', 52.90);

    insert into sc (SNO, CNO, SCORE) values ('s006', 'c010', 92.90);

    插入student：

    insert into student (SNO, SNAME, SAGE, SSEX) values ('s001', '张三', 23, '男');

    insert into student (SNO, SNAME, SAGE, SSEX) values ('s002', '李四', 23, '男');

    insert into student (SNO, SNAME, SAGE, SSEX) values ('s003', '吴鹏', 25, '男');

    insert into student (SNO, SNAME, SAGE, SSEX) values ('s004', '琴沁', 20, '女');

    insert into student (SNO, SNAME, SAGE, SSEX) values ('s005', '王丽', 20, '女');

    insert into student (SNO, SNAME, SAGE, SSEX) values ('s006', '李波', 21, '男');

    insert into student (SNO, SNAME, SAGE, SSEX) values ('s007', '刘玉', 21, '男');

    insert into student (SNO, SNAME, SAGE, SSEX) values ('s008', '萧蓉', 21, '女');

    insert into student (SNO, SNAME, SAGE, SSEX) values ('s009', '陈萧晓', 23, '女');

    insert into student (SNO, SNAME, SAGE, SSEX) values ('s010', '陈美', 22, '女');

    红色标注sql有变动的部分

    第一步：找到所有和s002任意课程相同的同学

    SELECT *   FROM SC S1   LEFT JOIN SC S2 ON S1.CNO = S2.CNO  WHERE S1.SNO <> 's002'    AND S2.SNO = 's002';

    得到结果：

    第二步 按第一步得到的结果 按sno 进行分组

    SELECT SNO, COUNT(*)   FROM (SELECT S1.SNO           FROM SC S1           JOIN SC S2 ON S1.CNO = S2.CNO          WHERE S1.SNO <> 's002'            AND S2.SNO = 's002')  GROUP BY SNO;

    第三步 对结果进行筛选，选出相同课程数和s002一样的同学

    SELECT SNO, COUNT(*)   FROM (SELECT S1.SNO           FROM SC S1           JOIN SC S2 ON S1.CNO = S2.CNO          WHERE S1.SNO <> 's002'            AND S2.SNO = 's002')  GROUP BY SNOHAVING COUNT(*) = (SELECT COUNT(*) FROM SC WHERE SNO = 's002');

    第四步 将A学了s002所有的课程，还学了其他的课程这种情况排除

    SELECT SNO, COUNT(*)   FROM SC  GROUP BY SNO INTERSECT (SELECT SNO, COUNT(*)              FROM (SELECT S1.SNO                      FROM SC S1                      JOIN SC S2 ON S1.CNO = S2.CNO                     WHERE S1.SNO <> 's002'                       AND S2.SNO = 's002')             GROUP BY SNO            HAVING COUNT(*) = (SELECT COUNT(*) FROM SC WHERE SNO = 's002'));

    第五步 提取出学号

    SELECT SNO   FROM （SELECT SNO, COUNT(*)   FROM SC  GROUP BY SNO INTERSECT (SELECT SNO, COUNT(*)              FROM (SELECT S1.SNO                      FROM SC S1                      JOIN SC S2 ON S1.CNO = S2.CNO                     WHERE S1.SNO <> 's002'                       AND S2.SNO = 's002')             GROUP BY SNO            HAVING COUNT(*) = (SELECT COUNT(*) FROM SC WHERE SNO = 's002')));

    第六步 得到结果

    SELECT * FROM student WHERE sno IN    (SELECT SNO           FROM (SELECT SNO, COUNT(*)                   FROM SC                  GROUP BY SNO                 INTERSECT (SELECT SNO, COUNT(*)                             FROM (SELECT S1.SNO                                     FROM SC S1                                     JOIN SC S2 ON S1.CNO = S2.CNO                                    WHERE S1.SNO <> 's002'                                      AND S2.SNO = 's002')                            GROUP BY SNO                           HAVING COUNT(*) = (SELECT COUNT(*)                                               FROM SC                                              WHERE SNO = 's002'))));

                                                                                                                 

     

     

     

     

                                                                                                              学习自https://www.cnblogs.com/yin-tao/p/7880741.html

    Processed: 0.009, SQL: 10