有成绩表T_Score（Stu_id和Lession_id为联合主键）
缺考情况不录入，例如B001的L002课程缺考。
Stu_id（学生号） Lession_id（课程） Score（成绩）
A001 L001 90
A001 L002 80
A002 L001 70
A002 L002 60
B001 L001 50
B002 L001 85
…… …… ……
学生档案T_Stu_Profile（Stu_id为主键）
包含所有学生信息
Stu_id（学生号） Stu_name（姓名） Class_id（班级）
A001 张三 06101
A002 李四 06101
B001 王五 06102
…… …… ……
课程信息表T_Lession（Lession_id为主键）
包含所有课程信息
Lession_id（课程号） Lession_des（课程）
L001 语文
L002 数学
L003 英语
L004 物理
L005 化学
一、找出缺考的学生名单，输出如下格式：
Class_id（班级） Stu_name（姓名） Lession_des（课程）
B001 王五 数学
…… …… ……
要求：如果不使用游标，如何实现；如果使用游标，又如果实现？
答：
select Class_id as 班级,Stu_name as 姓名,Lession_des as 课程
from (
select T.*,L.* from
T_Stu_Profile T,T_Lession L ) as a
where not exists
(Select 1 from T_Score AS B Where A.Stu_ID = B.Stu_ID
AND A.Lession_ID = B.Lession_ID)
二、找出五门课程中的年级前三名，输出如下格式：（假设前三名不出现并列的情况）
Lession_des（课程） 第一名 第二名 第三名
语文
数学
英语
物理
化学 …… …… ……
要求：如果不使用游标，如何实现；如果使用游标，又如果实现？
答：
declare @tab1 table
(Lession_ID varchar(32),
Stu_ID varchar(32))
declare @tab2 table
(Lession_ID varchar(32),
Stu_ID varchar(32))
declare @tab3 table
(Lession_ID varchar(32),
Stu_ID varchar(32))
INSERT @tab1
Select B.Lession_ID,A.Stu_ID
From T_Score AS A
INNER JOIN
(Select Lession_ID,MAX(Score) AS Score
From T_Score
Group By Lession_ID
) AS B ON A.Lession_ID = B.Lession_ID AND A.Score = B.Score
INSERT @tab2
Select B.Lession_ID,A.Stu_ID
From T_Score AS A
INNER JOIN
(Select Lession_ID,MAX(Score) AS Score
From
(Select * from T_Score AS A
Where not exists(Select 1 from @tab1 AS B Where A.Stu_ID = B.Stu_ID AND a.Lession_ID = B.Lession_ID)
) AS A Group By Lession_ID
) AS B ON A.Lession_ID = B.Lession_ID AND A.Score = B.Score
INSERT @tab3
Select B.Lession_ID,A.Stu_ID
From T_Score AS A
INNER JOIN
(Select Lession_ID,MAX(Score) AS Score
From
(Select * from T_Score AS A
Where not exists(Select 1 from (Select * from @tab1
UNION
Select * from @tab2) AS B Where A.Stu_ID = B.Stu_ID AND a.Lession_ID = B.Lession_ID)
) AS A Group By Lession_ID
) AS B ON A.Lession_ID = B.Lession_ID AND A.Score = B.Score
--
Select ISNULL(ISNULL(A.Lession_ID,B.Lession_ID),C.Lession_ID) AS Lession_ID,A.Stu_ID,B.Stu_ID,C.Stu_ID
From @tab1 AS A
FULL OUTER JOIN @tab2 AS B ON A.Lession_ID = B.Lession_ID
FULL OUTER JOIN @tab3 AS C ON A.Lession_ID = B.Lession_ID
---------------------------------------------------------------------------------------------------------------------------------
declare
@lession_id varchar(32),
@Lession_dsc varchar(32),
@1st numeric(9,2),
@2nd numeric(9,2),
@3rd numeric(9,2)
declare
@tab1 table(Lession_dsc varchar(32),
score1 numeric(9,2),score2 numeric(9,2),score3 numeric(9,2))
Declare tCursor CURSOR FOR
select lession_ID,Lession_des
from T_Lession
open tCursor
FETCH NEXT FROM tCursor into @lession_id,@Lession_dsc
while @@FETCH_STATUS = 0
begin
Declare aCursor CURSOR FOR
select top 3 score from T_Score where lession_id=@lession_id order by score desc open aCursor
FETCH NEXT FROM aCursor into @1st
if(@@FETCH_STATUS = 0)
FETCH NEXT FROM aCursor into @2nd
if(@@FETCH_STATUS = 0)
FETCH NEXT FROM aCursor into @3rd
CLOSE aCursor
DEALLOCATE aCursor
insert into @tab1 values(@Lession_dsc,@1st ,@2nd ,@3rd)
FETCH NEXT FROM tCursor into @lession_id,@Lession_dsc
end
CLOSE tCursor
DEALLOCATE tCursor
select * from @tab1
三、输出06101班的学生成绩单，格式如下：
姓名 语文 数学 英语 物理 化学 总分
…… …… …… …… …… …… ……
要求：如果不使用游标，如何实现；如果使用游标，又如果实现？
答：
Select Stu_ID
,SUM(CASE Lession_ID WHEN 'L001' THEN Score ELSE 0 END) AS 语文
,SUM(CASE Lession_ID WHEN 'L002' THEN Score ELSE 0 END) AS 数学
,SUM(CASE Lession_ID WHEN 'L003' THEN Score ELSE 0 END) AS 英语
,SUM(CASE Lession_ID WHEN 'L004' THEN Score ELSE 0 END) AS 物理
,SUM(CASE Lession_ID WHEN 'L005' THEN Score ELSE 0 END) AS 化学
From T_Score
Group By Stu_ID
四、假如成绩表增加考试日期Test_Date，记录高中三年大大小小每次考试成绩。请问：如何求出高三阶段（2005年），每位学生的每门课的平均考试成绩。（缺考以及60分以下的成绩不计入平均，如高三数学共考试20次，B001缺考一次、另一次成绩58分，则B001的平均考试成绩以18次计算）。输出格式同试题三。
答：
Select Stu_ID,Lession_ID,SUM(Score)/Count(*) AS AvgScore
From T_Score
Where Test_Date = 2005 AND Score >=60
Group By Stu_ID,Lession_ID
