SQL查询在表中查找min_numbers和max_number之间的漏洞。

Quick question for the SQL gurus.

对于SQL专家来说，这是一个快速的问题。

I have a table which contains, amongst others, two columns - min_number and max_number I have been trying unsuccessfully to write a query which finds the first hole of n size between the min and max numbers

我有一个表，其中包含两个列——min_number和max_number，我一直试图编写一个查询，该查询在最小值和最大值之间找到第一个n大小的洞

Example

例子

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

If I want to find a hole of size 50, row 1's max of 200 would be returned (there is a hole of 50 between that and row 2's min), a hole of 20 would return row 2s max of 300 etc. If no suitable sized hole existed the last max (400) would be returned.

如果我想找一个洞的大小50,第一行的max 200将返回(50之间有一个洞,和行2分钟),20个洞将返回行2 s 300 max等。如果没有合适大小的洞存在最后马克斯(400)将返回。

Thanks

谢谢

6 个解决方案

#1

Edited: final answer is at the bottom.

编辑:最终答案在底部。

Why do so many SQL questions forget the table name?

为什么这么多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
                     )

The NOT EXISTS clause is crucial - it ensures that you are only considering adjacent ranges.

不存在子句是至关重要的——它确保您只考虑相邻的范围。

This deals with the 'there is a gap big enough' case.

这就解决了“差距足够大”的问题。

Nominally, you can deal with the 'there is no gap big enough' with a UNION clause:

名义上，你可以用一个工会条款来处理“没有足够大的差距”

...
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
                             )
            )

The inner SELECT is a direct transcription of the first, indented.

内部选择是直接转录第一个，缩进。

---

The SQL above was untested. The first part works (especially on the test data) - but can produce multiple answers. So, it needs to be revised to (fixing, I think, an off-by-two error):

上面的SQL没有经过测试。第一部分起作用(特别是在测试数据上)——但是可以产生多个答案。因此，需要修改为(我认为，修正了一个误差为2的错误):

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
                     )

The UNION clause is giving me some grief...not producing the answer I expect.

工会条款让我有些伤心……没有给出我期望的答案。

Syntactically, I had to amend it to:

从句法上来说，我必须把它改成:

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
                             )
            )

This circumvents problems with the keyword MAX being used as a column name (I could probably have written example.max instead of solo.max. But it isn't producing me the answer I expect.

这避免了将关键字MAX用作列名的问题(我可能已经编写了示例)。代替solo.max马克斯。但它并没有给我我所期望的答案。

---

A UNION is equivalent to an OR, certainly in this case, and this query seems to produce the answer I want:

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)
;

It is crucial that the OR clause cite lo.max and not hi.max; otherwise, you get the wrong answer.

OR子句引用lo是至关重要的。麦克斯和不是hi.max;否则，你会得到错误的答案。

---

OK - the UNION version is doomed, because SQL misdefines the behaviour of MIN. Specifically, if there are no rows that match, then MIN returns a single row with the value NULL, rather than returning no rows. That means that the first clause of the UNION returns a NULL when there are no rows found; the second clause can be 'fixed' by omitting the MIN from the SELECT inside the NOT EXISTS, but you still end up with two rows (a NULL and the correct value) from the statement, which is not really acceptable. So, the OR version is the one to use - and SQL bites again with NULL values.

好的——联合版本注定要失败，因为SQL错误地定义了最小值的行为，特别是，如果没有匹配的行，那么MIN返回一个值为NULL的单行，而不是不返回任何行。这意味着当没有找到行时，UNION的第一个子句返回NULL;第二个子句可以通过从NOT exist内部的SELECT中省略最小值来“固定”，但是您仍然会从语句中得到两行(NULL和正确的值)，这是不可以接受的。因此，OR版本是使用-和SQL的字节再次使用空值。

Rigorously avoiding nulls can be done by framing the UNION in a table expression in the FROM clause. This ends up being slightly simpler:

严格地避免null可以通过在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
         );

The first half of the UNION can return any number of slots including zero; the second always returns a value (as long as there are any rows in the table at all). The outer query then chooses the lowest of these values.

联盟的前半部分可以返回任意数量的插槽，包括零;第二个总是返回一个值(只要表中有任何行)。然后，外部查询选择这些值的最小值。

This version can, of course, be used to allocate rows:

当然，这个版本可以用于分配行:

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
             );

#2

SELECT
     MIN(T1.max_value)
FROM
     My_Table T1
LEFT OUTER JOIN My_Table T2 ON
     T2.min_value BETWEEN (T1.max_value + 1) AND (T1.max_value + @range)
WHERE
     T2.id IS NULL

I'm guessing that since you are looking for IDs to assign, that you want a range of values completely exclusive of the max_value and min_value.

我猜，由于您正在寻找要分配的id，所以您需要一个完全排除max_value和min_value的值范围。

You can also do the above query with a NOT EXISTS clause. Try it with both and see which performs better for you.

您还可以使用一个不存在子句执行上述查询。两种都试试，看看哪一种更适合你。

Another thing to consider is, do you really need to reuse IDs? Are your ID values going to get so high and your range available so low that you will need to do that? I don't know the specifics of your system, but it just seems like you might be spending a lot of effort and then using a lot of extra processing to solve a problem that doesn't really exist.

另一件需要考虑的事情是，您真的需要重用id吗?你的ID值会变得这么高，你的可用范围又这么低，你需要这么做吗?我不知道你的系统的具体细节，但是看起来你可能花了很多精力然后用很多额外的处理来解决一个实际上并不存在的问题。

#3

select min(n+1) from myTable where n+1 NOT IN  (select n from myTable)

• R Doherty
• R多尔蒂

#4

Personally, I wouldn't try to do that in SQL - AIUI it's difficult to perform analysis across different rows without effectively having to scan the table in O(n^2) in the worst case. That would possibly be easier using a stored procedure, though.

就我个人而言,我不会试图用SQL - AIUI很难执行分析在不同的行无需有效扫描表O(n ^ 2)在最坏的情况下。不过，使用存储过程可能更容易实现。

My solution, if you're able, would be to change your database schema and code so that each time a new row is inserted, the previous row is updated with the difference between the max of that row and the min of the new row, with that difference value stored in its own column.

我的解决方案,如果你能,就改变你的数据库模式和代码,这样每次插入一个新行,前面的行是更新的区别最大的行和最小的新行,这种差异值存储在自己的专栏。

Searching for the first row that has a gap large enough then becomes relatively trivial.

搜索第一行的间隙足够大，那么搜索第一行就变得相对不重要了。

#5

Have MySQL model clause? If yes you can do it with a query with it.

MySQL模型条款吗?如果是，你可以用查询来完成。

#6

"a hole of 20 would return row 2s max of 300 etc" I'm not following your logic there - the gap between the maximum of row 2 (300) and minimum of row 3 (330) is 30 (if you are including either the min or max values, 29 if not). Does this mean you are looking for the first gap of "greater than or equal to" the specified value, or does the gap need to be an exact match? If it is "greater than or equal to" then surely the first match returned would be row 1, which has a gap > 20 between it and row 2?

“一个20的洞会返回第2行最大的300”，“我没有按照你的逻辑去做——第2行(300)和第3行(330)的最小值之间的差距是30(如果你包括最小值或最大值，如果不是，则是29)。这是否意味着您正在寻找“大于或等于”指定值的第一个间隙，还是该间隙需要精确匹配?如果它“大于或等于”，那么返回的第一个匹配一定是第1行，它与第2行之间的间隔是> 20 ?

At any rate if your table has a row ID of some sort, as the example seems to indicate, then you could try something like this (assuming a table MyTable with columns RowID, MinVal and MaxVal populated with the data in your example):

无论如何，如果您的表具有某种类型的行ID(如示例所示)，那么您可以尝试类似的操作(假设表MyTable具有RowID、MinVal和MaxVal列，其中填充了示例中的数据):

SELECT TOP 1
        a.RowID,
        a.MinVal,
        a.MaxVal, -- this is the value you want to return
        ISNULL(b.MinVal, 9999) AS MinVal_NextRow,
        ISNULL(b.MinVal, 9999) - a.MaxVal AS Diff
FROM    MyTable a
        LEFT JOIN MyTable b ON a.RowID = ( b.RowID - 1 )
WHERE   ( ISNULL(b.MinVal, 9999) - a.MaxVal ) = 20

This example selects the first row where the gap is exactly 20. If you were looking for the first gap of at least 20, then you could change the WHERE clause to :

这个示例选择第一行的差值恰好为20。如果你正在寻找至少20的第一个差距，那么你可以将WHERE子句改为:

WHERE   ( ISNULL(b.MinVal, 9999) - a.MaxVal ) >= 20

The query substitutes an arbitrarily large number (9999) in for when the row is the last one available - this is what returns the last (largest) MaxVal if there are no gaps of a suitable size. You would need to adjust this number to something that made sense for your data (i.e. larger than any possible values in the data).

当行是最后一个可用的行时，查询替换为任意大的数字(9999)——如果没有合适大小的间隔，这将返回最后一个(最大的)MaxVal。您需要将这个数字调整为对您的数据有意义的东西(例如，大于数据中任何可能的值)。