http://bbs.csdn.net/topics/390859869
这里还有100分,只求答案.
这里还有100分,只求答案.
解决方案 »
- 怎么查看操作SQL SERVER 2005数据库的记录?
- 问个困惑很久的问题
- 新手问题,在线等
- 请问如何比较一个datetime类型带时分秒,一个datetime类型不带时分秒
- MSDE怎么总也安不上啊?
- 能不能用变量来接收exec('')返回的值?
- 我想插入varchar类型字段如下的值'601090','6010901','6010902'(注意这是一个完整的字符串)请问SQL应该如何写?
- Microsoft OLE DB Provider for SQL Server 错误 '80040e14' 在关键字 'and' 附近有语法错误。
- 不等记录数的行列转换
- 对表查询结果取字段,某些字段会出现:无效的描述符索引,请高手指教
- 用jdbc连接数据库报这个错误怎么解决?
- 求帮助
[classID] [int] IDENTITY(1,1) NOT NULL,
[classname] [nvarchar](50) NULL,
PRIMARY KEY CLUSTERED
(
[classID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[Course](
[CourseID] [int] IDENTITY(1,1) NOT NULL,
[CourseName] [nvarchar](50) NULL,
PRIMARY KEY CLUSTERED
(
[CourseID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[Mark](
[studnetId] [int] NULL,
[CourseID] [int] NULL,
[score] [decimal](18, 2) NULL
) ON [PRIMARY]CREATE TABLE [dbo].[Student](
[studnetId] [int] IDENTITY(1,1) NOT NULL,
[studentName] [nvarchar](50) NULL,
[sex] [nvarchar](2) NULL,
[age] [nvarchar](3) NULL,
[classId] [int] NULL,
CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED
(
[studnetId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]这是个5列结果
班级 最好课程成绩 成绩 最差成绩课程 成绩
我的思路是先求平均成绩 再求最高的平均成绩 和最差的平均成绩 放在一起 没写出来.
Class A inner join
(
select t.classId,
maxcouseID=(select CourseID from (select B.classId,A.CourseID, avg(A.score) 平均成绩 from dbo.Mark A inner join dbo.Student B on
A.studnetId=B.studnetId
group by A.CourseID,B.classId) c where C.classId =t.classId and 平均成绩=MAX(t.平均成绩)),
最高分=MAX(t.平均成绩),
mincouseID=(select CourseID from (select B.classId,A.CourseID, avg(A.score) 平均成绩 from dbo.Mark A inner join dbo.Student B on
A.studnetId=B.studnetId
group by A.CourseID,B.classId) c where C.classId =t.classId and 平均成绩=MIN(t.平均成绩)),
最低分=MIN(t.平均成绩)
from (
select B.classId,A.CourseID, avg(A.score) 平均成绩 from dbo.Mark A inner join dbo.Student B on
A.studnetId=B.studnetId
group by A.CourseID,B.classId
) t
group by t.classId
) Z ON A.classID=Z.classId