实际例题1 for sql语言

sn sid name subject score
1 1001 张青 语文 87
2 1001 张青 语文 78
3 1002 李立 数学 65
4 1002 李立 数学 56
5 1003 王华 英语 90

1.查询出登记成绩出错的学员姓名
2.如果某学员成绩出错，则保留其该门课程的最高分，其余记录删除。
怎么写SQL语句？

 

1.

select name from student
group by name
havingcount(sid)>1
and count(subject)>1

 

2.

delete from student1                          --总体思想：找到想要的，用delete not in得出结果
where sn not in                               --想要的包括两部分：1.没重复的
              (                               --                  2.重复中但是score是最大的一条
               select sn from student1
               where (name,subject)  not in             --取出来没重复的   
                                          (
                                           select name,subject from student1
                                           group by name,subject
                                           having count(sn)>1
                                          )
               or (name,subject,score)  in              --取出来score最大的     
                                     (
                                      select name,subject,max(score)from student1
                                      group by subject,name
                                     )
               and sn in                                 --挑选最小的序号    
                        (
                         select min(sn) from student1
                         group by name,subject,score
                        )
               )

 

3.

select * from student1
where (name,score,subject) in
(
select name,score,subject from student1
group by name,score,subject
having count(sn)>1
)
and sn not in
(
select min(sn) from student1
group by name,score,subject
having count(*)>1
)