用sql找出ID列中不连续的ID

WITH TT AS
 (SELECT 1 ID FROM DUAL
  UNION ALL
  SELECT 2 ID FROM DUAL
  UNION ALL
  SELECT 3 ID FROM DUAL
  UNION ALL
  SELECT 5 ID FROM DUAL
  UNION ALL
  SELECT 6 ID FROM DUAL
  UNION ALL
  SELECT 7 ID FROM DUAL
  UNION ALL
  SELECT 8 ID FROM DUAL
  UNION ALL
  SELECT 10 ID FROM DUAL
  UNION ALL
  SELECT 11 ID FROM DUAL
  UNION ALL
  SELECT 12 ID FROM DUAL
  UNION ALL
  SELECT 15 ID FROM DUAL),
IT AS
 (SELECT LEVEL ID FROM DUAL CONNECT BY LEVEL <= (SELECT MAX(ID) FROM TT))
SELECT A.ID
  FROM IT A
 WHERE NOT EXISTS (SELECT 1 FROM TT B WHERE A.ID = B.ID)
 ORDER BY 1

posted @ 2014-11-30 11:15  智能先行者  阅读(919)  评论(0编辑  收藏  举报