2023年6月27日发(作者：)

SQL五⼗道经典题型——（⼀）查询课程编号为“01”的课程⽐“02”的课程成绩⾼的所有学⽣。。。SQL必会50道经典题型创建表学⽣表：Student(s_id,s_name,s_birth,s_sex) –学⽣编号,学⽣姓名, 出⽣年⽉,学⽣性别课程表：Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号教师表：Teacher(t_id,t_name) –教师编号,教师姓名成绩表：Score(s_id,c_id,s_s_score) –学⽣编号,课程编号,分数测试数据--建表--学⽣表CREATE TABLE `Student`(`s_id` VARCHAR(20),`s_name` VARCHAR(20) NOT NULL DEFAULT '',`s_birth` VARCHAR(20) NOT NULL DEFAULT '',`s_sex` VARCHAR(10) NOT NULL DEFAULT '',PRIMARY KEY(`s_id`));--课程表CREATE TABLE `Course`(`c_id` VARCHAR(20),`c_name` VARCHAR(20) NOT NULL DEFAULT '',`t_id` VARCHAR(20) NOT NULL,PRIMARY KEY(`c_id`));--教师表CREATE TABLE `Teacher`(`t_id` VARCHAR(20),`t_name` VARCHAR(20) NOT NULL DEFAULT '',PRIMARY KEY(`t_id`));--成绩表CREATE TABLE `Score`(`s_id` VARCHAR(20),`c_id` VARCHAR(20),`s_score` INT(3),PRIMARY KEY(`s_id`,`c_id`));--插⼊学⽣表测试数据insert into Student values('01' , '赵雷' , '1990-01-01' , '男');insert into Student values('02' , '钱电' , '1990-12-21' , '男');insert into Student values('03' , '孙风' , '1990-05-20' , '男');insert into Student values('04' , '李云' , '1990-08-06' , '男');insert into Student values('05' , '周梅' , '1991-12-01' , '⼥');insert into Student values('06' , '吴兰' , '1992-03-01' , '⼥');insert into Student values('07' , '郑⽵' , '1989-07-01' , '⼥');insert into Student values('08' , '王菊' , '1990-01-20' , '⼥');--课程表测试数据insert into Course values('01' , '语⽂' , '02');insert into Course values('02' , '数学' , '01');insert into Course values('03' , '英语' , '03');--教师表测试数据insert into Teacher values('01' , '张三');insert into Teacher values('02' , '李四');insert into Teacher values('03' , '王五');insert into Teacher values('03' , '王五');--成绩表测试数据insert into Score values('01' , '01' , 80);insert into Score values('01' , '02' , 90);insert into Score values('01' , '03' , 99);insert into Score values('02' , '01' , 70);insert into Score values('02' , '02' , 60);insert into Score values('02' , '03' , 80);insert into Score values('03' , '01' , 80);insert into Score values('03' , '02' , 80);insert into Score values('03' , '03' , 80);insert into Score values('04' , '01' , 50);insert into Score values('04' , '02' , 30);insert into Score values('04' , '03' , 20);insert into Score values('05' , '01' , 76);insert into Score values('05' , '02' , 87);insert into Score values('06' , '01' , 31);insert into Score values('06' , '03' , 34);insert into Score values('07' , '02' , 89);insert into Score values('07' , '03' , 98);第⼀题、 查询课程编号为“01”的课程⽐“02”的课程成绩⾼的所有学⽣的学号（重点）分析题⽬：找到01课分数⼤于02课分数的同学。⾸先找到01课程的分数SELECT s_id, s_score

FROM score

WHERE c_id = '01'其次找到02课程的分数SELECT s_id, s_score

FROM score

WHERE c_id = '02'找到01科⽬分数⽐02科⽬分数⼤的同学SELECT a.s_id AS sid, a.s_score AS 01_score, b.s_score AS 02_score

FROM ( SELECT s_id, c_id, s_score FROM score WHERE c_id = '01' ) AS a, ( SELECT s_id, c_id, s_score FROM score WHERE c_id = '02' ) AS b

WHERE a.s_id = b.s_id #这⼀⾏是为了判断是同⼀个⼈的01科⽬的分数⼤于02科⽬的分数，如果不存在这个判断，就会查询出a同学01的科⽬分数⼤于b同学的02科⽬分数。 AND a.s_score > b.s_score将student表与score表连接起来采⽤内连接的⽅式，内连接就是关联的两张或多张表中，根据关联条件，显⽰所有匹配的记录，匹配不上的，不显⽰。SELECT st.*, a.s_score AS 01_score, b.s_score AS 02_score

FROM student AS st INNER JOIN ( SELECT s_id, s_score FROM score WHERE c_id = '01' ) AS a ON st.s_id = a.s_id INNER JOIN ( SELECT s_id, s_score FROM score WHERE c_id = '02' ) AS b ON st.s_id = b.s_id

WHERE a.s_score > b.s_score;