Select pro vyber "der"

[Honza Pazdziora]

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