Geek asks and answers

Selecting only those numbers that are in the array AND not in the table

I have a table in MySql where I save some data, let's say nameand stand. I know that the stands will be from 1 to 100, I would like to choose those stands that were not taken. For example, suppose whe has only 5 tables and this table:

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

in this case the only free stand will be 2.

is there any instruction for this? ... I was thinking about an article NOT IN, but I can’t understand the code ... maybe if I can define am Array in MySql?

+5
source share
3 answers

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

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 verified, so it may have syntax errors.

, ( 1 100). . , . , min max, .

+2

.

: -

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

, i, 10 0 9, 0 999 ( , ).

, : -

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

, . min 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
+1

, , 100. , .

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

SELECT  i.value

FROM    integer_values AS i

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

WHERE   s.stand IS NULL
+1
source

More articles:


All Articles