SELECT SUM(SALE_QTY) AS QTYB
FROM V_OUT_SST_CUS_OUT_DETAIL AS B
WHERE (RPT_DATE >= TO_DATE('2009-01', 'yyyy-MM')) AND
(RPT_DATE < TO_DATE('2009-02', 'yyyy-MM')) AND (CUS_ID LIKE 'C0807080012')
-----------------------------------------------------------------------------------
SELECT SUM(SALE_QTY) AS QTYA
FROM V_OUT_SST_CUS_OUT_DETAIL AS A
WHERE (RPT_DATE >= TO_DATE('2009-02', 'yyyy-MM')) AND
(RPT_DATE < TO_DATE('2009-03', 'yyyy-MM')) AND (CUS_ID LIKE 'C0807080012')想通过一条SQL得到两个SUM后的值的差值,即(QTYA-QTYB)的值,是否可以实现请高手指教,多谢。
FROM V_OUT_SST_CUS_OUT_DETAIL AS B
WHERE (RPT_DATE >= TO_DATE('2009-01', 'yyyy-MM')) AND
(RPT_DATE < TO_DATE('2009-02', 'yyyy-MM')) AND (CUS_ID LIKE 'C0807080012')
-----------------------------------------------------------------------------------
SELECT SUM(SALE_QTY) AS QTYA
FROM V_OUT_SST_CUS_OUT_DETAIL AS A
WHERE (RPT_DATE >= TO_DATE('2009-02', 'yyyy-MM')) AND
(RPT_DATE < TO_DATE('2009-03', 'yyyy-MM')) AND (CUS_ID LIKE 'C0807080012')想通过一条SQL得到两个SUM后的值的差值,即(QTYA-QTYB)的值,是否可以实现请高手指教,多谢。
from
( SELECT SUM(SALE_QTY) AS QTYA
FROM V_OUT_SST_CUS_OUT_DETAIL AS A
WHERE (RPT_DATE >= TO_DATE('2009-02', 'yyyy-MM')) AND
(RPT_DATE < TO_DATE('2009-03', 'yyyy-MM')) AND (CUS_ID LIKE 'C0807080012')
)S,
( SELECT SUM(SALE_QTY) AS QTYB
FROM V_OUT_SST_CUS_OUT_DETAIL AS B
WHERE (RPT_DATE >= TO_DATE('2009-01', 'yyyy-MM')) AND
(RPT_DATE < TO_DATE('2009-02', 'yyyy-MM')) AND (CUS_ID LIKE 'C0807080012') ) T
(
SELECT SUM(SALE_QTY) AS QTYA
FROM V_OUT_SST_CUS_OUT_DETAIL AS A
WHERE (RPT_DATE >= TO_DATE('2009-02', 'yyyy-MM')) AND
(RPT_DATE < TO_DATE('2009-03', 'yyyy-MM')) AND (CUS_ID LIKE 'C0807080012')
)t1,
(
SELECT SUM(SALE_QTY) AS QTYB
FROM V_OUT_SST_CUS_OUT_DETAIL AS B
WHERE (RPT_DATE >= TO_DATE('2009-01', 'yyyy-MM')) AND
(RPT_DATE < TO_DATE('2009-02', 'yyyy-MM')) AND (CUS_ID LIKE 'C0807080012')
)t2
where 1=1有主键的话,可以通过sum(case when ) 的用法,将2个语句合并
FROM V_OUT_SST_CUS_OUT_DETAIL AS A
WHERE (RPT_DATE >= TO_DATE('2009-02', 'yyyy-MM')) AND
(RPT_DATE < TO_DATE('2009-03', 'yyyy-MM')) AND (CUS_ID LIKE 'C0807080012')
) - (SELECT SUM(SALE_QTY) AS QTYB
FROM V_OUT_SST_CUS_OUT_DETAIL AS B
WHERE (RPT_DATE >= TO_DATE('2009-01', 'yyyy-MM')) AND
(RPT_DATE < TO_DATE('2009-02', 'yyyy-MM')) AND (CUS_ID LIKE 'C0807080012') )
-SUM(case when RPT_DATE >= TO_DATE('2009-01', 'yyyy-MM') and RPT_DATE < TO_DATE('2009-02', 'yyyy-MM') then SALE_QTY else 0 end)
FROM V_OUT_SST_CUS_OUT_DETAIL
WHERE (CUS_ID LIKE 'C0807080012')
group by id 类似这个语句