求合格人数及不合格人数。

表结构：

SQL> desc t_mms_loan_pratc_train;
Name               Type          Nullable Default Comments
------------------ ------------- -------- ------- --------
PAPER_ID           VARCHAR2(32)                           
TLR_ID             VARCHAR2(7)   Y                        
TLR_NM             VARCHAR2(32)  Y                        
TRAIN_PAPER_ID     VARCHAR2(32)  Y                        
TRAIN_DT           VARCHAR2(10)  Y                        
TRAIN_ADDR         VARCHAR2(256) Y                        
TRAIN_CONTENT      VARCHAR2(256) Y                        
SCORE              NUMBER(16,2)  Y                        
TRAIN_EVL          VARCHAR2(256) Y                        
LAST_MODIFY_TLR_ID VARCHAR2(9)   Y                        
LAST_MODIFY_PRG_ID VARCHAR2(60)  Y                        
LAST_MODIFY_TM     VARCHAR2(19)  Y

 

前10条数据：

SQL> select tlr_id,score,train_evl from t_mms_loan_pratc_train where rownum<=10;

TLR_ID               SCORE TRAIN_EVL
------- ------------------ --------------------------------------------------------------------------------
B010224             221.00 良好
B010225             222.00 良好
B010226             223.00 良好
B010227             224.00 良好
B010228             225.00 良好
B010229             226.00 良好
B010230             227.00 良好
B010231             228.00 良好
B010232             229.00 良好
B010233             230.00 良好

 

 

在此表里面查询出合格人数以及不合格人数：

可以使用oracle里面的decode函数和sign函数。

decode()函数是ORACLE PL/SQL是功能强大的函数之一，目前还只有ORACLE公司的SQL提供了此函数，其他数据库厂商的SQL实现还没有此功能。

      DECODE函数是ORACLE PL/SQL是功能强大的函数之一，目前还只有ORACLE公司的SQL提供了此函数，其他数据库厂商的SQL实现还没有此功能。DECODE有什么用途呢？ 先构造一个例子，假设我们想给智星职员加工资，其标准是：工资在8000元以下的将加20％；工资在8000元以上的加15％，通常的做法是，先选出记录中的工资字段值? select salary into var-salary from employee，然后对变量var-salary用if-then-else或choose case之类的流控制语句进行判断。 如果用DECODE函数，那么我们就可以把这些流控制语句省略，通过SQL语句就可以直接完成。如下：select decode(sign(salary - 8000),1,salary*1.15,-1,salary*1.2,salary from employee 是不是很简洁？ DECODE的语法：DECODE(value,if1,then1,if2,then2,if3,then3,...,else)，表示如果value等于if1时，DECODE函数的结果返回then1,...,如果不等于任何一个if值，则返回else。初看一下，DECODE 只能做等于测试，但刚才也看到了，我们通过一些函数或计算替代value，是可以使DECODE函数具备大于、小于或等于功能。

 

decode()函数使用技巧

　　·含义解释:

　　decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)

　　该函数的含义如下:

　　IF 条件=值1 THEN

　　RETURN(翻译值1)

　　ELSIF 条件=值2 THEN

　　RETURN(翻译值2)

　　......

　　ELSIF 条件=值n THEN

　　RETURN(翻译值n)

　　ELSE

　　RETURN(缺省值)

　　END IF

　　· 使用方法:

　　1、比较大小

　　select decode(sign(变量1-变量2),-1,变量1,变量2) from dual; --取较小值

　　sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1

　　例如:

　　变量1=10，变量2=20

　　则sign(变量1-变量2)返回-1，decode解码结果为“变量1”，达到了取较小值的目的。

 

 

因此可以得到如此的查询语句：

select sum(decode(sign(score-60),-1,1,0))as 不合格人数,
sum(decode(sign(score-60),1,1,0))as 合格人数
from t_mms_loan_pratc_train

 

结果：

不合格人数   合格人数
---------- ----------
        10        203

 

发现不合格人数少了。看表数据才知道，有些score数据是空，当score为空时，找不出来同时当score==60时也出不来。这样写不行。

 

 

不过要改进！

改进后：

select sum(decode(sign(score-60),-1,1))as 不合格人数,
sum(decode(sign(score-60),1,1,0,1))as 合格人数
from t_mms_loan_pratc_train

结果：

不合格人数   合格人数
---------- ----------
        10        204

呵呵。。合格人数正确罗，不过不合格人数还是不正确。

 

使用另一种方法：

case when then.

select sum(case when score>=60 then 1 end) as 合格人数,
       sum(case when (score<60 or score is null) then 1 end)as 不合格人数
from t_mms_loan_pratc_train

 

结果：

  合格人数 不合格人数
---------- ----------
       204         11

这才正确。