teacher:教师表(id,name)
course:课程表(id,name)
teachcourse:教师课程表(teach_id,course_id)
1.
select teacher.name from teacher,course,teachcourse where teacher.id=teachcourse.teach_id and course.id=teachcourse.course_id and
teacher.name='微机原理' or teacher.name='C语言'
2.
select teacher.name from teacher,course,teachcourse where teacher.id=teachcourse.teach_id and
teacher.name='微机原理' or teacher.name='C语言'
course:课程表(id,name)
teachcourse:教师课程表(teach_id,course_id)
1.
select teacher.name from teacher,course,teachcourse where teacher.id=teachcourse.teach_id and course.id=teachcourse.course_id and
teacher.name='微机原理' or teacher.name='C语言'
2.
select teacher.name from teacher,course,teachcourse where teacher.id=teachcourse.teach_id and
teacher.name='微机原理' or teacher.name='C语言'
select 教师表.教师姓名 from 教师带课表 inner join 教师表 on 教师带课表.教师ID=教师表.教师ID inner join 课程表 on 课程表.课程ID=教师带课表.课程ID
where (课程表.课程名="微机原理" or 课程表.课程名="C语言")
group by 教师表.教师ID,教师表.教师姓名 having count(教师表.教师ID)=2
2
select 教师表.教师姓名 from 教师带课表 inner join 教师表 on 教师带课表.教师ID=教师表.教师ID inner join 课程表 on 课程表.课程ID=教师带课表.课程ID
where (课程表.课程名="微机原理" or 课程表.课程名="C语言")
group by 教师表.教师ID,教师表.教师姓名教师可能重名
go
insert into 教师表 VALUES ('A')
insert into 教师表 VALUES ('B')
insert into 教师表 VALUES ('C')
insert into 教师表 VALUES ('D')
GO
create table 课程表( 课程ID INT IDENTITY(1000,1),课程名 NVARCHAR(200))insert into 课程表 VALUES ('微机原理')
insert into 课程表 VALUES ('VC')
insert into 课程表 VALUES ('JAVA')
insert into 课程表 VALUES ('数据库原理')
insert into 课程表 VALUES ('C语言')
GO
CREATE TABLE 教师带课表(教师ID INT ,课程ID INT)
INSERT INTO 教师带课表 VALUES(1,1000)
INSERT INTO 教师带课表 VALUES(2,1000)
INSERT INTO 教师带课表 VALUES(1,1004)
INSERT INTO 教师带课表 VALUES(3,1004)
INSERT INTO 教师带课表 VALUES(4,1001)
INSERT INTO 教师带课表 VALUES(2,1004)
GO
--question 1
select B.教师姓名 from 教师带课表 A
left outer join 教师表 B on A.教师ID=B.教师ID
left outer join 课程表 C on C.课程ID=A.课程ID
where (C.课程名='微机原理' OR C.课程名='C语言')
group by B.教师姓名 having count(B.教师ID)=2--question 2
select B.教师姓名 from 教师带课表 A
left outer join 教师表 B on A.教师ID=B.教师ID
left outer join 课程表 C on C.课程ID=A.课程ID
where (C.课程名='微机原理' OR C.课程名='C语言')
group by B.教师姓名
go
select *
from 教师表
where 教师ID in (select 教师id
from 教师带课表
where 课程ID = (select max(课程ID)
from 课程表
where 课程名= '微机原理'
)
)
AND
教师ID in (select 教师id
from 教师带课表
where 课程ID = (select max(课程ID)
from 课程表
where 课程名= 'C语言'
)
)2.
select *
from 教师表
where 教师ID in (select 教师id
from 教师带课表
where 课程ID = (select max(课程ID)
from 课程表
where 课程名= '微机原理'
)
)
OR
教师ID in (select 教师id
from 教师带课表
where 课程ID = (select max(课程ID)
from 课程表
where 课程名= 'C语言'
)
)
select *
from 教师表
where 教师ID in (select 教师id
from 教师带课表
where 课程ID = (select max(课程ID)
from 课程表
where 课程名= '微机原理'
)
or
课程ID = (select max(课程ID)
from 课程表
where 课程名= 'C语言'
) )
还要再加 where 条件:
and
教师ID in (select 教师id
from 教师带课表 T
where (select count(*) from 教师带课表 where T.教师id = 教师id ) =2
)或者:
and
教师ID in (select 教师id
from 教师带课表
group by 教师id
having count(*) =2
)
即:
select *
from 教师表
where 教师ID in (select 教师id
from 教师带课表
where 课程ID = (select max(课程ID)
from 课程表
where 课程名= '微机原理'
)
)
AND
教师ID in (select 教师id
from 教师带课表
where 课程ID = (select max(课程ID)
from 课程表
where 课程名= 'C语言'
)
)
and
教师ID in (select 教师id
from 教师带课表 T
where (select count(*) from 教师带课表 where T.教师id = 教师id ) =2
)/*
and
教师ID in (select 教师id
from 教师带课表
group by 教师id
having count(*) =2
)*/