SELECT D.SAL_TIME,D.SP,CASE WHEN MONTHS_SP IS NULL THEN NULL ELSE ROUND(((D.MONTHS_SP-D.SP)/D.MONTHS_SP),3)*100||'%' END HB
,CASE WHEN YEAR_SP IS NULL THEN NULL ELSE ROUND(((D.YEAR_SP-D.SP)/D.YEAR_SP),3)*100||'%' END TB
FROM (
SELECT A.*,B.MONTHS_SP,C.YEAR_SP FROM (
SELECT TO_CHAR(SAL_TIME,'YYYYMM') SAL_TIME,SUM(SAL_NUM*P.P_PEI) SP FROM T004_SALE S LEFT JOIN T004_PRODUCT P ON S.PRODUCTID=P.P_ID
GROUP BY TO_CHAR(SAL_TIME,'YYYYMM') ORDER BY SAL_TIME ) A
LEFT JOIN
(
SELECT TO_CHAR(SAL_TIME,'YYYYMM') SAL_TIME,SUM(SAL_NUM*P.P_PEI) MONTHS_SP FROM T004_SALE S LEFT JOIN T004_PRODUCT P ON S.PRODUCTID=P.P_ID
GROUP BY TO_CHAR(SAL_TIME,'YYYYMM') ORDER BY SAL_TIME ) B
ON A.SAL_TIME = TO_CHAR(ADD_MONTHS(TO_DATE(B.SAL_TIME,'YYYYMM'),1),'YYYYMM')
LEFT JOIN
(
SELECT TO_CHAR(SAL_TIME,'YYYYMM') SAL_TIME,SUM(SAL_NUM*P.P_PEI) YEAR_SP FROM T004_SALE S LEFT JOIN T004_PRODUCT P ON S.PRODUCTID=P.P_ID
GROUP BY TO_CHAR(SAL_TIME,'YYYYMM') ORDER BY YEAR_SP ) C
ON A.SAL_TIME = TO_CHAR(ADD_MONTHS(TO_DATE(C.SAL_TIME,'YYYYMM'),12),'YYYYMM')
) D入代码片
环比同比
环比增长率=(本期数-上期数)/上期数×100%。
同比增长率=(本期数-同期数)/同期数×100%。
求出,每个月份的销售总销售,环比;同比;同比()