仅选择数组中不在表中的数字

I have a table in MySql where I save some data let's assume a name and a stand. I know that the stands will be from 1 to 100, i would like to select those stands that aren't taken. For example let's assume that whe have only 5 stands and this table:

我在MySql中有一个表，我保存了一些数据，让我们假设一个名字和一个立场。我知道看台将从1到100，我想选择那些未被占用的看台。例如，让我们假设只有5个看台和这个表：

|  name  |  stand  |
--------------------
|  test  |    1    |
|  anot  |    3    |
|  blah  |    4    |
|  uuuh  |    5    |

in this case the only free stand will be the 2.

在这种情况下，唯一的免费展位将是2。

is there a statement to do that ? ... I was thinking at the clause NOT IN but I can't figure out the code... maybe if I can define am Array in MySql?

有没有声明呢？ ...我正在考虑条款NOT IN但我无法弄清楚代码......也许我可以在MySql中定义am Array吗？

3 个解决方案

#1

If you know the values are from 1 to 100, then you can do this:

如果您知道值为1到100，那么您可以这样做：

select n.num
from (select d1.d*10 + d2.d as n
      from (select 0 as d union all select 1 union all select 2 union all select 3 union all select 4 union all
            select 5 union all select 6 union all select 7 union all select 8 union all select 9
           ) d1 cross join
           (select 0 as d union all select 1 union all select 2 union all select 3 union all select 4 union all
            select 5 union all select 6 union all select 7 union all select 8 union all select 9
           ) d2
      ) nums left outer join
      stands s
      on s.stand = nums.n cross join
      (select min(stand) as minstand and max(stand) as maxstand from stands) const
where s.stand is null and nums.n between minstand and maxstand;

This is not tested, so it may have syntax errors.

这未经过测试，因此可能存在语法错误。

That is, create a table with all possible values (1 to 100). Left join this to your table. This gives you all the numbers that are not used. However, you want to limit it to the min and max values, so calcualte these and use them for filterin.

也就是说，创建一个包含所有可能值（1到100）的表。左边加入你的桌子。这将为您提供所有未使用的数字。但是，您希望将其限制为最小值和最大值，因此请计算这些值并将其用于过滤器。

#2

You can use a table of integers to get a range of numbers.

您可以使用整数表来获取一系列数字。

Say:-

说：-

SELECT a.i + b.i * 10 + c.i * 100 FROM integers a, integers b, integers c

With a table called integers with a single column called i, with 10 rows with the values 0 to 9 that will get you all the numbers between 0 and 999 (you can easily join against the table a few more times to get larger numbers).

使用一个名为整数的表，其中一列名为i，其中10行的值为0到9，它们将为您提供0到999之间的所有数字（您可以轻松地连接几次以获得更大的数字）。

You could join that against you table to find the unused numbers:-

您可以加入针对您的表来查找未使用的数字： -

SELECT *
FROM (SELECT a.i + b.i * 10 + c.i * 100 AS anInt FROM integers a, integers b, integers c) Sub1
LEFT OUTER JOIN someTable
ON Sub1.anInt = someTable.stand
WHERE someTable.stand IS NULL

This will work, but will give you all the numbers either end of the range in stand as well. for these you need to join against the min and max

这样可以工作，但是也可以为你提供所有数字。对于这些你需要加入最小和最大

SELECT Sub1.anInt
FROM (SELECT a.i + b.i * 10 + c.i * 100 AS anInt FROM integers a, integers b, integers c) Sub1
INNER JOIN (SELECT MIN(stand) AS MinStand, MAX(stand) AS MaxStand FROM someTable) Sub2
ON Sub1.anInt >= Sub2.MinStand AND Sub1.anInt <= Sub2.MaxStand
LEFT OUTER JOIN someTable
ON Sub1.anInt = someTable.stand
WHERE someTable.stand IS NULL

#3

A simpler solution, in my opinion, would be to have a table with all the number between one and 100. Then do a left join from that table to the stands table and show any values that don't match.

在我看来，一个更简单的解决方案是使用一个包含1到100之间所有数字的表。然后从该表左边连接到stand表并显示任何不匹配的值。

You could create a table called "integer_values" with a field called "value". Then populate it with all the numbers from 1 to 100. Then you could use the following query:

您可以创建一个名为“integer_values”的表，其中包含一个名为“value”的字段。然后使用从1到100的所有数字填充它。然后您可以使用以下查询：

SELECT  i.value

FROM    integer_values AS i

        LEFT JOIN stands AS s
        ON i.value = s.stand

WHERE   s.stand IS NULL

#1