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
评论