mysql中的自连接查询

大刘sir 0



一、什么是自连接

自连接查询,顾名思义,就是自己和自己比较。

例如:分数表中,科目1有学生考试不及格,不及格的学生补考了,这个的话,同一个学生,同一个科目出现了2笔成绩,如果查询出成绩高的那一笔记录的话,需要分数表自己和自己比较。

二、数据准备

create database stuMS default charset=’utf8′;

use stuMS;

— 表1:学生信息表 Students

create table Students(

Scode int not null PRIMARY key auto_increment,

SName  char(10) not null,

SAddress  varchar(50) default ‘湖南长沙’,

SGrade  int,

SEmail  varchar(50) CHECK(SEmail like ‘%@%’),

SSex    int CHECK(ssex=0 or ssex=1),

CreateTime datetime default  NOW()

);

— 表2: 科目表 Course

create table Course(

CourseID  int not null primary key,

CourseName   varchar(50) not null

);

— — 表3:成绩表 Score

create table Score(

ScoreID  int not null PRIMARY key,

CourseID  int not null,

Studentid  int not null,

Score   smallint,

CONSTRAINT score_Studentid_fk foreign key(Studentid)

references Students(Scode),

CONSTRAINT score_Courseid_fk foreign key(CourseID)

references Course(CourseID)

);

— 4.  表1学生信息表插入数据

insert into Students(SName,saddress,ssex) VALUES(‘张三’,’北京’,1);

insert into Students(SName,saddress,ssex) VALUES(‘李四’,’上海’,0);

insert into Students(SName,saddress,ssex) VALUES(‘王五’,’广州’,1);

insert into Students(SName,saddress,ssex) VALUES(‘赵六’,’深圳’,0);

insert into Students(SName,saddress,ssex) VALUES(‘田七’,’长沙’,1);

— 5.  表2 学生课程表中输入数据

insert into Course VALUES(1,’语文’);

insert into Course VALUES(2,’数学’);

insert into Course VALUES(3,’英语’);

insert into Course VALUES(4,’测试’);

— 6.  表3 成绩表中插入课程1的成绩,要求有人不及格

INSERT into score VALUES(1,1,1,100);

INSERT into score VALUES(2,1,2,88);

INSERT into score VALUES(3,1,3,50);

INSERT into score VALUES(4,1,4,70);

INSERT into score VALUES(5,1,5,46);

— 7.  表3 成绩表中插入课程1的补考成绩,补考后成绩及格

INSERT into score VALUES(6,1,3,80);

INSERT into score VALUES(7,1,5,82);

三、分步练习

–练习目标: 使用自身查询,查询科目1 的成绩,如果重考,取分数高的那一笔

select  * from score where courseid=1;

— 练习1:找出重考分数高的成绩

select a.*

from score a,score b

where a.studentid=b.studentid

and a.courseid=b.courseid

and a.score > b.score

and a.courseid=1;

— 练习2:找出科目1不及格的分数

select a.*

from score a,score b

where a.studentid=b.studentid

and a.courseid=b.courseid

and a.score < b.score

and a.courseid=1;

— 练习3:找出需要排除的数据的scoreid

select a.scoreid

from score a,score b

where a.courseid=b.courseid

and a.studentid=b.studentid

and a.score<b.score

and a.courseid=1;

–练习4:最后的结果

select *

from score

where scoreid not in   — 排除的数据的不显示,其他数据显示

(select a.scoreid

from score a,score b

where a.courseid=b.courseid

and a.studentid=b.studentid

and a.score<b.score

and a.courseid=1

)and courseid=1;

 

发表评论

邮箱地址不会被公开。 必填项已用*标注