Bonjour,
Je coince sur une requête , voici un exemple d'input :
Output désiré :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24 SELECT 'Paris' as ville,5 as numero, 'A' as lettre UNION SELECT 'Paris' as ville,6 as numero, 'A' as lettre UNION SELECT 'Paris' as ville,8 as numero, 'B' as lettre UNION SELECT 'Paris' as ville,12 as numero, 'B' as lettre UNION SELECT 'Paris' as ville,19 as numero, 'A' as lettre UNION SELECT 'Paris' as ville,20 as numero, 'A' as lettre UNION SELECT 'Bruxelles' as ville,5 as numero, 'C' as lettre UNION SELECT 'Bruxelles' as ville,7 as numero, 'C' as lettre UNION SELECT 'Bruxelles' as ville,8 as numero, 'B' as lettre UNION SELECT 'Bruxelles' as ville,13 as numero, 'B' as lettre UNION SELECT 'Bruxelles' as ville,21 as numero, 'C' as lettre UNION SELECT 'Bruxelles' as ville,22 as numero, 'C' as lettre
Ce que je désire:
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24 SELECT 'Paris' as ville,5 as numero, 'A' as lettre , 1 as result UNION SELECT 'Paris' as ville,6 as numero, 'A' as lettre , 1 as result UNION SELECT 'Paris' as ville,8 as numero, 'B' as lettre , 2 as result UNION SELECT 'Paris' as ville,12 as numero, 'B' as lettre , 2 as result UNION SELECT 'Paris' as ville,19 as numero, 'A' as lettre , 3 as result UNION SELECT 'Paris' as ville,20 as numero, 'A' as lettre , 3 as result UNION SELECT 'Bruxelles' as ville,5 as numero, 'C' as lettre , 1 as result UNION SELECT 'Bruxelles' as ville,7 as numero, 'C' as lettre , 1 as result UNION SELECT 'Bruxelles' as ville,8 as numero, 'B' as lettre , 2 as result UNION SELECT 'Bruxelles' as ville,13 as numero, 'B' as lettre , 2 as result UNION SELECT 'Bruxelles' as ville,21 as numero, 'C' as lettre , 3 as result UNION SELECT 'Bruxelles' as ville,22 as numero, 'C' as lettre , 3 as result
-Regrouper par ville
-Pour chaque ville , trier par numéro croissant
-A chaque numéro est associée une lettre.Si la lettre change , je doit incrémenter le résultat (même si l'on repasse à la même lettre que précédemment ).
-Le résultat est remis à 0 à chaque changement de ville
-L'objectif est d'avoir la colonne result
Une idée?
Merci d'avance
Partager