sql中使用if多条件判断

1、以一表为例：bill 单据表

--------------------------------------------------------------------------

id         billno           status        amount            create_date

-------------------------------------------------------------------------

1         GR0001          GR               155               2009-09-09

2         DO0001          DO              150               2009-09-09

2         SO0001          SO               153               2009-09-09

-------------------------------------------------------------------------

说明:

      status:GR表示“进货单”

      status:DO表示“出货单”

      status:SO表示“销售单”

 

2、实现按日期统计进货、销售、出货,

针对mysql使用如下sql

select create_date,
 sum(if(status='GR',amount,0)) as am1,
 sum(if(status='DO',amount,0)) as am2,
 sum(if(status='SO',amount,0)) as am3
from bill group by create_date

针对oracle使用如下sql

 

select create_date,
 sum(decode(status,'GR',amount,0)) as am1,
 sum(decode(status,'DO',amount,0)) as am2,
 sum(decode(status,'SO',amount,0)) as am3
from bill group by create_date

 

 

3、效果如下

-----------------------------------------------------

create_date          am1        am2        am3

-----------------------------------------------------

2009-09-09           155         150         153