万里挑一进清北

清华北大在某地选尖子大约万分之一的比例，比如某年大连市有十万考生，那么能进清北就十个左右！

如果我们现在有了一张考生成绩表，这张表里记录了十万考生语数外理化五门功课的考试成绩，结构如下：

create table gk_score(
   id number(8,0) primary key,
   subjectid number(1,0) not null,
   stuid number(8,0) not null,
   score number(3,0) not null
)

要问怎么给它充值的，请看这里。

 

现在我们的任务就是要找出那十个幸运儿，找的依据就是五门功课总分最高的十个人。

下面的语句能把所有考生的五门成绩汇总并按从高到低排序：

select stuid,sum(score) as summary from gk_score group by stuid order by summary desc

然后我们要利用rownum，给高分到低分排个名次：

select rownum as rn,tb1.stuid,tb1.summary from (
select stuid,sum(score) as summary from gk_score group by stuid order by summary desc
) tb1
order by tb1.summary desc

排好名次后，只要取出前十个就好了：

select * from 
(
select rownum as rn,tb1.stuid,tb1.summary from (
select stuid,sum(score) as summary from gk_score group by stuid order by summary desc
) tb1
order by tb1.summary desc
) tb2
where rn<11

让我们看看这十个幸运儿是谁：

SQL> SELECT *
  2  FROM (
  3  SELECT rownum AS rn, tb1.stuid, tb1.summary
  4  FROM (
  5  SELECT stuid, SUM(score) AS summary
  6  FROM gk_score
  7  GROUP BY stuid
  8  ORDER BY summary DESC
  9  ) tb1
 10  ORDER BY tb1.summary DESC
 11  ) tb2
 12  WHERE rn < 11;

        RN      STUID    SUMMARY
---------- ---------- ----------
         1      46332        712
         2      67468        707
         3      85204        702
         4      93904        701
         5      93667        699
         6      95589        699
         7       6023        699
         8      92032        698
         9      21336        697
        10      73123        697

已选择10行。

总分都这么高，平均都接近一百四了，清北学子真是万里挑一的人尖子啊。

--2020年2月15日--