SELECT STORE_NO,(CASE WHEN SUM(AMOUNT*PRICE) != 0 THEN SUM(AMOUNT*PRICE) ELSE 0 END )AS Q1 from SALES where SALE_DATE between to_date('2010-04','YYYY-MM') and to_date('2010-06','YYYY-MM')GROUP BY STORE_NO
这是显示第一季度的各店销售额,没有销售额的显示为0
显示效果是要这个样子SQLselect
这是显示第一季度的各店销售额,没有销售额的显示为0
显示效果是要这个样子SQLselect
with t1 as
(
select 'aa' c1,date'2013-01-04' c2,100 c3 from dual union all
select 'bb' c1,date'2013-01-04' c2,55 c3 from dual union all
select 'aa' c1,date'2013-03-04' c2,33 c3 from dual union all
select 'aa' c1,date'2013-04-04' c2,241 c3 from dual union all
select 'bb' c1,date'2013-02-04' c2,14 c3 from dual union all
select 'aa' c1,date'2013-05-04' c2,55 c3 from dual union all
select 'aa' c1,date'2013-07-04' c2,44 c3 from dual union all
select 'bb' c1,date'2013-07-04' c2,66 c3 from dual union all
select 'aa' c1,date'2013-11-04' c2,77 c3 from dual union all
select 'bb' c1,date'2013-12-04' c2,88 c3 from dual
)select c1,
nvl(sum(decode(c2,1,c3,0)),0) q1,
nvl(sum(decode(c2,2,c3,0)),0) q1,
nvl(sum(decode(c2,3,c3,0)),0) q1,
nvl(sum(decode(c2,4,c3,0)),0) q1,
nvl(sum(c3),0) total
from
(
select c1,to_char(c2,'q') c2,sum(c3) c3
from t1
group by c1,to_char(c2,'q')
)
group by c1
select c1,
nvl(sum(decode(to_char(c2,'q'),1,c3,0)),0) q1,
nvl(sum(decode(to_char(c2,'q'),2,c3,0)),0) q2,
nvl(sum(decode(to_char(c2,'q'),3,c3,0)),0) q3,
nvl(sum(decode(to_char(c2,'q'),4,c3,0)),0) q4,
nvl(sum(c3),0) total
from t1
group by c1
c1 q1 q2 q3 q4 total
----------------------------------------------------------
1 aa 133 296 44 77 550
2 bb 69 0 66 88 223
nvl(sum(decode(to_char(SALE_DATE,'q'),1,AMOUNT*PRICE,0)),0) q1,
nvl(sum(decode(to_char(SALE_DATE,'q'),2,AMOUNT*PRICE,0)),0) q2,
nvl(sum(decode(to_char(SALE_DATE,'q'),3,AMOUNT*PRICE,0)),0) q3,
nvl(sum(decode(to_char(SALE_DATE,'q'),4,AMOUNT*PRICE,0)),0) q4,
nvl(sum(AMOUNT*PRICE),0) total
from t1
--如果数据不多
--where to_char(SALE_DATE,'yyyy') = 2010
--如果数据量相对较大
where SALE_DATE >= date'2010-01-01' and SALE_DATE < date'2011-01-01'
group by STORE_NO
(CASE
WHEN (SUM(AMOUNT * PRICE) != 0) and
(SALE_DATE between to_date('2010-04', 'YYYY-MM') and
to_date('2010-06', 'YYYY-MM')) THEN
SUM(AMOUNT * PRICE)
ELSE
0
END) AS Q1,
(CASE
WHEN (SUM(AMOUNT * PRICE) != 0) and
(SALE_DATE between to_date('2010-07', 'YYYY-MM') and
to_date('2010-09', 'YYYY-MM')) THEN
SUM(AMOUNT * PRICE)
ELSE
0
END) AS Q1,
(CASE
WHEN (SUM(AMOUNT * PRICE) != 0) and
(SALE_DATE between to_date('2010-10', 'YYYY-MM') and
to_date('2010-12', 'YYYY-MM')) THEN
SUM(AMOUNT * PRICE)
ELSE
0
END) AS Q1,
(CASE
WHEN (SUM(AMOUNT * PRICE) != 0) and
(SALE_DATE between to_date('2010-01', 'YYYY-MM') and
to_date('2010-03', 'YYYY-MM')) THEN
SUM(AMOUNT * PRICE)
ELSE
0
END) AS Q1,
sum(AMOUNT * PRICE) as total
from SALES
GROUP BY STORE_NO;