万里挑一进清北

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

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

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日--

 

posted @ 2020-02-15 09:12  逆火狂飙  阅读(163)  评论(1)    收藏  举报
生当作人杰 死亦为鬼雄 至今思项羽 不肯过江东