iteye_13073
iteye_13073
2011-07-26 13:30
浏览 576

oracle数据库中NOT IN 和 NOT EXISTS为什查不到数据

SELECT GA.ADID, GA.ADNAME
      FROM GAP_AD GA
     WHERE
     exists(SELECT GAP.ADID
              FROM GAP_ADPOSITION GAP
             WHERE GAP.ADPOSITIONSTATUS = '1')
       AND NOT exists(SELECT GAP.ADID
              FROM GAP_ADPOSITION GAP
             WHERE GAP.ADPOSITIONSTATUS = '0'
                OR GAP.ADPOSITIONSTATUS = '2')
为什么查不到数据呢?
SELECT GA.ADID, GA.ADNAME
      FROM GAP_AD GA
     WHERE GA.ADID IN(SELECT GAP.ADID
              FROM GAP_ADPOSITION GAP
             WHERE GAP.ADPOSITIONSTATUS = '1')
       AND GA.ADID NOT IN (SELECT GAP.ADID
              FROM GAP_ADPOSITION GAP
             WHERE GAP.ADPOSITIONSTATUS = '0'
                OR GAP.ADPOSITIONSTATUS = '2')
这个就可以查到
哪位朋友帮帮我

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • cndone
    cndone 2011-07-26 13:54
    已采纳

    SELECT GA.ADID, GA.ADNAME
    FROM GAP_AD GA
    WHERE
    exists(SELECT GAP1.ADID
    FROM GAP_ADPOSITION GAP1
    WHERE GAP1.ADPOSITIONSTATUS = '1' and GA.ADID=GAP1.ADID)
    AND NOT exists(SELECT GAP2.ADID
    FROM GAP_ADPOSITION GAP2
    WHERE (GAP2.ADPOSITIONSTATUS = '0'
    OR GAP2.ADPOSITIONSTATUS = '2')AND GA.ADID=GAP2.ADID )

    LZ,have a try!

    点赞 评论
  • cndone
    cndone 2011-07-26 13:51

    exists(SELECT GAP.ADID
    FROM GAP_ADPOSITION GAP
    WHERE GAP.ADPOSITIONSTATUS = '1')

    为true;
    AND NOT exists(SELECT GAP.ADID
    FROM GAP_ADPOSITION GAP
    WHERE GAP.ADPOSITIONSTATUS = '0'
    OR GAP.ADPOSITIONSTATUS = '2')
    为false. true and false 的结果肯定是false,所以查不到

    点赞 评论

相关推荐