Ⅰ. 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區(qū)間內(nèi)長度/爐區(qū)速度 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區(qū)間內(nèi)長度/爐區(qū)速度 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區(qū)間內(nèi)長度/爐區(qū)速度 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區(qū)間內(nèi)長度/爐區(qū)速度 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區(qū)間內(nèi)長度/爐區(qū)速度 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區(qū)間內(nèi)長度/爐區(qū)速度 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區(qū)間內(nèi)長度/爐區(qū)速度 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區(qū)間內(nèi)長度/爐區(qū)速度 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區(qū)間內(nèi)長度/爐區(qū)速度 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區(qū)間內(nèi)長度/爐區(qū)速度 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區(qū)間內(nèi)長度/爐區(qū)速度 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區(qū)間內(nèi)長度/爐區(qū)速度 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 ' )
?
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號聯(lián)系: 360901061
您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點(diǎn)擊下面給點(diǎn)支持吧,站長非常感激您!手機(jī)微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點(diǎn)擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對您有幫助就好】元
