Oracle多对多、维表

-- 多对多的秘密：项目中，往往把关系分拆出一张单独表
--维表（学生维度）
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