Renumérotation de lignes par catégorie

Bonjour à tous,

JE suis sur un problème de renumérotation. Je voudrais que, indépemdament pour chaque catégorie, un numéro d'ordre soit donné à chaque enregistrement, et qu'il n'y ait pas de trou.

Désolé de ne pas pouvoir être plus explicite. JE pense que vous comprendrez mieux avec l'exemple .

Créons la situation de départ :

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

create table Test (
id int unsigned auto_increment,
name varchar(255) not null unique,
categ int unsigned not null,
value int unsigned not null default 0,
primary key(id));

insert into Test (name, categ) values
('Alice', 1),
('Bob', 1),
('Cédric', 1),
('David', 1),
('Albert', 2),
('Bertrand', 2),
('Claude', 2),
('Dylan', 2),
('Adeline', 3),
('Béatrice', 3),
('Cécile', 3),
('Delphine', 3);

select * from Test order by categ, name;

Situation de départ :

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

id  name    categ   value
1   Alice   1   0
2   Bob 1   0
3   Cédric  1   0
4   David   1   0
5   Albert  2   0
6   Bertrand    2   0
7   Claude  2   0
8   Dylan   2   0
9   Adeline 3   0
10  Béatrice    3   0
11  Cécile  3   0
12  Delphine    3   0

ET après si possible un seul, mais éventuellement plusieurs updates, je voudrais obtenir ceci:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

id  name    categ   value
1   Alice   1   1
2   Bob 1   2
3   Cédric  1   3
4   David   1   4
5   Albert  2   1
6   Bertrand    2   2
7   Claude  2   3
8   Dylan   2   4
9   Adeline 3   1
10  Béatrice    3   2
11  Cécile  3   3
12  Delphine    3   4

En d'autres terme, Alice, Bob, Cédric et David reçoivent respectivement le numéro 1, 2, 3 et 4 de la catégorie 1. On recommence à 1 pour numéroter les membres de la catégorie suivante, et ainsi de suite.

L'ordre alphabétique est pratique donc c'est cool si on peut le garder, mais ce n'est pas une obligation; je peux très bien faire sans. La table originale a 5000 lignes, plein de colonnes en plus, et il existe plusieurs dizaines de catégories…

En tant que première approche, j'ai essayé ceci :

1
2
3
4
5

set @c :=0;

update Test set value = (
select @c := @c+1
) order by name;

Sans surprise, je n'obtiens bien sûr pas encore ce que je veux :

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

id  name    categ   value
1   Alice   1   3
2   Bob 1   6
3   Cédric  1   8
4   David   1   10
5   Albert  2   2
6   Bertrand    2   5
7   Claude  2   9
8   Dylan   2   12
9   Adeline 3   1
10  Béatrice    3   4
11  Cécile  3   7
12  Delphine    3   11

La numérotation est globale et non plus par catégorie, et je ne vois vraiment pas comment je pourrais déclarer un compteur différent pour chaque catégorie. Alors du coup j'ai essayé complètement autre chose, qui ne fonctionne pas du tout:

1
2
3
4
5
6

update Test set value = (
select 1+max(t.value) as value
from Test t
where t.categ=Test.categ
) 
order by name;

J'avoue que je ne comprends pas trop pourquoi il refuse d'exécuter cette requête. IL y a apparament une limitation qui empêche, dans le sub-select, de faire référence à la table qu'on est en train de modifier.

J'ai quand même fini par trouver une variante qui marche, mais je n'obtiens quand même pas ce que je veux :

1
2
3
4
5
6
7
8

update Test set value = (
select value from (
select 1+max(t.value) as value, categ as c
from Test t
group by c
) z where Test.categ=c
) 
order by name;

Mais je ne m'attendais pas à obtenir ceci :

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

id  name    categ   value
1   Alice   1   1
2   Bob 1   1
3   Cédric  1   1
4   David   1   1
5   Albert  2   1
6   Bertrand    2   1
7   Claude  2   1
8   Dylan   2   1
9   Adeline 3   1
10  Béatrice    3   1
11  Cécile  3   1
12  Delphine    3   1

D'un côté on peut se dire tiens, il n'est pas si con, il n'exécute pas la sous-sous-requête à chaque fois. Sauf que dans le cas présent ça ne m'arrange pas…

J'ai aussi essayé de détourner la limitation en utilisant une vue :

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

create view TestView as
select categ as ctg, 1+max(value) as value
from Test
group by categ;

update Test set value = (
select value from TestView where ctg = Test.categ
) order by name;

drop view TestView;

Bizarrement, en passant par une vue, il ne rechigne plus à accéder à la table qui est en cours de modification. Mais le résultat est le même, toutes les lignes obtiennent la valeur 1.

Comment faire ?

Merci pour votre aide.

Bonsoir, Si j'ai bien compris tu veux mettre en place une sous-séquence sur le champ categ, la requête suivante pourrait fonctionner mais il faut la lancer pour chaque valeur de ce champ (ou l'inclure dans une procédure avec un curseur bouclant sur ses valeurs distinctes)

update Test set value = (select @rownum:=@rownum+1 rownum FROM (SELECT @rownum:=0) r) where categ = 1 order by name asc

la requête suivante pourrait fonctionner mais il faut la lancer pour chaque valeur de ce champ (ou l'inclure dans une procédure avec un curseur bouclant sur ses valeurs distinctes)

Donc c'est bien ce que j'avais imaginé, il faut le faire séparément pour chaque catégorie une par une.

Est-ce qu'il y a un moyen de le faire en SQL sans passer par Java ?

En gros, ça donc; sauf que la syntaxe est évidemment incorrecte :

1
2
3
4
5
6
7
8

for i in (select distinct categ from Test) {
set @c := 0;
update Test set value = (
select @c := @c+1
) 
where categ = i
order by name;
}

Tu peux le faire en SQL avec un curseur

Tu peux le faire en SQL avec un curseur

Je ne connaissais pas, et effectivement c'était la solution.

Voici le code :

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

create procedure renumber()
begin
declare ctg int;--
declare done int default 0;--
declare cur cursor for select distinct categ from Test;--
declare continue handler for not found set done = 1;--
open cur;--
readLoop: loop
fetch cur into ctg;--
if done then  
leave readLoop;--
end if;--
set @c := 0;--
update Test set subid = (
select @c := @c +1
) 
where categ = ctg
order by name;--
end loop;--
close cur;--
end;

Le sujet est donc résolu.

Merci !