mysql中的非等值连接查询

大刘sir 0



一、什么是非等值连接

非等值连接其实很简单,就是不是where  A=B形式的都行,比如用between and 或者使用大于小于号

二、实战

表1:学生成绩表Score

create table Score(

ScoreID  int not null PRIMARY key,

CourseID  int not null,

Studentid  int not null,

Score   smallint);

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);

表2:分数等级表levels

CREATE TABLE levels(

levels CHAR(1),

beginscore INT,

endscore INT

);

INSERT INTO levels VALUES(‘A’,90,100);

INSERT INTO levels VALUES(‘B’,80,89);

INSERT INTO levels VALUES(‘C’,60,79);

INSERT INTO levels VALUES(‘D’,0,59);

 

非等值连接查询:根据分数表中分数所在的范围,确定等级

SELECT a.studentid AS 学号,a.score,b.levels AS 等级

FROM SCORE a,levels b

WHERE a.score BETWEEN b.beginscore AND endscore;

 

 

 

发表评论

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