Pandaaaa906

find_skipped_id

Mar 1st, 2019
202
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. --有个表(tbig),数据量为kW级别,有一个字段(id)是整型,但是不连续,现在需要找出缺失的记录。
  2. WITH RECURSIVE t(n, present) as(
  3.     SELECT 1, (SELECT exists(SELECT 1 FROM tbig WHERE tbig.id=1))
  4.     UNION
  5.     SELECT t.n+1, (SELECT exists(SELECT 1 FROM tbig WHERE tbig.id=t.n+1)) FROM t
  6. )
  7. SELECT n FROM t WHERE NOT present
  8. limit (SELECT MAX(tbig.id)-COUNT(*) FROM tbig)
RAW Paste Data