select cd.cust_code,sp.currency,
case when cd.eta>=to_date('2011-01-01','yyyy-mm-dd') and cd.eta<to_date('2011-02-01','yyyy-mm-dd') then sum(cd.cfc_qty*sp.price) as Jan_Amount
when cd.eta>=to_date('2011-02-01','yyyy-mm-dd') and cd.eta<to_date('2011-03-01','yyyy-mm-dd') then sum(cd.cfc_qty*sp.price) as Feb_Amount
when cd.eta>=to_date('2011-03-01','yyyy-mm-dd') and cd.eta<to_date('2011-04-01','yyyy-mm-dd') then sum(cd.cfc_qty*sp.price) as Mar_Amount
else 0
end as cd.cfc_qty*sp.price
from cfc_detail cd,selling_price sp
where sp.cust_code =cd.cust_code
group by cd.cust_code,
sp.currency
order by cd.cust_code
我要按客户查询 一月 ,二月和三月的销售额 以上sQl有错,求正解!
输出格式为
cust_code currency Jan_Amount Feb_Amount Mar_Amount
case when cd.eta>=to_date('2011-01-01','yyyy-mm-dd') and cd.eta<to_date('2011-02-01','yyyy-mm-dd') then sum(cd.cfc_qty*sp.price) as Jan_Amount
when cd.eta>=to_date('2011-02-01','yyyy-mm-dd') and cd.eta<to_date('2011-03-01','yyyy-mm-dd') then sum(cd.cfc_qty*sp.price) as Feb_Amount
when cd.eta>=to_date('2011-03-01','yyyy-mm-dd') and cd.eta<to_date('2011-04-01','yyyy-mm-dd') then sum(cd.cfc_qty*sp.price) as Mar_Amount
else 0
end as cd.cfc_qty*sp.price
from cfc_detail cd,selling_price sp
where sp.cust_code =cd.cust_code
group by cd.cust_code,
sp.currency
order by cd.cust_code
我要按客户查询 一月 ,二月和三月的销售额 以上sQl有错,求正解!
输出格式为
cust_code currency Jan_Amount Feb_Amount Mar_Amount
解决方案 »
- hibernateTemplate.save(obj)的问题,急 急
- 传说中的Exception in threads "main" java.lang.NoClassDefFoundError: 试了n种方法了,求救.....
- Hibernate重新加载配置文件
- java工作流开发用什么开源项目比较好??
- 谁给我发个oralce的视频教程到[email protected] 谢谢
- struts问题:各位大侠帮忙诊断一下--在线等待中
- servlet上下文是不是就是webapps那个目录啊!?
- 求struts in action书中源代码 散分
- 求《Struts in action》中文版前四章
- Struts html:link tag的问题
- java调试的问题,怎样找这行代码上面的内容?
- java求解链接地址在http://topic.csdn.net/u/20101229/11/7c94d0d8-4618-4e5e-9fee-328acf930
a.currency,
sum(a.Jan_Amount) as Jan_Amount,
sum(a.Feb_Amount) as Feb_Amount,
sum(a.Mar_Amount) as Mar_Amount
from (select a.cust_code,
b.currency,
a.eta,
decode(trunc(a.eta, 'mm'),
to_date('2011-01-01', 'yyyy-mm-dd'),
sum(a.cfc_qty * b.price)) as Jan_Amount,
decode(trunc(a.eta, 'mm'),
to_date('2011-02-01', 'yyyy-mm-dd'),
sum(a.cfc_qty * b.price)) as Feb_Amount,
decode(trunc(a.eta, 'mm'),
to_date('2011-03-01', 'yyyy-mm-dd'),
sum(a.cfc_qty * b.price)) as Mar_Amount
from cfc_detail a, selling_price b
where a.cust_code = b.cust_code
group by a.cust_code, b.currency, a.eta
order by a.cust_code) a
group by a.cust_code, a.currency;