=>junerr(路在何方) 谢谢回答,但是还有没有更好的解决方案。 因为如果要查询同时教授n门课程的老师的话, 就需要做n个表的结合,效率会不会低。select T1.teacher from A as T1,A as T2... where T1.teacher=T2.teacher and T1.course='C002' and T2.course='C003' ....Tn ='xxxx'
select teacher, count(distinct course) from A where A.course in ('C002', 'C003') group by teacher having count(distinct course)=2;
select teacher from A where A.curse in ('C002','C003') group by teacher;
其实速率上与表的大小,与索引是否得到更高效作标准。 有时用in比关系查询还要快。如: select a.* from a,b where a.id=b.id -- 1 select * from a where id in (select id from b)--2 语句2在特定环境有可能比语句1快.根据Oracle优化路径 rule 模式下的规则:1、有两个索引存在的情况下,from后面大表在前。小表(返回记录少的表,不是记录少的表)在最后,作为驱动表,Oracle处理SQL语句是从左到右2、有一个索引,则顺序无关3、都没有索引,则大表应该在后
=> bobfang(匆匆过客) 好主意
Select Distinct Teacher from A where Course in ('C002', 'C003');
select T1.teacher from A as T1,A as T2
where T1.teacher=T2.teacher and T1.course='C002' and T2.course='C003'
这是什么逻辑啊
(select teacher from A where course='C002')
and course='C003'
i)在条件T1.course='C002'下,T1将为
Teacher Course
T001 C002
T002 C002ii)在条件T2.course='C003'下,T2将为
Teacher Course
T001 C003
T003 C003iii)在连接条件T1.teacher=T2.teacher下,T1的第一行(T001,C002)和T2的第一行(T001,C003)连接,这时T1.teacher='T001'正是要找的教师
谢谢回答,但是还有没有更好的解决方案。
因为如果要查询同时教授n门课程的老师的话,
就需要做n个表的结合,效率会不会低。select T1.teacher from A as T1,A as T2...
where T1.teacher=T2.teacher and T1.course='C002' and T2.course='C003'
....Tn ='xxxx'
from A
where A.course in ('C002', 'C003')
group by teacher
having count(distinct course)=2;
where A.curse in ('C002','C003')
group by teacher;
有时用in比关系查询还要快。如:
select a.* from a,b where a.id=b.id -- 1
select * from a where id in (select id from b)--2
语句2在特定环境有可能比语句1快.根据Oracle优化路径
rule 模式下的规则:1、有两个索引存在的情况下,from后面大表在前。小表(返回记录少的表,不是记录少的表)在最后,作为驱动表,Oracle处理SQL语句是从左到右2、有一个索引,则顺序无关3、都没有索引,则大表应该在后
好主意
where Course in ('C002', 'C003');