Select pro vyber "der"
Honza Pazdziora
adelton na informatics.muni.cz
Pondělí Listopad 8 18:28:55 CET 1999
On Mon, Nov 08, 1999 at 02:21:18PM +0100, Martin Horak wrote:
> Preji pekny den.
> Jakozto zacatecnik s SQL jsem narazil na nasledujici problem:
> V tabulce ve sloupci dejme tomu ID je _temer_ souvisla rada cisel.
> Potrebuji ted napsat SELECT, ktery by vybral "diry" v teto rade
> (prakticky - porebuji pridelit nejnizsi volnou IP adresu).
> Myslel jsem, ze by to vyresil nasledujici SELECT:
> select ID + 1 from tab A
> where not exist(
> select ID from tab B
> where A.ID = B.ID + 1
> );
> Bohuzel MySQL mi tento SELECT nebere - asi nepodporuje vnoreny SELECT
> v klauzuli WHERE. Slo by tento pripad nejak vyresit?
Je mozno to nahradit outer joinem:
mysql> select a.id as aid, b.id as bid from test_diry a
left outer join test_diry b on a.id = b.id - 1 order by aid;
+------+------+
| aid | bid |
+------+------+
| 0 | 1 |
| 1 | 2 |
| 2 | NULL |
| 5 | 6 |
| 6 | NULL |
| 9 | NULL |
+------+------+
6 rows in set (0.06 sec)
(v tom aid sloupci je aktualni obsah tabulky). Cili
mysql> select min(a.id) + 1
from test_diry a left outer join test_diry b on a.id = b.id - 1
where b.id is null ;
+---------------+
| min(a.id) + 1 |
+---------------+
| 3 |
+---------------+
1 row in set (0.02 sec)
mi vezme prvni zaznam, ktery ma vpravo null, a zvysi o jedna -> 3.
Postupne ty vracene hodnoty pridavam a vraci mi to 4, 7, 8, 10, 11, ...
takze bych to indukci ;-) prohlasil za funkcni.
------------------------------------------------------------------------
Honza Pazdziora | adelton na fi.muni.cz | http://www.fi.muni.cz/~adelton/
.project: Perl, DBI, Oracle, MySQL, auth. WWW servers, MTB, Spain, ...
------------------------------------------------------------------------
Další informace o konferenci Databases