学习Oracle日记（八）--ORACLE CASE WHEN
Ⅰ. ORACLE WHERE 字句里面使用CASE WHEN 25行
Ⅱ. 嵌套使用 CASE WHEN 2~13行
1 SELECT 2 SUM(CASE WHEN M.MV_AVG < 800 AND C.THICKNESS >= 0.5 AND C.THICKNESS <= 0.9 THEN (CASE WHEN D.MV_AVG = 0 OR D.MV_AVG IS NULL THEN 0 ELSE P.LENGTH / D.MV_AVG END ) END ) R1CD ,--1区间内长度/炉区速度 3 SUM(CASE WHEN M.MV_AVG < 800 AND C.THICKNESS > 0.9 AND C.THICKNESS <= 1.3 THEN (CASE WHEN D.MV_AVG = 0 OR D.MV_AVG IS NULL THEN 0 ELSE P.LENGTH / D.MV_AVG END) END ) R2CD ,--2区间内长度/炉区速度 4 SUM(CASE WHEN M.MV_AVG < 800 AND C.THICKNESS > 1.3 AND C.THICKNESS <= 1.6 THEN (CASE WHEN D.MV_AVG = 0 OR D.MV_AVG IS NULL THEN 0 ELSE P.LENGTH / D.MV_AVG END) END ) R3CD ,--3区间内长度/炉区速度 5 SUM(CASE WHEN M.MV_AVG < 800 AND C.THICKNESS > 1.6 THEN (CASE WHEN D.MV_AVG = 0 OR D.MV_AVG IS NULL THEN 0 ELSE P.LENGTH / D.MV_AVG END) END ) R4CD , --4区间内长度/炉区速度 6 SUM(CASE WHEN M.MV_AVG >= 800 AND M.MV_AVG < 830 AND C.THICKNESS >= 0.5 AND C.THICKNESS <= 0.9 THEN (CASE WHEN D.MV_AVG = 0 OR D.MV_AVG IS NULL THEN 0 ELSE P.LENGTH / D.MV_AVG END) END ) R5CD ,--5区间内长度/炉区速度 7 SUM(CASE WHEN M.MV_AVG >= 800 AND M.MV_AVG < 830 AND C.THICKNESS > 0.9 AND C.THICKNESS <= 1.3 THEN (CASE WHEN D.MV_AVG = 0 OR D.MV_AVG IS NULL THEN 0 ELSE P.LENGTH / D.MV_AVG END) END ) R6CD ,--6区间内长度/炉区速度 8 SUM(CASE WHEN M.MV_AVG >= 800 AND M.MV_AVG < 830 AND C.THICKNESS > 1.3 AND C.THICKNESS <= 1.6 THEN (CASE WHEN D.MV_AVG = 0 OR D.MV_AVG IS NULL THEN 0 ELSE P.LENGTH / D.MV_AVG END) END ) R7CD ,--7区间内长度/炉区速度 9 SUM(CASE WHEN M.MV_AVG >= 800 AND M.MV_AVG < 830 AND C.THICKNESS > 1.6 THEN (CASE WHEN D.MV_AVG = 0 OR D.MV_AVG IS NULL THEN 0 ELSE P.LENGTH / D.MV_AVG END) END ) R8CD , --8区间内长度/炉区速度 10 SUM(CASE WHEN M.MV_AVG >= 830 AND C.THICKNESS >= 0.5 AND C.THICKNESS <= 0.9 THEN (CASE WHEN D.MV_AVG = 0 OR D.MV_AVG IS NULL THEN 0 ELSE P.LENGTH / D.MV_AVG END) END ) R9CD ,--9区间内长度/炉区速度 11 SUM(CASE WHEN M.MV_AVG >= 830 AND C.THICKNESS > 0.9 AND C.THICKNESS <= 1.3 THEN (CASE WHEN D.MV_AVG = 0 OR D.MV_AVG IS NULL THEN 0 ELSE P.LENGTH / D.MV_AVG END) END ) R10CD ,--10区间内长度/炉区速度 12 SUM(CASE WHEN M.MV_AVG >= 830 AND C.THICKNESS > 1.3 AND C.THICKNESS <= 1.6 THEN (CASE WHEN D.MV_AVG = 0 OR D.MV_AVG IS NULL THEN 0 ELSE P.LENGTH / D.MV_AVG END) END ) R11CD ,--11区间内长度/炉区速度 13 SUM(CASE WHEN M.MV_AVG >= 830 AND C.THICKNESS > 1.6 THEN (CASE WHEN D.MV_AVG = 0 OR D.MV_AVG IS NULL THEN 0 ELSE P.LENGTH / D.MV_AVG END) END ) R12CD --12区间内长度/炉区速度 14 FROM PRODCOIL_MV M , (SELECT M.PCOIL_SID , M.MV_AVG FROM PRODCOIL_MV M WHERE M.QDR_CHAN = 1 AND M.PRODUCT_LINE = 'CGL1') D ,PRODCOILS P ,COILS C ,PRODCOILS_SOURCES S 15 WHERE M.PCOIL_SID = P.PCOIL_SID 16 AND P.PCOIL_SID = S.PCOIL_SID 17 AND S.COIL_SID = C.COIL_SID 18 AND M.PCOIL_SID = D.PCOIL_SID 19 AND P.DUMMY_COIL = 0 20 AND M.PRODUCT_LINE = 'CGL1' 21 AND P.PRODUCT_LINE = 'CGL1' 22 AND C.PRODUCT_LINE = 'CGL1' 23 AND S.PRODUCT_LINE = 'CGL1' 24 25 AND M.QDR_CHAN = (CASE WHEN 'CGL1' = 'CGL1' THEN 521 ELSE 531 END ) 26 AND P.PROD_DAY BETWEEN TO_DATE('2014-12-01','YYYY-MM-DD') AND TO_DATE('2014-12-31','YYYY-MM-DD')
