求缺考的学生的id,姓名,和缺考的科目
id,姓名我会求,但缺考科目不会
select s.* from student s join
(select sid from score group by sid having count(lesson_id)<2) b
on s.sid=b.sid
--
表:student
sid name
001 wade
002 jordan
003 kobe
表:lesson
lesson_id lesson_name
L001 chinese
L002 maths
表:score(没输入,为缺考,003没考maths)
sid lesson_id score
001 L001 100
001 L002 80
002 L001 70
002 L002 60
003 L001 50create table student(sid char(3),name nvarchar(20))
insert student select '001','wade' union
select '002','jordan' union
select '003','kobe'
--
create table lesson(lesson_id nvarchar(8),lesson_name nvarchar(20))
insert into lesson select 'L001','chinese' union all
select 'L002','maths'
--
create table score(sid char(3),lesson_id nvarchar(8),score decimal(4,1))
insert into score select '001','L001',100 union all
select '001','L002',80 union all
select '002','L001',70.0 union all
select '002','L002',60.0 union all
select '003','L001',50.0
id,姓名我会求,但缺考科目不会
select s.* from student s join
(select sid from score group by sid having count(lesson_id)<2) b
on s.sid=b.sid
--
表:student
sid name
001 wade
002 jordan
003 kobe
表:lesson
lesson_id lesson_name
L001 chinese
L002 maths
表:score(没输入,为缺考,003没考maths)
sid lesson_id score
001 L001 100
001 L002 80
002 L001 70
002 L002 60
003 L001 50create table student(sid char(3),name nvarchar(20))
insert student select '001','wade' union
select '002','jordan' union
select '003','kobe'
--
create table lesson(lesson_id nvarchar(8),lesson_name nvarchar(20))
insert into lesson select 'L001','chinese' union all
select 'L002','maths'
--
create table score(sid char(3),lesson_id nvarchar(8),score decimal(4,1))
insert into score select '001','L001',100 union all
select '001','L002',80 union all
select '002','L001',70.0 union all
select '002','L002',60.0 union all
select '003','L001',50.0
from score a,esson b,student c
where a.sid=c.sid and a.lesson_id=b.lesson_id and (c.score is null or isnull(c.score,0)=0)
(
select m.sid , m.name , n.lesson_id , n.lesson_name from student m,lesson n
) t1 where not exists(select 1 from score t2 where t2.sid = t1.sid and t2.lesson_id = t1.lesson_id)/*
sid name lesson_id lesson_name
---- -------------------- --------- --------------------
003 kobe L002 maths(所影响的行数为 1 行)
*/
a.sid,
a.name as 姓名,
b.lesson_name as 缺考的科目
from student a,lesson b
where not exists(select * from score where sid=a.sid and lesson_id =b.lesson_id)/**
sid 姓名 缺考的科目
---- -------------------- --------------------
003 kobe maths(所影响的行数为 1 行)
**/
from(
select s.sid,l.lesson_id,l.lesson_name
from student s ,lesson l ) b
full join score c
on b.lesson_id = c.lesson_id and b.sid = c.sid
where c.lesson_id is null