select *
from 学生表 a
left join 科目表 b on 1=1
left join 成绩表 c on a.学生ID = c.学生ID and b.科目ID = c.科目ID
where c.id is null
from 学生表 a
left join 科目表 b on 1=1
left join 成绩表 c on a.学生ID = c.学生ID and b.科目ID = c.科目ID
where c.id is null
from 学生表 cross join 科目表
where cast(学生表.学生姓名 as varchar) + cast(科目表.科目名称 as varchar)
not in
(
select cast(学生表.学生姓名 as varchar) + cast(科目表.科目名称 as varchar)
from 学生表,科目表,成绩表
where 成绩表.学生ID = 学生表.学生ID and 成绩表.科目ID = 科目表.科目ID
)
select *
from 学生表 a
cross join 科目表 b
left join 成绩表 c on a.学生ID = c.学生ID and b.科目ID = c.科目ID
where c.id is null
cast(学生表.学生姓名 as varchar) + ',' + cast(科目表.科目名称 as varchar)
create table subject (suid varchar(10),suname varchar(10))
create table grade (gid varchar(10),stid varchar(10),suid varchar(10),grade int default 0)
insert into grade (gid,stid,suid,grade) values('001','001','001',98)
insert into grade (gid,stid,suid,grade) values('002','001','002',78)
insert into grade (gid,stid,suid,grade) values('003','002','001',79)
insert into grade (gid,stid,suid,grade) values('004','002','002',89)
insert into grade (gid,stid,suid,grade) values('005','004','001',100)
insert into student (stid,stname) values('001','li')
insert into student (stid,stname) values('002','wang')
insert into student (stid,stname) values('003','zhang')
insert into student (stid,stname) values('004','zhao')
insert into subject (suid,suname) values('001','math')
insert into subject (suid,suname) values('002','english')
select student.stname,subject.suname from student cross join subject
where student.stname+subject.suname
not in
(select student.stname+subject.suname from student,subject,grade
where grade.stid=student.stid and grade.suid=subject.suid)
drop table grade
drop table subject
drop table student
楼上的我开始崇拜你了
select d.stname,e.suname from student a cross join subject b left join grade c on a.stid=c.stid and b.suid=c.suid left join student d on a.stid=d.stid
left join subject e on b.suid=e.suid where grade is null