Ⅰ. 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
'
)
?
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
