SQL查询以查找表中min_numbers和max_number之间的孔

SQL专家的快速提问.

我有一个表,其中包含两列-min_number和max_number
我一直在尝试写一个查询,但未成功找到最小和最大数字之间的第n个孔

例

     min    max
1.   100    200
2.   250    300
3.   330    400

如果我想找到一个大小为50的孔,则将返回第1行的最大值200(在第2行的最小值和第2行的最小值之间有50的孔),第20行的孔将返回第2s的最大值300,以此类推.
如果不存在合适大小的孔,则将返回最后的最大值(400).

谢谢

为什么这么多SQL问题忘记了表名？

-- Buggy: should reference (lo.max + 1)
SELECT lo.max + 1 AS min_range
    FROM example lo,example hi
    WHERE hi.min - (lo.max - 1) >= 40   -- Example won't work with 50
      AND NOT EXISTS (SELECT * FROM example AS mid
                         WHERE mid.min > lo.max
                           AND mid.max < hi.min
                     )

NOT EXISTS子句至关重要-它确保您仅考虑相邻范围.

这涉及“差距足够大”的情况.

名义上,您可以使用UNION子句处理“没有足够大的间隙”：

...
UNION
SELECT MAX(max)+1
    FROM example
    WHERE NOT EXISTS(
        SELECT lo.max + 1 AS min_range
            FROM example lo,example hi
            WHERE hi.min - (lo.max - 1) >= 40   -- Example won't work with 50
              AND NOT EXISTS (SELECT * FROM example AS mid
                                 WHERE mid.min > lo.max
                                   AND mid.max < hi.min
                             )
            )

内部SELECT是第一个缩进的直接转录.

上面的SQL未经测试.第一部分工作(尤其是在测试数据上)-但是可以产生多个答案.因此,需要将其修订为(我认为,修正了二分之一错误)：

SELECT MIN(lo.max + 1) AS min_range
    FROM example lo,example hi
    WHERE hi.min - (lo.max + 1) >= 40   -- Example won't work with 50
      AND NOT EXISTS (SELECT * FROM example AS mid
                         WHERE mid.min > lo.max
                           AND mid.max < hi.min
                     )

UNION子句让我有些悲伤…没有产生我期望的答案.

在语法上,我必须将其修改为：

SELECT MIN(lo.max + 1) AS min_range
    FROM example lo,example hi
    WHERE hi.min - (lo.max + 1) >= 40   -- Example won't work with 50
      AND NOT EXISTS (SELECT * FROM example AS mid
                         WHERE mid.min > lo.max
                           AND mid.max < hi.min
                     )
UNION
SELECT MAX(solo.max)+1
    FROM example AS solo
    WHERE NOT EXISTS(
        SELECT MIN(lo.max + 1) AS min_range
            FROM example lo,example hi
            WHERE hi.min - (lo.max - 1) >= 40   -- Example won't work with 50
              AND NOT EXISTS (SELECT * FROM example AS mid
                                 WHERE mid.min > lo.max
                                   AND mid.max < hi.min
                             )
            )

这可以避免使用关键字MAX作为列名的问题(我可能写了example.max而不是solo.max.但是它并没有产生我期望的答案.

在这种情况下,UNION等效于OR,并且此查询似乎产生了我想要的答案：

SELECT MIN(lo.max + 1) AS min_range
    FROM example lo,example hi
    WHERE (hi.min - (lo.max + 1) >= 40
           AND NOT EXISTS (SELECT * FROM example AS mid
                              WHERE mid.min > lo.max
                                AND mid.max < hi.min
                          )
          )
       OR lo.max = (SELECT MAX(solo.max) FROM Example AS Solo)
;

至关重要的是,OR子句引用lo.max而不是hi.max.否则,您将得到错误的答案.

OK-UNION版本注定要失败,因为SQL错误定义了MIN的行为.具体来说,如果没有匹配的行,则MIN返回值为NULL的一行,而不返回任何行.这意味着,在未找到任何行的情况下,UNION的第一子句将返回NULL.第二个子句可以通过在NOT EXISTS内部省略SELECT中的MIN来“固定”,但是您仍然从语句中得到两行(NULL和正确值),这实际上是不可接受的.因此,OR版本是要使用的版本-SQL再次使用NULL值进行咬合.

可以通过在FROM子句中的表表达式中构造UNION来严格避免使用null.最终会变得稍微简单一些：

SELECT MIN(min_range)
    FROM (SELECT (lo.max + 1) AS min_range
              FROM example lo,example hi
              WHERE hi.min - (lo.max + 1) >= 49
                AND NOT EXISTS (SELECT * FROM example AS mid
                                   WHERE mid.min > lo.max
                                     AND mid.max < hi.min
                               )
          UNION
          SELECT MAX(solo.max + 1) AS min_range
              FROM example AS solo
         );

UNION的前半部分可以返回任意数量的时隙,包括零；第二个总是返回一个值(只要表中有任何行).然后,外部查询选择这些值中的最低者.

当然,此版本可用于分配行：

INSERT INTO Example(min,max)
    SELECT MIN(min_range) AS min,MIN(min_range) + (50 - 1) AS max
        FROM (SELECT (lo.max + 1) AS min_range
                  FROM example lo,example hi
                  WHERE hi.min - (lo.max + 1) >= 50
                    AND NOT EXISTS (SELECT * FROM example mid
                                       WHERE mid.min > lo.max
                                         AND mid.max < hi.min
                                   )
              UNION
              SELECT MAX(solo.max + 1) AS min_range
                  FROM example AS solo
             );

（编辑：云计算网_泰州站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!