2
SELECT
*
3
FROM
IPLAT4J.TMMICSALJ01 T01
4
WHERE
PROD_UNIT
in
(
'
1LJ
'
,
'
3LJ
'
,
'
4LJ
'
)
and
PROD_DATE
BETWEEN
'
20220306
'
AND
'
20220306
'
5
order
by
PROD_UNIT),
6
TEMP2
AS
(
7
SELECT
*
,
CASE
WHEN
PROD_UNIT
=
'
1LJ
'
THEN
nvl(ACT_COST,
0
)
ELSE
0
8
end
as
ACT_COST1,
CASE
WHEN
PROD_UNIT
=
'
3LJ
'
THEN
nvl(ACT_COST,
0
)
ELSE
0
9
end
as
ACT_COST3,
CASE
WHEN
PROD_UNIT
=
'
4LJ
'
THEN
nvl(ACT_COST,
0
)
ELSE
0
10
end
as
ACT_COST4
11
FROM
TEMP1 ),
12
TEMP3
AS
(
13
SELECT
*
,
CASE
WHEN
PROD_UNIT
=
'
1LJ
'
THEN
nvl(ACT_CONSUME_DH,
0
)
ELSE
0
14
end
as
ACT_CONSUME_DH1,
CASE
WHEN
PROD_UNIT
=
'
3LJ
'
THEN
nvl(ACT_CONSUME_DH,
0
)
ELSE
0
15
end
as
ACT_CONSUME_DH3,
CASE
WHEN
PROD_UNIT
=
'
4LJ
'
THEN
nvl(ACT_CONSUME_DH,
0
)
ELSE
0
16
end
as
ACT_CONSUME_DH4
17
FROM
TEMP2 )
18
SELECT
MAT_CODE,
MAX
(TYPE1_NAME)
AS
TYPE1_NAME,
MAX
(TYPE2_NAME)
AS
TYPE2_NAME,
MAX
(TYPE3_NAME)
AS
TYPE3_NAME,
MAX
(COST_SUBJECT_NAME)
AS
COST_SUBJECT_NAME,
19
MAX
(ACT_COST1)
AS
ACT_COST1,
MAX
(ACT_COST3)
AS
ACT_COST3,
MAX
(ACT_COST4)
AS
ACT_COST4,
MAX
(ACT_CONSUME_DH1)
AS
ACT_CONSUME_DH1,
20
MAX
(ACT_CONSUME_DH3)
AS
ACT_CONSUME_DH3,
MAX
(ACT_CONSUME_DH4)
AS
ACT_CONSUME_DH4
21
FROM
TEMP3
GROUP
BY
MAT_CODE
执行sql后的结果想把为null的行去掉
于是乎，我就在group by 后加上了HAVING COUNT(TYPE3_NAME)>0，然后问题就解决了
HAVING COUNT(目标字段) 是对group by分组后的数据再过滤的方法。
