1、几天前，终于修改了SQL查询语句，改为适应报表格式。其中最关键的就是用DECODE和CASE WHEN方式，只要查询结果正确，那么用这两种方式，基本上想要什么格式就可以拿到什么格式。

2、DECODE函数使用：

格式是  DECODE(表字段名，‘数值1’，‘数值2’，‘数值3’，‘数值4’，‘数值5’)。

具体含义：这个函数，实际作用的是，检查指定的表字段，如果字段值=数值1，那么就返回数值2，如果字段值=数值3，那么返回数值4，如果字段值不等于数值1也不等于数值3，那么就返回数值5 。也许前面的表述，说的不那么清楚，就请看下面的示例：

示例表student_arhievement：

student_name	student_arhievement
black	80
jhon	40
jack	80
pher	40
clear	80
ruini	70
pull	30
ada	30
boen	50

   select student_arhievement, decode(student_arhievement,'80','pass A','70','pass B','fail') from student_arhievement;

查询结果应该是：

black	pass A
jhon	fail
jack	pass A
pher	fail
clear	pass A
ruini	pass B
pull	fail
ada	fail
boen	fail

 

这个查询结果应该是很明显了，注意哈，这个查询结果，我是没有经过测试的，有可能出错，也有可能是对的。

我把查询语句改成：

select decode(student_arhievement,'80','pass','70','pass','fail'),count(*) from student_arhievement group by decode(student_arhievement,'80','pass','70','pass','fail') ;

查询结果应该是：

pass 4

fail   5

这里，我在画蛇添足一下，查询语句改成这样：

break on dummy;

compute sum label totalcount of count on dummy;

select null dummy,decode(student_arhievement,'80','pass','70','pass','fail'),count(*) count from student_arhievement group by decode(student_arhievement,'80','pass','70','pass','fail') ;

查询结果是：

             pass 4

             fail    5

total               9

这个可以给出一个汇总行，就是最后的total行，很不错的东西。

请注意最后的语句，这里有个技巧，就是那个null dummy，其实就是一个伪列，字段名为dummy，但是却没有值，所以pass行和fail行的前几个字段位置上全是空格，因为是NULL，当然就不显示了。经过实际测试，还要注意，dummy伪列的列宽度，因为没有值，所以SQLPLUS会将这个列的宽度定义为1个字符位置，结果导致显示如下：

       pass 4

       fail    5

t              9

就是最后的汇总行，只显示了一个T，其实就是TOTAL的第一个字符，因为列宽度不够，所以只显示T。办法就是在语句前面增加一个column dummy format a5就可以了。

关于BREAK和COMPUTR，我估计还需要另外一篇文章来专门写，内容比较多。

3、CASE WHEN函数

    相对来说，DECODE 函数已经很强大了，但是CASE WHEN函数还要强大的多。

DECODE 函数主要作用是，根据给定的值的要求，就是比如如果字段值是A，那么函数返回就是1，如果字段值是B，那么返回就是2，这个比较适合指定的字段值，有明显的少量基数，就是字段值比较像男女性别这个值，只有男、女或者未知等，但是如果指定的字段值，没有这样的基数，数值全是乱的，从1到100全有，那么用DECODE就很不方便了，这时候CASE WHEN就会方便很多。

CASE WHEN格式：

CASE WHEN 表达式 THEN 返回值1 ELSE 返回值2 END；

这个函数，有一部分功能是和DECODE函数一样的。

仔细看看下面两个：

decode(archivement ，'80’，'pass’，'fail’)

case when arvhivement='80' then 'pass' else 'fail' end

上面这两句，结果是一样的。但是CASE WHEN功能更强。

看下面的表studentu_archievement：

student_name	student_arhievement
black	81
jhon	43
jack	87
pher	35
clear	98
ruini	67
pull	49
ada	89
boen	61

这表中所有同学的成绩全部不一样，这时候用DECODE处理就会很麻烦，CASE WHEN很好用。可以这样写：

select case when student_archievement>60 then 'pass' else 'fail' end,count(*) from student_archievement

group by case when student_archievement>60 then 'pass' else 'nopass' end ;

查询结果：

pass 6

fail    3

这样处理起来就比DECODE要强多了，语句看起来也要简单的很多。

4、竖排变横排的作法

其实，这种竖排变横排的查询需求，有一部分是业务系统没有做好。

看示例表 student_archievement：

student_name	student_class	student_archievement
jack	Mathematics	98
jack	Chinese	67
jack	English	82
ada	Mathematics	78
ada	Chinese	89
ada	English	91
jhon	Mathematics	81
jhon	Chinese	85
jhon	English	79

   从某种意义上讲，这样的表结构很不理想，但是实际情况中，有很多这样的表结构。

现在需要把查询结果改成一行就是一个人的3门课的成绩，比如像这样：jack 98 67 82。

处理办法可以这样写：

select student_name,

sum(decode(student_class,'Mathematics',student_archievement)) Mathematics,

sum(decode(student_class,'Chinese',student_archievement)) Chinese,

sum(decode(student_class,'English',student_archievement)) English

from student_archievement

group by student_name;

这句话的执行结果就是：

student_name	Mathematics	Chinese	English
jack	98	67	82
ada	78	89	91
jhon	81	85	79

   这样的查询结果，看起来就会舒服很多。

因为文章中的示例数据太过简单了，所以没有办法记录更复杂的计算办法，这里就只写一些相对简单的，以后再进一步添加。

我这里只用到了DECODE函数，当然还是可以用CASE WHEN来做，只是CASE WHEN语句相对复杂点，长度较长而已