日期:2014-05-17 浏览次数:20553 次
create table Student(S# varchar(10),Sname varchar(10),Sage int,Ssex char(2))
create table Course(C# varchar(10),Cname varchar(10),T# varchar(10))
create table SC(S# varchar(10),C# varchar(10),score int)
insert into Student values ('S001','张三',15,'男')
insert into Student values ('S002','李四',16,'女')
insert into Student values ('S003','王五',15,'男')
insert into Student values ('S004','赵六',14,'女')
insert into Course values ('M','数学','T001')
insert into Course values ('C','语文','T002')
insert into SC values ('S001','M',78)
insert into SC values ('S001','C',98)
insert into SC values ('S002','M',98)
insert into SC values ('S002','C',79)
insert into SC values ('S003','M',67)
insert into SC values ('S003','C',89)
insert into SC values ('S004','M',66)
insert into SC values ('S004','C',88)
select T2.*,T1.* from
(
select A.S#,SUM(B.score) as 总分,Rank() over (order by sum(B.score) desc) as 总分排名
from Student A inner join SC B on A.S#=B.S# inner join Course C on B.C#=C.C#
group by A.S#
) T1,
(
select A.S#,A.Sname,C.Cname,B.score
from Student A inner join SC B on A.S#=B.S# inner join Course C on B.C#=C.C#
)T2
where T1.S#=T2.S# order by T1.总分 desc
S# Sname Cname score S# 总分 总分排名
---------- ---------- ---------- ----------- ---------- ----------- --------------------
S002 李四 数学 98 S002 177 1
S002 李四 语文 79 S002 177 1
S001 张三 数学 78