SQL 查询返回按键排序的行加上缺少键的空行

Question

我有一个表，本质上具有以下结构:

 key        value ------     ------ 2          val1 3          val2 5          val3

键是从 1 到(当前)100 万的连续整数，每天增加几千。删除记录时会出现键间隙。

我正在寻找返回以下内容的 SQL 查询:

 key        value ------     ------ 1 2          val1 3          val2 4  5          val3

我可以看到如何通过连接到具有完整键列表的第二个表来执行此操作。但是，我更喜欢使用标准 SQL(没有存储过程或第二个键表)的解决方案，并且无论键的上限值是多少，它都可以工作。

Answer 1

SQL 查询没有循环机制。过程语言有循环，但查询本身只能“循环”它们在表(或派生表)中找到的数据。

为了动态生成数字列表，我所做的是对一个由 0 到 9 的数字组成的小表格进行交叉连接:

CREATE TABLE n (d NUMERIC);
INSERT INTO n VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

然后生成00..99:

SELECT n1.d + n2.d*10 AS d
FROM n AS n1 CROSS JOIN n AS n10;

如果你只想要 00..57:

SELECT n1.d + n2.d*10 AS d
FROM n AS n1 CROSS JOIN n AS n2
WHERE n1.d + n2.d*10 <= 57;

您当然可以为 100 位、1000 位等连接表。请注意，您不能在 WHERE 子句中使用列别名，因此您必须重复完整的表达式。

现在您可以将其用作 FROM 子句中的派生表并将其连接到您的数据表。

SELECT n0.d, mytable.value
FROM
   (SELECT n1.d + n2.d*10 + n2.d*100 + n3.d*1000 
      + n4.d*10000 + n5.d*100000 AS d
    FROM n AS n1 CROSS JOIN n AS n2 CROSS JOIN n AS n3 
      CROSS JOIN n AS n4 CROSS JOIN n AS n5) AS n0
  LEFT OUTER JOIN mytable ON (n0.d = mytable.key)
WHERE n0.d <= (SELECT MAX(key) FROM mytable);

每次您的表超过一个数量级时，您确实需要添加另一个CROSS JOIN。例如。当它超过 100 万时，为 n6 添加一个连接。

另请注意，我们现在可以在外部查询的 WHERE 子句中使用列别名。

诚然，仅在 SQL 中执行此操作可能是一个非常昂贵的查询。您可能会发现通过编写一些应用程序代码来“填补空白”既简单又快捷。