查找行的 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 查询来找出这些缺失的字母数字字符？任何想法请

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 函数使列看起来像您的主键(请参阅 format model 函数的 TO_CHAR 文档)。
• 然后我将这些生成的值与您的实际表值进行比较(NOT IN 是一个 ANTI-JOIN)