-- 多对多的秘密:项目中,往往把关系分拆出一张单独表
--维表(学生维度)
create table t_stu(
id number primary key,
user_name varchar2(10)
);
insert into t_stu values(1,'小军');
insert into t_stu values(2,'小黄');
commit
select * from t_stu
drop table t_ke
--维表(课程维度)
create table t_ke(
id number primary key,
ke_name varchar2(100)
);
insert into t_ke values(1,'高等数学');
insert into t_ke values(2,'离散数学');
insert into t_ke values(3,'牛顿法');
insert into t_ke values(4,'大学英语');
insert into t_ke values(5,'马克思主义');
insert into t_ke values(6,'江泽明思想');
insert into t_ke values(7,'邓小平理论');
commit
select * from t_ke
-- 事实表
CREATE TABLE t_stu_ke(
id NUMBER PRIMARY KEY,
user_id NUMBER(5),
ke_id NUMBER(5)
);
delete from t_stu_ke
insert into t_stu_ke values(1,1,1);
insert into t_stu_ke values(2,1,2);
insert into t_stu_ke values(3,1,6);
commit;
insert into t_stu_ke values(4,2,4);
insert into t_stu_ke values(5,2,6);
commit;
select sk.id,s.user_name,k.ke_name from t_stu_ke sk
left join t_stu s on sk.user_id =s.id
left join t_ke k on sk.ke_id=k.id
insert into t_stu_ke values(6,3,3);
insert into t_stu_ke values(7,2,8);
commit
select
sk.id,
nvl(s.user_name,'未知学生:'||sk.user_id) 学生,
nvl(k.ke_name,'未知课程:'||sk.ke_id) 选课
from t_stu_ke sk
left join t_stu s on sk.user_id =s.id
left join t_ke k on sk.ke_id=k.id
-- 站在不通的维度看世界
group by