mysql语句实现if多条件判断查询_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

分享到：

2009-09-09 13:03

浏览 6000

评论