时间戳

首页 > 解决方案 > 我想调整下面的查询

sql - 我想调整下面的查询

问题描述

SELECT GRIRNO,GRIRDATE,CLRD_ON
FROM GRIR_PASS
WHERE APRVD_BY IS NOT NULL and
      grirno not in 
    (select grirno
     from grirmain
     where rcvd_by is not null
    )
ORDER BY
     TO_NUMBER(substr(GRIRNO,instr(GRIRNO,'/',1,1)+1,(instr(GRIRNO,'/',1,2)-instr(GRIRNO,'/',1,1)-1)));

标签： sqloracle

解决方案

您可以使用 MINUS 而不是 not in 来选择涉及的 GRIRNO 从两个表 GRIR_PASS 和 grirmain

  select GRIRNO,GRIRDATE,CLRD_ON 
  from GRIR_PASS 
  inner join  (
    select  GRIRNO 
    FROM GRIR_PASS
    WHERE APRVD_BY IS NOT NULL
    minus 
    select grirno 
    from grirmain
    where rcvd_by is not null
  ) t on t.GRIRNO = GRIR_PASS.GRIRNO
  ORDER BY
       TO_NUMBER(substr(GRIRNO,instr(GRIRNO,'/',1,1)+1,(instr(GRIRNO,'/',1,2)-instr(GRIRNO,'/',1,1)-1)));

推荐阅读