SQL 使用between完成表连接

现在有Teacher表

 

   以及Age_J表

 我们希望使用Teacher中的Age作为判别对象，将Age_LVL左连接到Teacher表中

select t.t_no,t.t_name,t.t_age ,a.age_lvl
from teacher t
left join age_j a on t.t_age between a.min_rng and a.max_rng

这里的Age_lvl表是没有值重叠的，如果我们的Age_lvl表变更一下，包含值重叠的情况

 再次运行sql语句，得到的结果就有差异了

 

 描述完了。把相关测试建表语句的数据贴一下

create table AGE_J
(
  age_lvl VARCHAR2(10),
  min_rng INTEGER,
  max_rng INTEGER
);

create table TEACHER
(
  t_no   NUMBER(5) not null,
  t_name VARCHAR2(8),
  t_age  NUMBER(3)
);


insert into age_j (AGE_LVL, MIN_RNG, MAX_RNG)values ('青年', 18, 29);
insert into age_j (AGE_LVL, MIN_RNG, MAX_RNG)values ('中年', 30, 45);
insert into age_j (AGE_LVL, MIN_RNG, MAX_RNG)values ('壮年', 46, 59);
insert into age_j (AGE_LVL, MIN_RNG, MAX_RNG)values ('老年', 60, 89);
insert into TEACHER (T_NO, T_NAME, T_AGE)values (10002, '李大钊', 24);
insert into TEACHER (T_NO, T_NAME, T_AGE)values (10003, '毕淑敏', 30);
insert into TEACHER (T_NO, T_NAME, T_AGE)values (10004, '朱自清', 59);
insert into TEACHER (T_NO, T_NAME, T_AGE)values (10008, '闻一多', 26);
commit;