将高考成绩流水表转化成高考成绩汇总表

高考成绩批阅后会形成这样的记录：

学号	科目id	分数
231	1	99
34	2	128
8799	3	130
...	...	...
9912	5	105

这样的数据统合上来，就会形成高考成绩流水表：

SQL> select * from gk_score where rownum<10;

        ID  SUBJECTID      STUID      SCORE
---------- ---------- ---------- ----------
      1083          1      46110         26
      1084          1      17309        118
      1085          1      82287        144
      1086          1      25989        108
      1087          1      40912         61
      1088          1      38971        137
      1089          1      88624        118
      1090          1       8646        112
      1091          1      23097        130

已选择9行。

这只是十万行中的九行，九牛一毛而已。要问这个数据是怎么创建出来的，请参考：https://www.cnblogs.com/xiandedanteng/p/12310596.html

 

当然这个流水表不好直接拿给学生家长看的，因为太费劲了，排名也不方便，因此我们需要创建一张汇总表，即把学生总分和单科分都列出来的汇总表，比如下面这种：

id	学号	总分	语文	数学	英语	物理	化学
1	332	465	126	33	89	86	131
2	56	363	9	77	62	112	103
3	3002	415	3	100	51	129	132
...	...	...	...	...	...	...	..

要由成绩流水表得到成绩汇总表，可以按以下步骤来。

这条SQL语句能按学号分组，然后把总分求和，并把单科分汇合到一个字段里：

select stuid,sum(score) as summary,listagg(lpad(score,3,'0'),',') within group (order by subjectid) as sbjs from gk_score group by stuid

其中lprad是位数不足左侧补零，listagg则是行转列分析函数，不熟悉这两个函数的同学请自行百度一下。

然后用个括号给它包一下，目的是加上行号。

select rownum as rn,tb1.stuid,tb1.summary,tb1.sbjs from
(
    select stuid,sum(score) as summary,listagg(lpad(score,3,'0'),',') within group (order by subjectid) as sbjs from gk_score group by stuid
) tb1

接下来创建目标，高考成绩汇总表：

create table gk_summary(
   id number(8,0) primary key,
   stuid number(8,0) not null,
   summary number(3,0) not null,
   chinese number(3,0) not null,
   math number(3,0) not null,
   english number(3,0) not null,
   physics number(3,0) not null,
   chemistry number(3,0) not null
)

加下来就是激动人心的时刻了：

insert into gk_summary(id,stuid,summary,chinese,math,english,physics,chemistry)
select tb2.rn,
       tb2.stuid,
       tb2.summary,
       to_number(substr(tb2.sbjs,1,3)),
       to_number(substr(tb2.sbjs,5,3)),
       to_number(substr(tb2.sbjs,9,3)),
       to_number(substr(tb2.sbjs,13,3)),
       to_number(substr(tb2.sbjs,17,3))
from 
(
    select rownum as rn,tb1.stuid,tb1.summary,tb1.sbjs from
    (
        select stuid,sum(score) as summary,listagg(lpad(score,3,'0'),',') within group (order by subjectid) as sbjs from gk_score group by stuid
    ) tb1
) tb2

注意上面出现了两个字符串处理函数用于把单科成绩从sbjs字段里抽出来，substr是截取字符串，第一参数是目标字符串，第二参数是起始位置，比较坑的是下标是从1起，0与1等效，第三参数是要截取的长度；to_number是把字符串转为数字。

插入很快就完毕了，记得先commit一下，让我们来看看结果：

SQL> select * from gk_summary where rownum<10;

        ID      STUID    SUMMARY    CHINESE       MATH    ENGLISH    PHYSICS  CHEMISTRY
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
     34968      34968        465        126         33         89         86        131
     34969      34969        363          9         77         62        112        103
     34970      34970        253         31         41          8         90         83
     34971      34971        239         12         69        125          2         31
     34972      34972        414         73         69        135         88         49
     34973      34973        382         76         83        145         22         56
     34974      34974        463         94         66        108        129         66
     34975      34975        415          3        100         51        129        132
     34976      34976        375          9        149        123         26         68

已选择9行。

有了汇总表gk_summary后，方便办的事情就很多了，比如找出那些总分前十的尖子：

select * from 
(
    select rownum as rn,tb.* from
    (select * from gk_summary order by summary desc) tb
) tb1
where tb1.rn<11

SQL> select * from
  2  (
  3  select rownum as rn,tb.* from
  4  (select * from gk_summary order by summary desc) tb
  5  ) tb1
  6  where tb1.rn<11;

        RN         ID      STUID    SUMMARY    CHINESE       MATH    ENGLISH    PHYSICS  CHEMISTRY
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1      46332      46332        712        146        138        145        150        133
         2      67468      67468        707        147        145        123        147        145
         3      85204      85204        702        124        148        147        138        145
         4      93904      93904        701        147        144        145        143        122
         5      95589      95589        699        139        146        132        142        140
         6       6023       6023        699        143        147        138        141        130
         7      93667      93667        699        145        122        141        148        143
         8      92032      92032        698        146        125        148        144        135
         9      73123      73123        697        129        143        147        148        130
        10      21336      21336        697        146        147        138        119        147

已选择10行。

这个数据，大家可以拿去和 https://www.cnblogs.com/xiandedanteng/p/12310661.html 最后求出的数据比较一下。

--2020年2月15日--

貌似还有方法达成目标，比如下面的采用标量子查询做的：

insert into gk_summary(id,stuid,summary,chinese,math,english,physics,chemistry)
select rownum as rn,
       tb1.stuid,
       tb1.summary,
       (select gs1.score from gk_score gs1 where gs1.subjectid=1 and gs1.stuid=tb1.stuid) as chinese,
       (select gs2.score from gk_score gs2 where gs2.subjectid=2 and gs2.stuid=tb1.stuid) as math,
       (select gs3.score from gk_score gs3 where gs3.subjectid=3 and gs3.stuid=tb1.stuid) as english,
       (select gs4.score from gk_score gs4 where gs4.subjectid=4 and gs4.stuid=tb1.stuid) as physics,
       (select gs5.score from gk_score gs5 where gs5.subjectid=5 and gs5.stuid=tb1.stuid) as chemistry       
from (
       select stuid,sum(score) as summary from gk_score group by stuid order by summary desc
) tb1
order by tb1.summary desc

大家可以试试这种貌似简单的查询执行起来究竟要多长时间。：-）