oracle数据库表 ccs_order
count(销量)
id name count cash date
1 诺基亚 1 3792 2017/03/20 11:07:47
2 诺基亚 1 3792 2017/03/1711:07:49
3 诺基亚 1 3792 2017/03/19 11:07:50
4 诺基亚 1 3792 2017/03/18 11:07:52
5 戴尔 1 7570 2017/03/18 14:52:23
6 戴尔 1 5613 2017/03/17 14:52:24
count(销量)
id name count cash date
1 诺基亚 1 3792 2017/03/20 11:07:47
2 诺基亚 1 3792 2017/03/1711:07:49
3 诺基亚 1 3792 2017/03/19 11:07:50
4 诺基亚 1 3792 2017/03/18 11:07:52
5 戴尔 1 7570 2017/03/18 14:52:23
6 戴尔 1 5613 2017/03/17 14:52:24
sum(to_char(date,'ww') = '11' then count end) "上周销量",
sum(to_char(date,'ww') = '12' then count end) "本周销量",
sum(to_char(date,'yyyy') = '2017' then count end) "本年销量",
(sum(to_char(date,'ww') = '12' then count end)-sum(to_char(date,'ww') = '11' then count end))/sum(to_char(date,'ww') = '11' then count end) "周上升幅度"
from ccs_order;
select 上周销量,
本周销量,
本年销量,
(本周销量 - 上周销量) / 上周销量 周上升幅度
from (select (select sum(count)
from ccs_order
where date >= trunc(sysdate, 'd') - 6
and date < trunc(sysdate, 'd') + 1) 上周销量,
(select sum(co unt)
from ccs_order
where date >= trunc(sysdate, 'd') + 1
and date < trunc(sysdate, 'd') + 8) 本周销量,
(select sum(count)
from ccs_order
where date >= trunc(sysdate, 'YYYY')) 本年销量,
from dual)
sum(case when to_char(date,'ww') = '11' then count end) "上周销量",
sum(case when to_char(date,'ww') = '12' then count end) "本周销量",
sum(case when to_char(date,'yyyy') = '2017' then count end) "本年销量",
(sum(case when to_char(date,'ww') = '12' then count end)-sum(case when to_char(date,'ww') = '11' then count end))/sum(case when to_char(date,'ww') = '11' then count end) "周上升幅度"
from ccs_order;
2楼忘记加case when 了