-- 多对多的秘密：项目中，往往把关系分拆出一张单独表
--维表（学生维度）
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
--1.查询有多少科
select * from t_ke
select count(1) from t_ke
--2.查询同学选了多少课
select count（1） from
(select distinct ke_id from t_stu_ke) tt
--3.查选一下每个同学各选多少课
-- group by 有5个聚合函数count
select user_id,count(1) from t_stu_ke group by user_id
--4.查选一下每个同学各选多少课(条件是这个课程必须存在)
select user_id,count(1) from t_stu_ke
where ke_id in (select id from t_ke)
group by user_id
--5.查选一下每门课程各有多少学生选
select * from t_ke
--正确：
select ke_name,count(ke_id) from t_ke
left join t_stu_ke
on t_ke.id=t_stu_ke.ke_id
group by ke_name
--6.查选一下每门课程各有多少学生选
-- 条件：【显示人数】1人以上的课程
-- 思路：【显示人数】-->>集合数，不能用where，改用having
select ke_name,count(ke_id) r from t_ke
left join t_stu_ke
on t_ke.id=t_stu_ke.ke_id
group by ke_name
having count(ke_id)>=1
order by r desc
-- 7 查询出现毛的课程出来
select * from t_ke where ke_name like '%毛%'
-- 8 最多人选的课程是什么？
-- 思路1：
select * from t_ke where id=
(select ke_id from (
select ke_id,count(1) tt from t_stu_ke
group by ke_id order by tt desc
) where rownum=1)
--思路2(不推荐)
select * from t_ke where id=(
select ke_id from (
select ke_id,count(1) tt from t_stu_ke
group by ke_id
) where tt =
(
select max(tt) from (
select ke_id,count(1) tt from t_stu_ke
group by ke_id
) tt
)
)