存储过程练习二

题目

学生成绩信息三个表，结构如下：
学生表：Member

字段名称	数据类型	说明
MID	Char(10)	学生号，主键
MName	Char(50)	姓名

课程表：F

字段名称	数据类型	说明
FID	Char(10)	课程，主键
FName	Char(50)	课程名

成绩表：Score

字段名称	数据类型	说明
SID	int	自动编号，主键，成绩记录号
FID	Char(10)	课程号，外键
MID	Char(10)	学生号，外键
Score	int	成绩

请编写T-SQL语句来实现如下功能：
1) 查询各个学生语文、数学、英语、历史课程成绩，例如下表：

姓名	语文	数学	英语	历史
张萨	78	67	89	76
王强	89	67	84	96
李三	70	87	92	56
李四	80	78	97	66

2) 查询四门课中成绩低于70分的学生及相对应课程名和成绩。
3) 统计各个学生参加考试课程的平均分，且按平均分数由高到底排序。
4) 创建存储过程，分别查询参加1、2、3、4门考试及没有参加考试的学生名单，要求显示姓名、学号。

解：

1)
SELECT MName AS 姓名,语文,数学,英语,历史 from
(SELECT m.MName,F.FNAME,s.score
FROM member m,F,score s
WHERE m.MID=s.MID AND F.FID=s.FID) AS A /*三个表联合查询找到有成绩的学生姓名，课程及分数*/
PIVOT(MAX(A.score) FOR A.FName IN (语文,数学,英语,历史))AS PVT /*列转到行上去pivot函数应用*/

SELECT m.MName,F.FNAME,s.score
FROM member m,F,score s
WHERE m.MID=s.MID AND F.FID=s.FID AND s.score<70 /*多表联合查询加条件分数小于70*/

SELECT * from
(SELECT m.MName AS 姓名,AVG(s.score)AS 平均分
FROM member m,F,score s
WHERE m.MID=s.MID AND F.FID=s.FID
GROUP BY m.MName)A
ORDER BY A.平均分 DESC

CREATE PROCEDURE [dbo].[PR_chosecoursenum]

@num INT AS BEGIN

PRINT '参加'+CONVERT(VARCHAR(5),@num)+'门考试的学生学号及姓名为'

SELECT m.MName AS 姓名,m.MID AS 学号,COUNT(s.Score)AS 数目 FROM member m LEFT JOIN score s ON m.MID=s.MID GROUP BY m.MName,m.MID HAVING COUNT(s.Score)=@num END