去除groupBy ⽬标字段分组后数据为null 的记录
执⾏sql后的结果想把为null的⾏去掉
于是乎,我就在group by 后加上了HAVING COUNT(TYPE3_NAME)>0,然后问题就解决了
HAVING COUNT(⽬标字段) 是对group by分组后的数据再过滤的⽅法。 1 WITH TEMP1 AS (
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) A S 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
>groupby分组
版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198,我们将在24小时内删除。
发表评论