再求SQL语句...关于生成流水号

Question

有如下表 T1:班级     姓名     科目     成绩      
一班     张三     语文      70
一班     张三     数学      73
一班     李四     语文      85
一班     李四     数学      72
二班     王五     语文      87
二班     王五     数学      90
二班     赵六     语文      65
二班     赵六     数学      68
二班     陈七     语文      83
二班     陈七     数学      67欲得到如下结果,要求:
(1) 按班级,姓名汇总算总分
(2) 按班级根据总分由高到低生成由小到大的流水号作为排名
(3) 对于同班有总分相同者 则尝试两种写法: 
             1.同分者则按姓名先后顺序排名,无并列排名.
             2.排名并列,总分紧随其后者,排名往后顺延一位.
排名       班级     姓名     总分
1          一班     李四     157
2          一班     张三     143
1          二班     王五     177
2          二班     陈七     150
3          二班     赵六     133

Answer 1

with tb as (
select '一班' "班级",'张三' "姓名",'语文' "科目",70 "成绩" from dual union all
select '一班', '张三', '数学', 73 from dual union all
select '一班', '李四', '语文', 85 from dual union all
select '一班', '李四', '数学', 72 from dual union all
select '一班', 'Tom', '语文', 85 from dual union all
select '一班', 'Tom', '数学', 72 from dual union all
select '二班', '王五', '语文', 87 from dual union all
select '二班', '王五', '数学', 90 from dual union all
select '二班', '赵六', '语文', 65 from dual union all
select '二班', '赵六', '数学', 68 from dual union all
select '二班', '陈七', '语文', 83 from dual union all
select '二班', '陈七', '数学', 67 from dual)
select rank() over(partition by "班级" order by "总分" desc) "排名",
       "班级","姓名","总分"
from
        (select "班级","姓名",sum("成绩") "总分"
        from tb group by "班级","姓名")
order by "班级" desc--Tom和李四的总分相同都排为1 张三排为3      排名 班级 姓名       总分
---------- ---- ---- ----------
         1 一班 Tom         157
         1 一班 李四        157
         3 一班 张三        143
         1 二班 王五        177
         2 二班 陈七        150
         3 二班 赵六        133

Answer 2

这种写法在PL/SQL下执行没有问题，但不知道应用程序ODBC或者JDBC驱动是否能认，要试试看。

Answer 3

--Oracle分析函数 row_number()、rank()、dense_rank()可以解决
with tab as (
select '一班' 班级,'张三' 姓名,'语文' 科目,70 成绩 from dual union all
select '一班', '张三', '数学', 73 from dual union all
select '一班', '李四', '语文', 85 from dual union all
select '一班', '李四', '数学', 72 from dual union ALL
select '一班', 'tom', '语文', 85 from dual union all
select '一班', 'tom', '数学', 72 from dual union all
select '二班', '王五', '语文', 87 from dual union all
select '二班', '王五', '数学', 90 from dual union all
select '二班', '赵六', '语文', 65 from dual union all
select '二班', '赵六', '数学', 68 from dual union all
select '二班', '陈七', '语文', 83 from dual union all
select '二班', '陈七', '数学', 67 from dual
)
SELECT Row_Number()over(partition by 班级 order by 总分 desc,姓名 ) 排名1,
rank() over(partition by 班级 order by 总分 desc) 排名2,
班级,姓名,总分
FROM (
SELECT 班级,姓名,Sum(成绩) 总分 FROM tab group by 班级,姓名)
ORDER BY 班级
排名1 排名2  班级  姓名 总分
-------------------------------
1 1 一班 tom  157
2 1 一班 李四 157
3 3 一班 张三 143      --注意这笔排名
1 1 二班 王五 177
2 2 二班 陈七 150
3 3 二班 赵六 133

Answer 4

select 班级,姓名,成绩,
rank() over(partition by 班级 order by 成绩 desc,name) rn1, --并列排名跳号
dense_rank() over(partition by 班级 order by 成绩 desc,name) rn2, ---并列排名不跳号
row_number() over(partition by 班级 order by 成绩 desc,name) rn3
from
(select 班级,姓名,sum(成绩) 成绩
from tb group by 班级,姓名)