oracle decode()函数

不了解技术的我，今天学到了decode()函数的运用。

 

要求如下：

对数据库中

set feedback off
set define off
prompt Creating RACE...
create table RACE
(
  RACEDATE NUMBER,
  RESULT   VARCHAR2(10)
)
;

prompt Loading RACE...
insert into RACE (RACEDATE, RESULT)
values (20090716, '胜');
insert into RACE (RACEDATE, RESULT)
values (20090716, '负');
insert into RACE (RACEDATE, RESULT)
values (20090717, '胜');
insert into RACE (RACEDATE, RESULT)
values (20090717, '负');
insert into RACE (RACEDATE, RESULT)
values (20090717, '胜');
insert into RACE (RACEDATE, RESULT)
values (20090717, '负');
insert into RACE (RACEDATE, RESULT)
values (20090716, '胜');
commit;
prompt 7 records loaded
set feedback on
set define on
prompt Done.

写SQL语句，输出结果：

racedate     胜     负

20090716   2      1

20090717   2      2

 

标准答案：

参考答案1：(大小写均可)

Select racedate,

       sum(decode(result, '胜', 1)) as "胜",

       sum(decode(result, '负', 1)) as "负"

from race

group by racedate

order by racedate;

参考答案2：

SELECT RACEDATE,  

  SUM(CASE WHEN RESULT = '胜' THEN 1 ELSE 0 END) AS 胜,  

  SUM(CASE WHEN RESULT = '负' THEN 1 ELSE 0 END) AS 负  

FROM RACE   

GROUP BY RACEDATE   

ORDER BY RACEDATE;

 

有个人写了个嵌套循环，居然没出错，还是牛：

select y.racedate,sum(y.a) 胜, sum(y.b) 负 from
 (select x.racedate, decode (x.result,'胜',x.times,0) a,decode (x.result,'负',x.times,0) b  from
          (select racedate,result,count(1) times from race
                     group by racedate,result) x) y
                           group by y.racedate

 

学习：

1、select 是按记录一行一行的显示，选择哪几个字段，就group by哪几个字段。

2、sum与group by结合着用。

3、如果不用sum和group by，decode每条记录都返回一个值。用了sum后，则对这些值求和后返回。