SQL查询以查找行

Question

我有一个在ORACLE数据库中的表mytable，只有一个名为Roll_no的列，如下所示:

Roll_no
----------
CD01 0001
CD01 0002
CD01 0004
CD01 0005
CD01 0008

这里的CD01是固定的，CD01后面有一个空格，然后是数字0001以及更多。

请注意，CD01 0003，CD01 0006和CD01 0007缺失了，这些是我需要的结果。

所需结果：

Roll_no
----------
CD01 0003
CD01 0006
CD01 0007

我该如何编写SQL查询以查找这些丢失的字母数字字符？请问有什么想法吗？

- saroj

---

由于您字段中的值不是原子性的（顺便说一下，这不是一个好主意），我怀疑您无法通过一个SQL查询实现您想要的结果。 - user520288

请问能否告诉我那个 SQL 查询语句？我一直在尝试，但是没有显示出所需的输出。 - saroj

@marc_s...你有什么想法吗？我该如何获得所需的输出？ - saroj

Answer 1

你可以生成完整的数字列表，然后使用 NOT EXISTS（或 NOT IN） 过滤掉已有的记录。例如：

SELECT new_roll_no
  FROM (SELECT 'CD01 ' || to_char(rownum, 'fm0000') new_roll_no FROM dual 
        CONNECT BY LEVEL <= (SELECT to_number(MAX(substr(roll_no, 6))) FROM T))
 WHERE new_roll_no NOT IN (SELECT roll_no FROM T)
 ORDER BY 1

这是它的工作原理：

• CONNECT BY LEVEL是一个特殊的查询，它会生成行数直到您指定的级别（在本例中，我选择了您表格的最大值）。我认为这个查询最初出现在askTom网站上，现在已经相当普及。
• 我生成了8行，接下来将使用TO_CHAR函数使一列看起来像您的主键（请参阅格式模型文档和TO_CHAR函数）。
• 然后将这些生成的值与您实际的表格值进行比较（NOT IN是一个反向连接）。

- Vincent Malgrat

---

非常感谢您的回答，但是当我运行查询时，它告诉我 ERROR at line 1: ORA-00907: 缺少右括号。 - saroj

@saroj：我漏了一个括号，现在应该可以工作了（但在9ir2上仍然会出错，但在11gr2上可以工作）。 - Vincent Malgrat

非常感谢，它起作用了，只有一个请求，请简要解释一下这些术语：fm0000、connect by level、new_roll_no？请稍微解释一下。 - saroj

Answer 2

select n
  from (select 'CD01' || to_char(rownum, '0009') n
          from dual
    connect by level <= (select max(substr(roll_no, 6)) from mytable))
where n not in (select roll_no from mytable)

- archimede

---

抱歉，没有看到Vincent的帖子。 - archimede