Ⅰ. 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
'
)
