sql语句

Question

数据库表：
学生表：学号，姓名，年龄，性别
课程表：课程号，课程名
成绩表：学号，课程号，成绩问题：查询每个课程的最高分数，最高分姓名，最低分数，最低分姓名，平均分

Answer 1

create table Student
(
S# varchar(50),
Sname varchar(50),
Ssex varchar(2)
)create table Course
(
C# varchar(50),
CName varchar(50),
)create Table SC 
(
S# varchar(50),
C# varchar(50),
Score varchar(50)
)insert into Student values (1,'张三',1);
insert into Student values (2,'李四',0);
insert into Student values (3,'王五',1);insert into Course values (1,'语文')
insert into Course values (2,'数学')insert into SC values (1,1,90)
insert into SC values (1,2,80)
insert into SC values (2,1,95)
insert into SC values (2,2,70)
insert into SC values (3,1,85)
insert into SC values (3,2,99)
SELECT A.*,B.*,C.AVGSCORE FROM 
(
SELECT DISTINCT C.CName,S.Sname as 科目最高分姓名,SC2.S#,SC1.C#,SC1.MAXScore FROM Course C
inner join  
(SELECT SC.C#,MAX(SC.Score) AS MAXScore FROM SC GROUP BY SC.C#) SC1 
ON C.C#=SC1.C#
INNER JOIN SC SC2 ON SC1.C#=SC2.C# AND SC1.MAXScore=SC2.Score
INNER JOIN Student S ON S.S#=SC2.S#
) A,
(
SELECT DISTINCT C.CName,S.Sname AS 科目最低分姓名,SC2.S#,SC1.C#,SC1.MINScore FROM Course C
inner join  
(SELECT SC.C#,MIN(SC.Score) AS MINScore FROM SC GROUP BY SC.C#) SC1 
ON C.C#=SC1.C#
INNER JOIN SC SC2 ON SC1.C#=SC2.C# AND SC1.MINScore=SC2.Score
INNER JOIN Student S ON S.S#=SC2.S#
) B ,(SELECT SC.C#,AVG(CAST(SC.Score AS DECIMAL(10,2))) AS AVGSCORE FROM SC GROUP BY SC.C#) CWHERE A.CName=B.CName AND A.C#=C.C#

Answer 2

呵呵，分不分的不算个啥就是看见sql的问题就想试试手，其实现在工作上用的是oracle
平时就拿sqlserver练手

Answer 3

用3楼的语句创建表和数据，score改成int型或其他数据型的，别用varchar了...select distinct Cname 科目,a.score1 最高成绩,d.Sname 最高成绩姓名,a.score2 最低成绩, e.sname 最低成绩姓名,a.score3 平均成绩 
from (select c#,max(score) score1,min(score) score2,avg(score) score3 
from sc a group by c#) a,sc b,sc c,student d,student e,course f
where a.c#=b.c# and a.score1=b.score and a.score2=c.score and b.s#=d.s# and c.s#=e.s# and a.c#=f.c#如果最高成绩和最低成绩有重复的，输出的数据不太好看...

Answer 4

这题难道真有难度？那个叫dba什么的也没为回复？

Answer 5

呃，高手，其实就只有一个人回复了呀。
就是贴里的问题，问一条sql语句
看看你能不能给条简单的语句

Answer 6

USE test
GO-->生成表t1
if object_id('t1') is not null 
drop table t1
Go
Create table t1([学号] nvarchar(2),[姓名] nvarchar(2),[年龄] smallint,[性别] nvarchar(1))
Insert into t1
Select N'N1',N'张三',17,N'M'
Union all Select N'N2',N'李四',17,N'F'
Union all Select N'N3',N'王五',16,N'M'
Union all Select N'N4',N'赵六',16,N'F'-->生成表t2
if object_id('t2') is not null 
drop table t2
Go
Create table t2([课程号] nvarchar(2),[课程名] nvarchar(2))
Insert into t2
Select N'S1',N'语文'
Union all Select N'S2',N'数学'
Union all Select N'S3',N'英语'-->生成表t3
if object_id('t3') is not null 
drop table t3
Go
Create table t3([学号] nvarchar(2),[课程号] nvarchar(2),[成绩] smallint)
Insert into t3
Select N'N1',N'S1',53
Union all Select N'N1',N'S2',87
Union all Select N'N1',N'S3',90
Union all Select N'N2',N'S1',86
Union all Select N'N2',N'S2',75
Union all Select N'N2',N'S3',95
Union all Select N'N3',N'S1',90
Union all Select N'N3',N'S2',89
Union all Select N'N3',N'S3',92
Union all Select N'N4',N'S1',73
Union all Select N'N4',N'S2',98
Union all Select N'N4',N'S3',69
--- Sql Statement
select 
b.课程名
,MAX(CASE WHEN c.Nature=0 THEN c.成绩 END) AS 最高分数
,MAX(CASE WHEN c.Nature=0 THEN a.姓名 END) AS 最高分数姓名
,MAX(CASE WHEN c.Nature=1 THEN c.成绩 END) AS 最低分数
,MAX(CASE WHEN c.Nature=1 THEN a.姓名 END) AS 最低分姓名
,(SELECT AVG(成绩) FROM t3 WHERE 课程号=b.课程号) AS 平均分
from t1 AS a,t2 AS b,(
SELECT 
学号
,课程号
,成绩
,0 AS Nature
FROM t3 AS x 
WHERE NOT EXISTS(SELECT 1 FROM t3 
WHERE 课程号=x.课程号 AND 成绩>x.成绩
)
UNION ALL
SELECT 
学号
,课程号
,成绩
,1
FROM t3 AS x 
WHERE NOT EXISTS(SELECT 1 FROM t3 
WHERE 课程号=x.课程号 AND 成绩<x.成绩
)

) AS cWHERE a.学号=c.学号 AND b.课程号=c.课程号
GROUP BY b.课程名,b.课程号/*
课程名  最高分数   最高分数姓名  最低分数   最低分姓名  平均分
------- ---------  ------------- ---------  ----------- ------
语文    90         王五  53     张三        75
数学    98         赵六  75         李四        87
英语    95         李四  69     赵六        86
*/