题目给了四个表分别是Student,Teacher,Course,Sc
#学生表
CREATE TABLE Student(
id varchar(10),
Sname nvarchar(10),
Sage datetime,
Ssex nvarchar(10)
);
#教师表
CREATE TABLE Teacher(
id varchar(10),
Tname nvarchar(10)
);
#课程表(id 学科编,Cname 课程名称,tid 教师编号)
CREATE TABLE Course(
id varchar(10),
Cname nvarchar(10),
tid varchar(10)
);
#学生成绩表(cid 课程编号)
CREATE TABLE SC(
id varchar(10),
cid varchar(10),
score decimal(18,1)
);
然后分别插入数据:
INSERT INTO Student VALUES
('01','赵雷','1990-01-01','男'),
('02','钱电','1990-12-21','男'),
('03','孙风','1990-05-20','男'),
('04','李云','1990-08-06','男'),
('05','周梅','1991-12-01','女'),
('06','吴兰','1992-03-01','女'),
('07','郑竹','1989-07-01','女'),
('08','王菊','1990-01-20','女');
INSERT INTO Teacher VALUES
('01','张三'),
('02','李四'),
('03','王五');
INSERT INTO Course VALUES
('01','语文','02'),
('02','数学','01'),
('03','英语','03');
#学生成绩表
INSERT INTO SC VALUES
('01','01','80'),
('01','02','90'),
('01','03','99'),
('02','01','70'),
('02','02','60'),
('02','03','80'),
('03','01','80'),
('03','02','80'),
('03','03','80'),
('04','01','50'),
('04','02','30'),
('04','03','20'),
('05','01','76'),
('05','02','87'),
('06','01','31'),
('06','03','34'),
('07','02','89'),
('07','03','98');
题目要求:查询每个教师带过那些学生
错误写法:
SELECT t.Tname '教师',s.Sname '学生' FROM Teacher t, Student s, SC sc,Course c WHERE s.id=sc.id AND t.id=c.tid AND c.id=sc.cid;
#其查询结果中,学生吴兰对应的教师是李四和王五,但是实际上吴兰同学应该对应的是张三和王五两个教师
正确写法:
SELECT t.Tname '教师',s.Sname '学生' FROM Teacher t, Student s, SC sc,Course c WHERE s.id=sc.id AND t.id=c.tid AND t.id=sc.cid;
所以上边两条语句有什么不同,求解答,谢谢!!