多对多查询
有学生,课程两个表是对对多关系的,中间表是分数表,如学生表数据是1 张三,2 李四 3 王五,
课程表数据是 1 语文,2 数学,3 外语, 中间表是 1 1 1 90,代表的是 1条记录 对应的是张三 语文 90分,现在要求是查询出哪些学生缺了哪些课?如果关联查询的话是哪些学生有哪些课,那怎么查没有的呢?
[解决办法]
先通过全链接 查询出所有人的所有课程 然后从里面过滤掉有分数的课程
[解决办法]
一般思路 大致这样了...
with t1 as
(
select 1 c1,'张三' c2 from dual union all
select 2 c1,'李四' c2 from dual union all
select 3 c1,'王五' c2 from dual
),t2 as
(
select 1 c3,'语文' c4 from dual union all
select 2 c3,'数学' c4 from dual union all
select 3 c3,'英语' c4 from dual
),t3 as
(
select 1 c5,1 c6,1 c7,80 c8 from dual union all
select 2 c5,1 c6,2 c7,66 c8 from dual union all
select 3 c5,2 c6,1 c7,78 c8 from dual union all
select 4 c5,3 c6,3 c7,95 c8 from dual
)
select a2,a4
from
(select c1 a1,c2 a2,c3 a3 ,c4 a4 from t1,t2) a
left join
(select c1 b1 ,c3 b3,c8 b8
from t1,t2,t3
where c1 = c6 and c3 = c7) b
on a1 = b1 and a3 = b3
where b8 is null
a2 a4
--------------------------
1王五语文
2李四英语
3张三英语
4王五数学
5李四数学