SQL1:
select SUM(CASE WHEN UNDESIRABLE != ' ' THEN 1 END) from Tm_Marketfeedback
WHERE datetochar(BACKDATE,'M') BETWEEN '2009-05' AND '2012-06')SQL2:
select SUM(LOT_SIZE) AS B from tl_out_lot
where datetochar(OUTGOINGDATE,'M') ='2012-06'将两条SQL的值相除 整合一条SQL,谢谢~~
select SUM(CASE WHEN UNDESIRABLE != ' ' THEN 1 END) from Tm_Marketfeedback
WHERE datetochar(BACKDATE,'M') BETWEEN '2009-05' AND '2012-06')SQL2:
select SUM(LOT_SIZE) AS B from tl_out_lot
where datetochar(OUTGOINGDATE,'M') ='2012-06'将两条SQL的值相除 整合一条SQL,谢谢~~
select (select SUM(CASE
WHEN UNDESIRABLE != ' ' THEN
1
END)
from Tm_Marketfeedback
WHERE datetochar(BACKDATE, 'M') BETWEEN '2009-05' AND '2012-06')) / (select SUM(LOT_SIZE) AS B
from tl_out_lot
where datetochar(OUTGOINGDATE, 'M') = '2012-06')
from dual;
但SUM(LOT_SIZE)做为除数的话,建议添加一个NULL或零值判断。
如:decode(NVL(SUM(LOT_SIZE),0),0,1,1)--如果是NULL值,强制转换为0,然后再把零值强制转换为1,其余的,不做变化。
from (SELECT DATETOCHAR(TM.BACKDATE, 'M') AS GROUPTIME,
CLIENT AS CUSTOMER,
(SELECT SUM(CASE
WHEN UNDESIRABLE != ' ' THEN
1
END)
FROM TM_MARKETFEEDBACK
WHERE datetochar(BACKDATE, 'M') BETWEEN '2010-11' AND
'2012-12') /
(select SUM(LOT_SIZE) AS B
FROM TL_OUT_LOT
where datetochar(OUTGOINGDATE, 'M') = '2012-12') * 1000000 AS LIANG
FROM TM_MARKETFEEDBACK TM
INNER JOIN TL_OUT_LOT TOL ON TOL.PRODUCT_ID = TM.CATEGORY
WHERE 1 = 1
AND DATETOCHAR(TM.BACKDATE, 'M') IS NOT NULL
AND CLIENT IS NOT NULL
GROUP BY DATETOCHAR(TM.BACKDATE, 'M'), CLIENT) T
WHERE LIANG IS NOT NULL
ORDER BY GROUPTIME--这个分组下来好像LIANG是不对的
--当月之前24个月发生售后退回数之和/当月销售数*1000000
--例如:2012年6月的24MIS售后不良PPM达成=2010年5月至2012年6月发生售后退回数之和/2012年6月
--销售数*1000000 请大神在帮改改