查找课程“01“比课程“02“成绩高的学生信息及课程分数

一、问题说明

查找学生同时选择“01”、“02”课程，且课程“01”的成绩大于课程“02”的成绩，输出满足该情况的所有学生信息

二、思路及需输出的字段

1、思路

1）查找同时选择“01”、“02”课程的学生信息

SELECT DISTINCT a.*,a1.*,a2.*
from student a
INNER JOIN ( SELECT * from sc where cid ='01' ) a1 using(sid)
INNER JOIN ( SELECT * from sc where cid ='02' ) a2 using(sid)

2）在第一个查找结果的基础上找出该学生课程“01”的成绩大于课程“02”的成绩信息

WHERE a1.score > a2.score;

2、输出字段

1）学生信息：即学生表内所有信息；课程分数：即成绩表中的分数信息

三、输出效果

四、参考答案

方法一：

写法一：
select DISTINCT a.*,a1.score
from student a,
		 sc a1,
		 sc a2
where a.sid=a1.sid
and a1.sid=a2.sid
and a1.cid='01' 
and a2.cid='02'
and a1.score > a2.score;

写法二：
select DISTINCT a.*, a1.score
from  Student a
INNER JOIN sc a1 USING(sid)
INNER JOIN sc a2 USING(sid)
where a1.cid = '01'
and a2.cid = '02'
and a1.score > a2.score;

方法二：

SELECT DISTINCT a.*,a1.score
from student a
INNER JOIN ( SELECT * from sc where cid ='01' ) a1 using(sid)
INNER JOIN ( SELECT * from sc where cid ='02' ) a2 using(sid)
WHERE a1.score > a2.score;

=========================================================================

丑丑的分割线

=========================================================================

五、表数据创建

#1、学生表

Student(Sid,Sname,Sage,Ssex) --> 学生编号，学生姓名，出生年月，学生性别

CREATE TABLE Student (
  SID VARCHAR (10),
  Same varchar (10),
  Sage datetime,
  Ssex varchar (10)
)
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' , '女');

# 2、成绩表

SC(Sid,Cid,Score) --> 学生编号，课程编号，分数

CREATE TABLE SC (
  SID VARCHAR (10),
  CID VARCHAR (10),
  score DECIMAL (18, 1)
)
INSERT INTO SC VALUES('01' , '01' , 80);
INSERT INTO SC VALUES('01' , '02' , 90);
INSERT INTO SC VALUES('01' , '03' , 99);
INSERT INTO SC VALUES('02' , '01' , 70);
INSERT INTO SC VALUES('02' , '02' , 60);
INSERT INTO SC VALUES('02' , '03' , 80);
INSERT INTO SC VALUES('03' , '01' , 80);
INSERT INTO SC VALUES('03' , '02' , 80);
INSERT INTO SC VALUES('03' , '03' , 80);
INSERT INTO SC VALUES('04' , '01' , 50);
INSERT INTO SC VALUES('04' , '02' , 30);
INSERT INTO SC VALUES('04' , '03' , 20);
INSERT INTO SC VALUES('05' , '01' , 76);
INSERT INTO SC VALUES('05' , '02' , 87);
INSERT INTO SC VALUES('06' , '01' , 31);
INSERT INTO SC VALUES('06' , '03' , 34);
INSERT INTO SC VALUES('07' , '02' , 89);
INSERT INTO SC VALUES('07' , '03' , 98);