1.select a.* from 表1 a left join 表3 b on a.sname=b.sname
where not exists (select 1 from 表2 where cname=b.cname and 教师姓名='李老师')
2.select a.* from 表1 a where sname in(select sname from 表3 group by sname having count(1)>=2)
3.select a.* from 表1 a where not exists(select 1 from 表3 where sname=a.sname and cname='1' or cname='2')
where not exists (select 1 from 表2 where cname=b.cname and 教师姓名='李老师')
2.select a.* from 表1 a where sname in(select sname from 表3 group by sname having count(1)>=2)
3.select a.* from 表1 a where not exists(select 1 from 表3 where sname=a.sname and cname='1' or cname='2')
select A.SName
from A
left join C on A.SNAME=C.SNAME
left join B on C.CNAME=B.CNAME
where C.SNAME is null
and
B.TEACHER='李老师'
--2:查询所有两科以上不及格的全部学生的名单。
select distinct SNAME
from C T
where (select count(1)
from C
where SNAME=T.SNAME
and OTHER<60
)>2--3:查询所有没有参加“1”课程,和“2”课程的全部学生名单
select T.SNAME
from A T
where not exists(select 1 from C where CNAME in ('1','2'))
2.select sname from 表3 group by sname having count(case when other<60 then 1 else 0 end)>2
3.select 表1.sname from 表1 left join 表3 on 表1.sname=表3.sname where cname<>'1' and cname<>'2'
select *from 学生A awhere not exists(select 1
from 成绩C c join 课程B b
on c.CNAME=b.CNAME
where c.SNMAE=a.SNMAE and b.TEACHER='李老师'
)
--The Second One.
select *from 学生A awhere (select count(*)
from 成绩C
where SNMAE=a.SNMAE and OTHER<60)>2
--The Third One.select *from 学生A awhere not exists(select 1
from 成绩C
where SNMAE=a.SNMAE and CNAME in ('1','2')
)
表名=case when a.colorder=1 then d.name else '' end,
查询所有没上“李老师”的课程的全部学生名单
select a.sname from table1 a inner join table2 c on a.sname=c.sname
inner join table b on b.cname=c.cname where b.TEACHER<>'李老師'查询所有两科以上不及格的全部学生的名单 select a.sname from table1 a inner join table2 c on a.sname=c.sname
inner join table b on b.cname=c.cname where c.OTHEr<'60'
group by CName
( having count(*)>2 )
查询所有没有参加“1”课程,和“2”课程的全部学生名单 select a.sname from table1 a inner join table2 c on a.sname=c.sname
inner join table b on b.cname=c.cname where c. cname in (1,2)
where not exists (select 1 from 表2 where cname=b.cname and b.teacher='李老师')
2.select a.* from 表1 a where sname in(select sname from 表3 where OTHER<60
group by sname having count(1)>=2)
3.select a.* from 表1 a where not exists(select 1 from 表3 where sname=a.sname and cname='1' or cname='2')
表1:学生A(SNAME,AGE) SNAME:学生姓名,AGE:年龄
表2: 课程B(CNAME,TEACHER) CNAME:课程名称 TEACHER:教师姓名
表3: 成绩C(SNMAE,CNAME,OTHER) OTHER:成绩请问如何实现:
1:查询所有没上“李老师”的课程的全部学生名单。
2:查询所有两科以上不及格的全部学生的名单。
3:查询所有没有参加“1”课程,和“2”课程的全部学生名单
--------------------------------------------------------------1.Select A.* from 学生A A
inner join 成绩C C on A.Sname=C.Sname
where not exists(Select 1 from 课程B B where B.Cname=C.Cname and B.Cname<>'李老师')2.Select A.* from 学生A A
where not exists( Select count(1) from 成绩C C where A.Sname=C.Name and OTHER<60
group by Sname having count(1)<2)
select * from 学生A A
where not exists(Select 1 from 成绩C C where A.Sname=C.Sname and (cname='1' or cname='2'))
where not exists (select 1 from 表2 where cname=b.cname and 教师姓名='李老师')
2.select a.* from 表1 a where sname in(select sname from 表3 group by sname having count(1)>=2)
3.select a.* from 表1 a where not exists(select 1 from 表3 where sname=a.sname and cname='1' or cname='2')