sql - SQL查询查找行

我在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查询来查找这些丢失的字母数字字符？有什么想法吗

最佳答案

您可以生成数字的完整列表，然后使用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函数使列看起来像您的主键(请参阅format model函数的TO_CHAR文档)。

然后我将这些生成的值与您的实际表值(NOT IN是一个ANTI-JOIN)进行比较