成绩管理的简单问题

[sunwindj]

/**
* 生成测试题目数据表所需表，并添加记录
* @Project SQL Test
* @Author Wan Yadong
* @Date 2003-11-02
*/



BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SctTable]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[SctTable]

CREATE TABLE [dbo].[SctTable] ( [Id] [int] NOT NULL IDENTITY (1, 1),
[StudentID] [int] NULL ,
[StudentName] [char] (12) COLLATE Chinese_PRC_CI_AS NULL ,
[ClassID] [int] NULL ,
[ClassName] [char] (20) NULL ,
[Score] [int] NULL ,
[TeacherID] [int] NULL ,
[TeacherName] [char] (12) COLLATE Chinese_PRC_CI_AS NULL )
ON [PRIMARY]

/**
* 以下加入记录
*/
INSERT INTO SctTable VALUES (3,'张三', 4, '政治', 53, 4, '李老师')
INSERT INTO SctTable VALUES (1,'陈一', 1, '数学', 61, 1, '赵老师')
INSERT INTO SctTable VALUES (2,'周二', 3, '英语', 88, 3, '孙老师')
INSERT INTO SctTable VALUES (1,'陈一', 4, '政治', 77, 4, '李老师')
INSERT INTO SctTable VALUES (2,'周二', 4, '政治', 67, 5, '周老师')
INSERT INTO SctTable VALUES (3,'张三', 2, '语文', 90, 2, '钱老师')
INSERT INTO SctTable VALUES (3,'张三', 1, '数学', 55, 1, '赵老师')
INSERT INTO SctTable VALUES (1,'陈一', 2, '语文', 81, 2, '钱老师')
INSERT INTO SctTable VALUES (4,'李四', 2, '语文', 59, 1, '钱老师')
INSERT INTO SctTable VALUES (1,'陈一', 3, '英语', 37, 3, '孙老师')
INSERT INTO SctTable VALUES (2,'周二', 1, '数学', 81, 1, '赵老师')

GO
COMMIT


问题 1：统计出各科成绩最高和最低的相关记录。
格式如下：
ClassID, ClassName, 最高分，StudentID, StudentName, TeacherID,
TeacherName, 最低分，StudentID, StudentName, TeacherID, TeacherName

问题 2：按平均成绩从高到低顺序，列出所有学生的四门课程成绩
格式如下：
StudentID, StudentName, 数学,语文，英语，政治，有效课程数，平均分数

[pengdali]

1、

select a.*,b.最底分,b.StudentID,b.StudentName,b.TeacherID,b.TeacherName from (
select classid,classname,score 最高分,StudentID, StudentName, TeacherID,TeacherName from SctTable tem where score=(select max(score) from scttable where classid=tem.classid)) a
,(
select classid,classname,score 最底分,StudentID, StudentName, TeacherID,TeacherName from SctTable tem where score=(select min(score) from scttable where classid=tem.classid)) b
where a.classid=b.classid

[pengdali]

2、


select StudentID,StudentName
,sum(case when classname='数学' then score else 0 end) 数学
,sum(case when classname='语文' then score else 0 end) 语文
,sum(case when classname='英语' then score else 0 end) 英语
,sum(case when classname='政治' then score else 0 end) 政治
,count(score) 有效课程数
,avg(score) 平均分数
from SctTable
group by StudentID,StudentName order by avg(score) desc

[pengdali]

2、动态得到：

declare @sql varchar(8000)
set @sql = 'select StudentID,StudentName'
select @sql = @sql + ',sum(case ClassID when '''+cast(ClassID as varchar(10))+''' then score else 0 end) ['+ClassName+']'
from (select distinct ClassID,ClassName from SctTable) as a
select @sql = @sql+',count(score) 有效课程数,avg(score) 平均分数 from SctTable group by StudentID,StudentName order by avg(score) desc'

exec(@sql)
go

[baisun]

就如 pengdali(大力 V3.0) 所说做吧。

[diannetasp]

如 pengdali(大力 V3.0) 所说做吧 ,学习.

[tangxc2003]

就如 pengdali(大力 V3.0) 所说做吧