去除groupBy目标字段分组后数据为null的记录

WITH TEMP1 AS (
        SELECT *
        FROM IPLAT4J.TMMICSALJ01 T01
        WHERE PROD_UNIT in ('1LJ','3LJ','4LJ')  and PROD_DATE BETWEEN '20220306' AND '20220306'
        order by PROD_UNIT),
        TEMP2 AS (
        SELECT *,CASE WHEN PROD_UNIT='1LJ' THEN nvl(ACT_COST,0) ELSE 0
        end as ACT_COST1, CASE WHEN PROD_UNIT='3LJ' THEN nvl(ACT_COST,0) ELSE 0
        end as ACT_COST3,CASE WHEN PROD_UNIT='4LJ' THEN nvl(ACT_COST,0) ELSE 0
        end as ACT_COST4
        FROM TEMP1 ),
        TEMP3 AS (
        SELECT *,CASE WHEN PROD_UNIT='1LJ' THEN nvl(ACT_CONSUME_DH,0)  ELSE 0
        end as ACT_CONSUME_DH1, CASE WHEN PROD_UNIT='3LJ' THEN nvl(ACT_CONSUME_DH,0)  ELSE 0
        end as ACT_CONSUME_DH3,CASE WHEN PROD_UNIT='4LJ' THEN nvl(ACT_CONSUME_DH,0)  ELSE 0
        end as ACT_CONSUME_DH4
        FROM TEMP2 )
        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,
        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,
        MAX(ACT_CONSUME_DH3) AS ACT_CONSUME_DH3,MAX(ACT_CONSUME_DH4) AS ACT_CONSUME_DH4
        FROM TEMP3 GROUP BY MAT_CODE

执行sql后的结果想把为null的行去掉

 

 于是乎，我就在group by 后加上了HAVING COUNT(TYPE3_NAME)>0，然后问题就解决了

 

 HAVING COUNT(目标字段) 是对group by分组后的数据再过滤的方法。