刚接触SQL,请大家指点下,感激不尽现有表
Grade 学生成绩表
create table grade
(
student_name char(8) not null, -- 学生姓名
subject char(8) not null, --课目
grade float not null --成绩
primary key(student_name,subject)
)
问题如下:
1。求出总分排在 第三名 的学生和总分。
2。列出前三名的学生与分别与前一名的学生的分差(请基于SQL SERVER 2005去完成调试出结果)
如:
student_name ,总分, 与前一名的分差
张三 200 0
李四 190 10
王八 120 70
Grade 学生成绩表
create table grade
(
student_name char(8) not null, -- 学生姓名
subject char(8) not null, --课目
grade float not null --成绩
primary key(student_name,subject)
)
问题如下:
1。求出总分排在 第三名 的学生和总分。
2。列出前三名的学生与分别与前一名的学生的分差(请基于SQL SERVER 2005去完成调试出结果)
如:
student_name ,总分, 与前一名的分差
张三 200 0
李四 190 10
王八 120 70
1:第一条SQL语句如下:
select student_name,Total from
(
select * ,dense_rank()over(Order by T.Total Desc)as [order] from
(
select student_name ,sum(grade)as Total from grade group by student_name
) as T
)as D
where D.[order]=3
第二个不会!
go
create table [grade]([student_name] varchar(4),[subject] char(8),[grade] int)
insert [grade]
select '张三','1',200 union all
select '李四','2',190 union all
select '王八','3',120
go
--select * from [grade]with t1 as
(select student_name,total=sum(grade) from grade group by student_name)
,t2 as
(select topGrade=max(total) from t1)
select student_name,total,dis=topGrade-total
from t1,t2
order by total desc
/*
student_name total dis
------------ ----------- -----------
张三 200 0
李四 190 10
王八 120 80(3 行受影响)
*/
--第一题
select student_name,total from(
select student_name,sum(grade) as total,row_number() over(order by sum(grade) desc) as rid from Grade
group by student_name) tmp where rid=3--第二题
;with list as
(
select top 3 * from(
select student_name,sum(grade) as total,row_number() over(order by sum(grade) desc) as rid from Grade
group by student_name
)tmp order by rid
),
numberone as
(
select top 1 total as score from list
)select list.*, score-total as discrepancy from list,numberone
;with list as
(
select top 3 * from(
select student_name,sum(grade) as total,row_number() over(order by sum(grade) desc) as rid from Grade
group by student_name
)tmp order by rid
)select a.*,isnull(b.total,a.total) - a.total as discrepancy from list a left join list b on a.rid=b.rid+1