ORACLE，DECODE函数和排名函数DENSE_RANK函数的使用

这几天写一个报表的页面，从很恶心的数据结构中做一个聚合函数的查询，结构大概是这个样子的：

 

所以有：对数据group by t.id,t.name.t.course

这样三层排序，然后用函数去取值。

• decode（feildname,'Match_value','value1','value2'）   -- 用字段名称去跟Match_value做对比，对比成功，返回value1  否则返回 value1 

这里把decode（）放在group by 的select内部，同聚合函数一起使用，计算对应值。

很明显，这里得到的数据结构并不是我想要的样子，但是以目前水平暂时只能做到这里了。后边的放在js里处理的。

• 排名函数：dense_rank

这个用法其实也没太搞明白，应该是将数据集根据条件排序，但是存在排序的排名方式，这个dense_rank还有rank()和row_number()三个函数不同之处见：http://blog.csdn.net/winer2008/article/details/4283539

 

下边贴上测试用的所有代码：

• 表的创建修改，插入数据。

  6 create table KECHENG
  7 (
  8   id       NUMBER,
  9   name     VARCHAR2(20),
 10   course   VARCHAR2(20),
 11   score    NUMBER,
 12   examtime DATE,
 13   test1    VARCHAR2(50)
 14 )
 15 tablespace THRONESPACE
 16   pctfree 10
 17   initrans 1
 18   maxtrans 255
 19   storage
 20   (
 21     initial 64K
 22     next 1M
 23     minextents 1
 24     maxextents unlimited
 25   );
 26 
 33 insert into KECHENG (id, name, course, score, examtime, test1)
 34 values (1, '张三', '语文', 657, to_date('12-07-2017 05:00:00', 'dd-mm-yyyy hh24:mi:ss'), '22');
 35 insert into KECHENG (id, name, course, score, examtime, test1)
 36 values (2, '张三', '数学', 476, to_date('11-07-2017 00:07:00', 'dd-mm-yyyy hh24:mi:ss'), '22ddd');
 37 insert into KECHENG (id, name, course, score, examtime, test1)
 38 values (2, '张三', '历史', 564, to_date('26-07-2017 04:00:00', 'dd-mm-yyyy hh24:mi:ss'), '22');
 39 insert into KECHENG (id, name, course, score, examtime, test1)
 40 values (2, '李四', '语文', 534, to_date('26-07-2017 19:02:00', 'dd-mm-yyyy hh24:mi:ss'), '22');
 41 insert into KECHENG (id, name, course, score, examtime, test1)
 42 values (2, '李四', '数学', 81, to_date('26-07-2017 17:57:00', 'dd-mm-yyyy hh24:mi:ss'), '22');
 43 insert into KECHENG (id, name, course, score, examtime, test1)
 44 values (2, '李四', '历史', 933, to_date('20-07-2017 03:57:00', 'dd-mm-yyyy hh24:mi:ss'), '22');
 45 insert into KECHENG (id, name, course, score, examtime, test1)
 46 values (3, '王五', '语文', 264, to_date('23-07-2017 05:03:00', 'dd-mm-yyyy hh24:mi:ss'), '22');
 47 insert into KECHENG (id, name, course, score, examtime, test1)
 48 values (3, '王五', '数学', 25, to_date('10-07-2017 03:00:00', 'dd-mm-yyyy hh24:mi:ss'), '22');
 49 insert into KECHENG (id, name, course, score, examtime, test1)
 50 values (3, '王五', '历史', 45, to_date('03-07-2017 11:00:00', 'dd-mm-yyyy hh24:mi:ss'), '22');
 51 insert into KECHENG (id, name, course, score, examtime, test1)
 52 values (2, '张三', '语文', 667, to_date('12-07-2017 05:00:00', 'dd-mm-yyyy hh24:mi:ss'), '22');
 53 insert into KECHENG (id, name, course, score, examtime, test1)
 54 values (1, '张三', '数学', 766, to_date('11-07-2017 00:09:00', 'dd-mm-yyyy hh24:mi:ss'), '22');
 55 insert into KECHENG (id, name, course, score, examtime, test1)
 56 values (2, '张三', '历史', 546, to_date('26-07-2017 04:03:00', 'dd-mm-yyyy hh24:mi:ss'), '22');
 57 insert into KECHENG (id, name, course, score, examtime, test1)
 58 values (2, '李四', '语文', 54, to_date('26-07-2017 19:00:00', 'dd-mm-yyyy hh24:mi:ss'), '22');
 59 insert into KECHENG (id, name, course, score, examtime, test1)
 60 values (1, '李四', '数学', 821, to_date('26-07-2017 17:00:00', 'dd-mm-yyyy hh24:mi:ss'), '22');
 61 insert into KECHENG (id, name, course, score, examtime, test1)
 62 values (2, '李四', '历史', 93, to_date('20-07-2017 03:00:00', 'dd-mm-yyyy hh24:mi:ss'), '22');
 63 insert into KECHENG (id, name, course, score, examtime, test1)
 64 values (3, '王五', '语文', 254, to_date('23-07-2017 05:00:00', 'dd-mm-yyyy hh24:mi:ss'), '22');
 65 insert into KECHENG (id, name, course, score, examtime, test1)
 66 values (3, '王五', '数学', 245, to_date('10-07-2017 03:58:00', 'dd-mm-yyyy hh24:mi:ss'), '22');
 67 insert into KECHENG (id, name, course, score, examtime, test1)
 68 values (3, '王五', '历史', 454, to_date('03-07-2017 10:00:00', 'dd-mm-yyyy hh24:mi:ss'), '22');
 69 insert into KECHENG (id, name, course, score, examtime, test1)
 70 values (1, '张三', '语文', 677, to_date('12-07-2017 05:02:00', 'dd-mm-yyyy hh24:mi:ss'), '22');
 71 insert into KECHENG (id, name, course, score, examtime, test1)
 72 values (1, '张三', '数学', 776, to_date('11-07-2017 00:07:00', 'dd-mm-yyyy hh24:mi:ss'), '22');
 73 insert into KECHENG (id, name, course, score, examtime, test1)
 74 values (1, '张三', '历史', 56, to_date('26-07-2017 05:00:00', 'dd-mm-yyyy hh24:mi:ss'), '22');
 75 insert into KECHENG (id, name, course, score, examtime, test1)
 76 values (2, '李四', '语文', 564, to_date('26-07-2017 21:00:00', 'dd-mm-yyyy hh24:mi:ss'), '22');
 77 insert into KECHENG (id, name, course, score, examtime, test1)
 78 values (1, '李四', '数学', 481, to_date('26-07-2017 17:02:00', 'dd-mm-yyyy hh24:mi:ss'), '22');
 79 insert into KECHENG (id, name, course, score, examtime, test1)
 80 values (1, '李四', '历史', 923, to_date('20-07-2017 03:00:00', 'dd-mm-yyyy hh24:mi:ss'), '22');
 81 insert into KECHENG (id, name, course, score, examtime, test1)
 82 values (3, '王五', '语文', 24, to_date('23-07-2017 05:08:00', 'dd-mm-yyyy hh24:mi:ss'), '22');
 83 insert into KECHENG (id, name, course, score, examtime, test1)
 84 values (3, '王五', '数学', 265, to_date('10-07-2017 03:02:00', 'dd-mm-yyyy hh24:mi:ss'), '22');
 85 insert into KECHENG (id, name, course, score, examtime, test1)
 86 values (3, '王五', '历史', 465, to_date('03-07-2017 10:05:00', 'dd-mm-yyyy hh24:mi:ss'), '22');
 87 insert into KECHENG (id, name, course, score, examtime, test1)
 88 values (1, '张三', '语文', 767, to_date('12-07-2017 07:00:00', 'dd-mm-yyyy hh24:mi:ss'), '22');
 89 insert into KECHENG (id, name, course, score, examtime, test1)
 90 values (1, '张三', '数学', 276, to_date('11-07-2017 01:07:00', 'dd-mm-yyyy hh24:mi:ss'), '22');
 91 insert into KECHENG (id, name, course, score, examtime, test1)
 92 values (1, '张三', '历史', 566, to_date('26-07-2017 04:00:00', 'dd-mm-yyyy hh24:mi:ss'), '22');
 93 insert into KECHENG (id, name, course, score, examtime, test1)
 94 values (2, '李四', '语文', 454, to_date('26-07-2017 19:02:00', 'dd-mm-yyyy hh24:mi:ss'), '22');
 95 insert into KECHENG (id, name, course, score, examtime, test1)
 96 values (2, '李四', '数学', 81, to_date('26-07-2017 17:00:00', 'dd-mm-yyyy hh24:mi:ss'), '22');
 97 insert into KECHENG (id, name, course, score, examtime, test1)
 98 values (2, '李四', '历史', 953, to_date('20-07-2017 03:02:00', 'dd-mm-yyyy hh24:mi:ss'), '22');
 99 insert into KECHENG (id, name, course, score, examtime, test1)
100 values (1, '王五', '语文', 244, to_date('23-07-2017 05:00:00', 'dd-mm-yyyy hh24:mi:ss'), '22');
101 insert into KECHENG (id, name, course, score, examtime, test1)
102 values (2, '王五', '数学', 425, to_date('10-07-2017 03:00:00', 'dd-mm-yyyy hh24:mi:ss'), '22');
103 insert into KECHENG (id, name, course, score, examtime, test1)
104 values (3, '王五', '历史', 45, to_date('03-07-2017 10:02:00', 'dd-mm-yyyy hh24:mi:ss'), '22');
105 commit;

 

•  查询用的sql

 1 select t.id,
 2        t.name,
 3        t.course,
 4        max(decode(t.course, '语文', score, 0)) yuwenmax,
 5        min(decode(t.course, '语文', score, 0)),
 6        round(avg(decode(t.course, '语文', score, 0)), 4),
 7        max(decode(t.course, '历史', score, 0)),
 8        min(decode(t.course, '历史', score, 0)),
 9        round(avg(decode(t.course, '历史', score, 0)), 4),
10        max(decode(t.course, '数学', score, 0)),
11        min(decode(t.course, '数学', score, 0)),
12        round(avg(decode(t.course, '数学', score, 0)), 4),
13        decode(t.course,
14               '数学' ，min(t.score) KEEP(dense_rank first ORDER BY t.examtime),
15               0)  as start_value,                                                                     --这里取起始值，结束值和增量
16        decode(t.course,
17               '数学' ，min(t.score) KEEP(dense_rank last ORDER BY t.examtime),
18               0) as end_value,
19        decode(t.course,
20               '数学' ，min(t.score) KEEP(dense_rank last ORDER BY t.examtime),
21               0) -
22        decode(t.course,
23               '数学' ，min(t.score) KEEP(dense_rank first ORDER BY t.examtime),
24               0) as minus_value
25   from kecheng t
26  group by t.id, t.name, t.course
27  order by t.id,
28           t.name

 

转载于:https://www.cnblogs.com/-THE-END/p/7247359.html