向sql专家快速提问。
我有一个表,其中包含两列-最小值和最大值
我一直试图编写一个查询,在最小和最大值之间找到第一个大小为n的孔,但没有成功
例子

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

如果我想找到一个大小为50的孔,则返回第1行的最大值200(在该孔和第2行的最小值之间有一个50的孔),返回第20行的孔,则返回第2行的最大值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
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
                             )
            )

内部选择是第一个缩进的直接转录。
上面的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
                     )

工会条款给了我一些悲伤…没有给出我期望的答案。
在句法上,我不得不将其修改为:
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。但这并没有给我带来我期望的答案。
联合相当于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是很重要的;否则,您将得到错误的答案。
好-联合版本是注定的,因为SQL错误地定义了min的行为。特别是,如果没有匹配的行,那么min返回值为空的单行,而不是不返回行。这意味着当找不到行时,联合体的第一个子句返回一个空值;第二个子句可以通过从not exists中的select中省略min来“修复”,但是您仍然会在语句中得到两行(一个空值和正确的值),这是不可接受的。因此,或版本是要使用的版本-并且SQL再次使用空值。
可以通过在from子句的表表达式中构造联合来严格避免空值。这最终变得稍微简单了一点:
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
         );

联合的前半部分可以返回任意数量的槽(包括0);第二部分总是返回一个值(只要表中有任何行)。然后外部查询选择这些值中的最低值。
当然,此版本可用于分配行:
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
             );

关于sql - SQL查询在表中查找min_numbers和max_number之间的漏洞,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/624333/

10-11 21:56
查看更多