Voici une solution, suite à une colle que l’on vient de me poser a propos de la problèmatique suivante avec sql serveur :
Une table contient une colonne de type Int les valeurs de cette colonne doivent etre uniques, lors de l’ajout d’un nouvel enregistrement il faut aller chercher la première rupture de sequence et non pas ajouter la valeur maximale de la colonne + 1, par exemple :
create table Sequence
(
Id int not null
)
insert into Sequence (Id) Values (1)
insert into Sequence (Id) Values (2)
insert into Sequence (Id) Values (8)
insert into Sequence (Id) Values (3)
nous avons le resultat suivant :
1
2
8
3
la nouvelle valeur doit etre 4, voici comment avec une seule requete :
select MIN(A.Precedent) + 1
from (
select ID
, (select max(subsquence.ID) from Sequence as subsquence where subsquence.ID < Sequence.Id) as Precedent
from Sequence
where ID - (select max(subsquence.ID) from Sequence as subsquence where subsquence.ID < Sequence.Id) > 1) as A
Explication :
une premiere requete selectionne les identifiants et l’identifiant de la ligne dont l’identifiant est inférieur à celui en cours :
select ID
, (select max(subsquence.ID) from Sequence as subsquence where subsquence.ID < Sequence.Id) as Precedent
from Sequence
ensuite il suffit d’ajouter une contrainte qui determine que la difference entre ces 2 indentifiants est supérieure à 1 (cas d’une rupture de sequence ).
where ID - (select max(subsquence.ID) from Sequence as subsquence where subsquence.ID < Sequence.Id) > 1
puis de prendre l’identifiant minimum de la liste et ajouter 1 :
select MIN(A.Precedent) + 1
from (...) as A
Le résultat donne bien 4
3 commentaires:
Bon plan.
Merci
Merci ! Exactement ce qu'il me fallait, avec une petite adaptation pour obtenir un ID même lorsqu'il n'y a pas de trou, voici le trigger magique obtenu:
DELIMITER $$
DROP TRIGGER IF EXISTS `generate_ELEPHANT_TROMPE_ID` $$
CREATE TRIGGER generate_ELEPHANT_TROMPE_ID BEFORE INSERT ON ELEPHANT
FOR EACH ROW
BEGIN
DECLARE i_new_id NUMERIC;
DECLARE c_new_id CURSOR FOR
SELECT min(A.Precedent) + 1
FROM (
SELECT TROMPE_ID, (SELECT max(subsquence.TROMPE_ID) FROM ELEPHANT as subsquence WHERE subsquence.TROMPE_ID < ELEPHANT.TROMPE_ID) as Precedent
FROM ELEPHANT
WHERE TROMPE_ID - (SELECT max(subsquence.TROMPE_ID) FROM ELEPHANT as subsquence WHERE subsquence.TROMPE_ID < ELEPHANT.TROMPE_ID) > 1
) as A;
# valorise le TROMPE_ID si il est vide
IF(NEW.TROMPE_ID IS NULL)THEN
OPEN c_new_id;
FETCH c_new_id INTO i_new_id;
CLOSE c_new_id;
if (i_new_id is null) then
select max(TROMPE_ID) + 1 from ELEPHANT into i_new_id;
end if;
SET NEW.TROMPE_ID = i_new_id;
END IF;
END;$$
DELIMITER ;
Effectivement , pour eviter le curseur il suffisait d'ecrire :
select isnull(Max(A.Precedent), (select max(Id) from Sequence)) + 1
From ...
Enregistrer un commentaire