求解一个题

Question

Score表  （成绩表） （学生编号）
 （课程编号）
  （成绩）表中插入的值
 
103    3-245    84
 
105    3-245    67
 
109    3-245    98
 
103    3-105    78
 
105    3-105    91
 
109    3-105    68
 
101    3-105    93
 
107    3-105    72
 
108    3-105    66
 
101    6-166    80
 
107    6-166    77
 
108    6-166    62103    6-166    70105    6-166    80 
查询选修多门课程的每个同学的分数中分数为非最高成绩的记录 

Answer 1

select t.* from score t where 成绩 not in (select max(成绩) from score where 学生编号 = t.学生编号)

Answer 2

create table Score(学生编号 int,课程编号 varchar(10),成绩 int) 
insert into score values(103 ,   '3-245' ,   84) 
insert into score values(105 ,   '3-245' ,   67) 
insert into score values(109 ,   '3-245' ,   98) 
insert into score values(103 ,   '3-105' ,   78) 
insert into score values(105 ,   '3-105' ,   91) 
insert into score values(109 ,   '3-105' ,   68) 
insert into score values(101 ,   '3-105' ,   93) 
insert into score values(107 ,   '3-105' ,   72) 
insert into score values(108 ,   '3-105' ,   66) 
insert into score values(101 ,   '6-166' ,   80) 
insert into score values(107 ,   '6-166' ,   77) 
insert into score values(108 ,   '6-166' ,   62) 
insert into score values(103 ,   '6-166' ,   70) 
insert into score values(105 ,   '6-166' ,   80)
goselect t.* from score t where 成绩 not in (select max(成绩) from score where 学生编号 = t.学生编号)drop table score/*
学生编号        课程编号       成绩          
----------- ---------- ----------- 
101         6-166      80
103         6-166      70
103         3-105      78
105         3-245      67
105         6-166      80
107         3-105      72
108         6-166      62
109         3-105      68（所影响的行数为 8 行）*/ 

Answer 3

CREATE Table Score
(
StuNo VARCHAR(10),
CourseNo VARCHAR(10),
MyScore INT
)
GO
insert into Score select '103','3-245',84
insert into Score select '105','3-245',67
insert into Score select '109','3-245',98
insert into Score select '103','3-105',78
insert into Score select '105','3-105',91
insert into Score select '109','3-105',68
insert into Score select '101','3-105',93
insert into Score select '107','3-105',72
insert into Score select '108','3-105',66
insert into Score select '101','6-166',80
insert into Score select '107','6-166',77
insert into Score select '108','6-166',62
insert into Score select '103','6-166',70
insert into Score select '105','6-166',80
SELECT s.* FROM score s JOIN 
(
SELECT StuNo, MIN(MyScore) AS MyScore FROM score GROUP BY StuNo HAVING COUNT(*)>1
) AS t ON s.[StuNo] = t.StuNo AND s.[MyScore] = t.MyScore
ORDER BY [StuNo]StuNo      CourseNo   MyScore
---------- ---------- -----------
101        6-166      80
103        6-166      70
105        3-245      67
107        3-105      72
108        6-166      62
109        3-105      68(6 row(s) affected)

Answer 4

CREATE Table Score
(
StuNo VARCHAR(10),
CourseNo VARCHAR(10),
MyScore INT
)
GO
insert into Score select '103','3-245',84
insert into Score select '105','3-245',67
insert into Score select '109','3-245',98
insert into Score select '103','3-105',78
insert into Score select '105','3-105',91
insert into Score select '109','3-105',68
insert into Score select '101','3-105',93
insert into Score select '107','3-105',72
insert into Score select '108','3-105',66
insert into Score select '101','6-166',80
insert into Score select '107','6-166',77
insert into Score select '108','6-166',62
insert into Score select '103','6-166',70
insert into Score select '105','6-166',80
SELECT s.* FROM score s JOIN 
(
SELECT StuNo, MAX(MyScore) AS MyScore FROM score GROUP BY StuNo HAVING COUNT(*)>1 AND MAX(MyScore)<>min(MyScore)

) AS t ON s.[StuNo] = t.StuNo AND s.[MyScore] <> t.MyScore
UNION ALL
SELECT s.* FROM score s JOIN 
(
SELECT StuNo, MAX(MyScore) AS MyScore FROM score GROUP BY StuNo HAVING COUNT(*)>1 AND MAX(MyScore)=min(MyScore)
) AS t ON s.[StuNo] = t.StuNo AND s.[MyScore] = t.MyScore
ORDER BY [StuNo]StuNo      CourseNo   MyScore
---------- ---------- -----------
101        6-166      80
101        6-166      80
103        3-105      78
103        6-166      70
103        6-166      70
103        3-105      78
105        3-245      67
105        6-166      80
105        3-245      67
105        6-166      80
107        3-105      72
107        3-105      72
108        6-166      62
108        6-166      62
109        3-105      68
109        3-105      68(16 row(s) affected)

Answer 5

CREATE Table Score
(
StuNo VARCHAR(10),
CourseNo VARCHAR(10),
MyScore INT
)
GO
insert into Score select '103','3-245',84
insert into Score select '105','3-245',67
insert into Score select '109','3-245',98
insert into Score select '103','3-105',78
insert into Score select '105','3-105',91
insert into Score select '109','3-105',68
insert into Score select '101','3-105',93
insert into Score select '107','3-105',72
insert into Score select '108','3-105',66
insert into Score select '101','6-166',80
insert into Score select '107','6-166',77
insert into Score select '108','6-166',62
insert into Score select '103','6-166',70
insert into Score select '105','6-166',80
SELECT s.* FROM score s JOIN 
(
SELECT StuNo, MAX(MyScore) AS MyScore FROM score GROUP BY StuNo HAVING COUNT(*)>1 AND MAX(MyScore)<>min(MyScore)

) AS t ON s.[StuNo] = t.StuNo AND s.[MyScore] <> t.MyScore
UNION 
SELECT s.* FROM score s JOIN 
(
SELECT StuNo, MAX(MyScore) AS MyScore FROM score GROUP BY StuNo HAVING COUNT(*)>1 AND MAX(MyScore)=min(MyScore)
) AS t ON s.[StuNo] = t.StuNo AND s.[MyScore] = t.MyScore
ORDER BY [StuNo]StuNo      CourseNo   MyScore
---------- ---------- -----------
101        6-166      80
103        3-105      78
103        6-166      70
105        3-245      67
105        6-166      80
107        3-105      72
108        6-166      62
109        3-105      68(8 row(s) affected)