Ich habe Dein Beispiel mal nachgebaut. Alle nicht belegten Zahlen zu selektieren ist IMHO unmöglich, daher zeige ich hier einen Weg, wie die jeweils nächste Lücke gefunden werden kann.
Ausgangssituation ist folgende Tabellenstruktur:
Code:
mysql> select * from fillnumbers;
+----+------+
| id | n |
+----+------+
| 1 | 1 |
| 2 | 4 |
| 3 | 6 |
| 4 | 9 |
| 5 | 18 |
| 6 | 2 |
+----+------+
6 rows in set (0.00 sec)
Die zwei wurde noch eingefügt, da die erste Lücke nach eins zu haben, auch mit diversen Querys richtige Ergebnisse bringt, die ansonsten falsch sind.
Als erstes benötigen wir ein Query, mit dem jeweils die nächste Zeile mit selektiert wird. Dabei können dann auch gleich die Zeilen ausgemustert werden, die einen direkten Nachfolger haben (in diesem Beispiel die Zeile mit n=1):
Code:
mysql> select
-> t1.id,
-> t1.n,
-> t2.n as nextNumber
-> from fillnumbers t1
-> join fillnumbers t2
-> on t1.n<t2.n
-> left join fillnumbers t3
-> on t1.n<t3.n
-> and t2.n>t3.n
-> where t3.id is null
-> and t2.n-t1.n>1;
+----+------+------------+
| id | n | nextNumber |
+----+------+------------+
| 6 | 2 | 4 |
| 2 | 4 | 6 |
| 3 | 6 | 9 |
| 4 | 9 | 18 |
+----+------+------------+
4 rows in set (0.00 sec)
Wenn man aus diesem Ergebnis das Minimum von n projeziert und 1 hinzu zählt, erhält man die nächste zu füllende Lücke:
Code:
mysql> select
-> min(t1.n)+1 as firstNewNumber
-> from fillnumbers t1
-> join fillnumbers t2
-> on t1.n<t2.n
-> left join fillnumbers t3
-> on t1.n<t3.n
-> and t2.n>t3.n
-> where t3.id is null
-> and t2.n-t1.n>1;
+----------------+
| firstNewNumber |
+----------------+
| 3 |
+----------------+
1 row in set (0.00 sec)
Fügt man diese Zahl jetzt ein, kann man mit dem gleichen Query die nächste suchen:
Code:
mysql> insert into fillnumbers set n=3;
Query OK, 1 row affected (0.00 sec)
mysql> select * from fillnumbers order by n;
+----+------+
| id | n |
+----+------+
| 1 | 1 |
| 6 | 2 |
| 7 | 3 |
| 2 | 4 |
| 3 | 6 |
| 4 | 9 |
| 5 | 18 |
+----+------+
7 rows in set (0.01 sec)
mysql> select
-> min(t1.n)+1 as firstNewNumber
-> from fillnumbers t1
-> join fillnumbers t2
-> on t1.n<t2.n
-> left join fillnumbers t3
-> on t1.n<t3.n
-> and t2.n>t3.n
-> where t3.id is null
-> and t2.n-t1.n>1;
+----------------+
| firstNewNumber |
+----------------+
| 5 |
+----------------+
1 row in set (0.00 sec)
Das kann man fortführen, bis alle Lücken gefüllt sind.
Sind dann alle Lücken gefüllt, gibt das Query null zurück:
Code:
mysql> select * from fillnumbers order by n;
+----+------+
| id | n |
+----+------+
| 1 | 1 |
| 6 | 2 |
| 7 | 3 |
| 2 | 4 |
| 8 | 5 |
| 3 | 6 |
| 9 | 7 |
| 18 | 8 |
| 4 | 9 |
| 10 | 10 |
| 11 | 11 |
| 12 | 12 |
| 13 | 13 |
| 14 | 14 |
| 15 | 15 |
| 16 | 16 |
| 17 | 17 |
| 5 | 18 |
+----+------+
18 rows in set (0.00 sec)
mysql> select
-> min(t1.n)+1 as firstNewNumber
-> from fillnumbers t1
-> join fillnumbers t2
-> on t1.n<t2.n
-> left join fillnumbers t3
-> on t1.n<t3.n
-> and t2.n>t3.n
-> where t3.id is null
-> and t2.n-t1.n>1;
+----------------+
| firstNewNumber |
+----------------+
| NULL |
+----------------+
1 row in set (0.00 sec)
In diesem Fall muss man das Maximum plus eins projezieren, um die nächste Zahl zu erhalten:
Code:
mysql> select max(n) + 1 as nextNumber from fillnumbers;
+------------+
| nextNumber |
+------------+
| 19 |
+------------+
1 row in set (0.00 sec)
Mit MySQL >= Version 4.1 kannst Du das direkt in einem Subquery machen:
Code:
mysql> select
-> if (min(t1.n) is null,
-> (select max(n) + 1 from fillnumbers),
-> min(t1.n)+1) as firstNewNumber
-> from fillnumbers t1
-> join fillnumbers t2
-> on t1.n<t2.n
-> left join fillnumbers t3
-> on t1.n<t3.n
-> and t2.n>t3.n
-> where t3.id is null
-> and t2.n-t1.n>1;
+----------------+
| firstNewNumber |
+----------------+
| 19 |
+----------------+
1 row in set (0.00 sec)
Dafür fällt mir aber kein Workaround ohne Subquery ein, der nicht auch das andere Ergebnis beeinflussen würde. In MySQL < 4.1 sind also zwei Queries nötig. Wenn man jedoch ohnehin mit einer Version ab 4.1 arbeitet, kann man es auch gleich mit Subqueries verkürzen. Für das Beispiel habe ich die Tabelle wieder gekürzt:
Code:
mysql> select * from fillnumbers order by n;
+----+------+
| id | n |
+----+------+
| 1 | 1 |
| 6 | 2 |
| 8 | 5 |
| 3 | 6 |
| 4 | 9 |
| 10 | 10 |
| 11 | 11 |
+----+------+
7 rows in set (0.00 sec)
mysql> select
-> if (min(t1.n) is null,
-> (select max(n) + 1 from fillnumbers),
-> min(t1.n)+1) as firstNewNumber
-> from fillnumbers t1
-> where
-> ( select min(t2.n)
-> from fillnumbers t2
-> where t2.n>t1.n
-> )
-> - t1.n > 1;
+----------------+
| firstNewNumber |
+----------------+
| 3 |
+----------------+
1 row in set (0.00 sec)
Gruß hpvw
