oracle not in exsits union all 等关键字使用

1.一个监控点只能被一个运维企业使用，且如果运维企业删除后选择的监控点释放

                           SELECT A.GUID, A.T_MONITOR_NAME
                              FROM ZTS_JKDXX A, ZTS_PFKJBXX B
                             WHERE A.T_PWK_ID = B.GUID
                               AND B.T_QY_ID =
                                   '6FC021DE44CE4CBA8E7EC2B49D1712EF'
                               AND A.GUID NOT IN
                                   (SELECT T_JKDID
                                      FROM ZTS_YWJKDGL JL
                                     WHERE EXISTS -- 判断运维企业是否被删除，如果被没被删除，运维企业关联的监控点就要被过滤
                                     (SELECT 1
                                              FROM ZTS_YWDWXX YW
                                             WHERE JL.T_YWDWID = YW.GUID))
                               AND A.T_PWK_ID =
                                   'D60D8FD05A84422CBE74A23862F5A1B9'；

 

2.查询查询企业的废水和废气排放状态，如果取所有监控点监测状态最大的值作为企业的监控状态

SELECT * 
 FROM (SELECT QY.GUID,QY.T_YQLX,QY.T_PWDWMC,QY.T_QYJC, QY.T_QYDZ T_PWDWDZ,
       QY.T_QYJD,QY.T_QYWD,QY.T_GLQY,
       CASE WHEN QY.T_PWLXR = 'NULL' OR QY.T_PWLXR = NULL THEN ''
         ELSE QY.T_PWLXR END AS T_PWLXR,
       CASE WHEN QY.T_PWLXDH = 'NULL' OR QY.T_PWLXDH = NULL THEN ''
         ELSE QY.T_PWLXDH END AS T_PWLXDH,
       CASE
          WHEN T_JKZT = '7' THEN '超标'
          WHEN T_JKZT = '6' THEN '预警'
          WHEN T_JKZT = '5' THEN '负值'
          WHEN T_JKZT = '4' THEN '超大值'
          WHEN T_JKZT = '3' THEN '零值'
          WHEN T_JKZT = '2' OR  T_JKZT IS NULL THEN '脱机'
          WHEN T_JKZT = '1' THEN '正常'
        END AS JCZT,
        NVL(T_JKZT,'2') T_JKZT,
        CASE
          WHEN JCSJ IS NULL  THEN TO_CHAR(SYSDATE,'HH24:MI')
          ELSE TO_CHAR(JCSJ,'HH24:MI')
        END AS JCSJ,
        QY.T_QYDZ
  FROM ZTS_QYJBXX QY
  LEFT JOIN (SELECT T_QYID,MAX(T_JKZT)T_JKZT,MAX(JCSJ)JCSJ FROM (  SELECT PFK.T_QY_ID T_QYID,PFK.GUID PFKID,JKD.GUID JKDID,PFK.T_OUTFALL_NAME,
       JKD.T_MONITOR_CODE,JKD.T_MONITOR_NAME,
        NVL(SS.T_JKZT,'2') T_JKZT,
        SS.T_JCSJ AS JCSJ
  FROM ZTS_PFKJBXX PFK
  LEFT JOIN ZTS_JKDXX JKD ON JKD.T_PWK_ID = PFK.GUID
  LEFT JOIN (SELECT MAX(JC.T_JKZT) AS T_JKZT, MAX(JC.T_JCSJ) AS T_JCSJ, JC.T_QYID,JC.T_JKDID
               FROM (SELECT DECODE(FS.T_JKZT,'2','6','3','7','4','2','6','5','7','4','8','3',FS.T_JKZT) AS T_JKZT,
                            FS.T_JCSJ,FS.T_QYID,FS.T_JKDID  FROM ZTS_FSYZSJJL FS

                   --关联废水和废气所有实时数据
                     UNION ALL SELECT DECODE(FQ.T_JKZT,'2','6','3','7','4','2','6','5','7','4','8','3',FQ.T_JKZT) AS T_JKZT,
                            FQ.T_JCSJ,FQ.T_QYID,FQ.T_JKDID FROM ZTS_FQYZSJJL FQ)JC
                     GROUP BY JC.T_QYID,JC.T_JKDID) SS
    ON PFK.T_QY_ID = SS.T_QYID AND JKD.GUID= SS.T_JKDID)
   GROUP BY T_QYID )DT ON QY.GUID = DT.T_QYID 
   WHERE T_SHZT IN ('待入网','已入网')
   )
  ORDER BY T_JKZT DESC, T_QYJC