SELECT to_date(to_char(last_day(add_months(SYSDATE, -2))+1,'yyyy-mm-dd'),'yyyy-mm-dd'),to_date(to_char(last_day(add_months(SYSDATE, -1)),'yyyy-mm-dd'),'yyyy-mm-dd') FROM dual;
SELECT to_date(to_char(last_day(add_months(SYSDATE, -3))+1,'yyyy-mm-dd'),'yyyy-mm-dd'),to_date(to_char(last_day(add_months(SYSDATE, -2)),'yyyy-mm-dd'),'yyyy-mm-dd') FROM dual;
SELECT to_date(to_char(last_day(add_months(SYSDATE, -4))+1,'yyyy-mm-dd'),'yyyy-mm-dd'),to_date(to_char(last_day(add_months(SYSDATE, -3)),'yyyy-mm-dd'),'yyyy-mm-dd') FROM dual;
1: select sum(case when to_char(billingdate,'yyyy-mm') =to_char(last_day(add_months(SYSDATE, 0)),'yyyy-mm') then quantity else 0 end) currentmonthquantity,
sum(case when to_char(billingdate,'yyyy-mm') =to_char(last_day(add_months(SYSDATE, -1)),'yyyy-mm') then quantity else 0 end) ,
sum(case when to_char(billingdate,'yyyy-mm') =to_char(last_day(add_months(SYSDATE, -2)),'yyyy-mm') then quantity else 0 end),
sum(case when to_char(billingdate,'yyyy-mm') =to_char(last_day(add_months(SYSDATE, -3)),'yyyy-mm') then quantity else 0 end) ,
prodid,branchid,sum(quantity) quantity From (
select * From tb_gos_sale_salestockoutdet
where billingdate between to_date(to_char(last_day(add_months(SYSDATE, -4))+1,'yyyy-mm-dd'),'yyyy-mm-dd') and sysdate
) group by prodid,branchid
having prodid='SPH00013655'
2: select * From (
select to_char(billingdate,'yyyy-mm') billdate,prodid,branchid,sum(quantity) quantity From (
select * From tb_gos_sale_salestockoutdet
where billingdate between to_date(to_char(last_day(add_months(SYSDATE, -4))+1,'yyyy-mm-dd'),'yyyy-mm-dd') and sysdate
) group by to_char(billingdate,'yyyy-mm'),prodid,branchid
having prodid='SPH00013655'
)
pivot
(
sum(quantity)
for billdate in ('2011-03' as 三月销售,'2011-04','2011-05','2011-06')
)
此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。 原文来自 — 版权归原作者所有。