Faisant suite au billet précédent, on aurait pu vouloir les plages de trous entre les séries de valeurs pour chaque famille et donnee.
Une requête permet d’y répondre…
Création de la table et insertion des données
Const cTable As String = "tFamilles"
With DoCmd
.SetWarnings False
.RunSQL "CREATE TABLE " & cTable & _
" (Famille LONG, Donnee VARCHAR(15), Valeur INTEGER," & _
" CONSTRAINT PrimaryKey PRIMARY KEY (Famille, Donnee, Valeur));"
.SetWarnings True
End With
InsertionFamilles
End Function
Public Sub InsertionFamilles()
Const cNombreFamille As Integer = 1
Const cTable As String = "tFamilles"
Const cInsert As String = "INSERT INTO " & cTable & " VALUES "
Dim i As Integer
With DoCmd
.SetWarnings False
i = Nz(DMax("famille", cTable)) + 1
For i = i To i + cNombreFamille - 1
.RunSQL cInsert & "(" & i & ", 'div', 1302);", False
.RunSQL cInsert & "(" & i & ", 'div', 1303);", False
.RunSQL cInsert & "(" & i & ", 'div', 1308);", False
.RunSQL cInsert & "(" & i & ", 'dov', 1290);", False
.RunSQL cInsert & "(" & i & ", 'dov', 1292);", False
.RunSQL cInsert & "(" & i & ", 'dov', 1293);", False
.RunSQL cInsert & "(" & i & ", 'dov', 1294);", False
.RunSQL cInsert & "(" & i & ", 'dov', 1295);", False
.RunSQL cInsert & "(" & i & ", 'dov', 1296);", False
.RunSQL cInsert & "(" & i & ", 'dov', 1297);", False
.RunSQL cInsert & "(" & i & ", 'dov', 1300);", False
.RunSQL cInsert & "(" & i & ", 'dov', 1301);", False
Next i
.SetWarnings True
End With
MsgBox "Création terminée"
End Sub
Résultat attendu
1 div 1304 1307
1 dov 1291 1291
1 dov 1298 1299
La requête SQL
t1.donnee,
t1.valeur + 1 AS Mini,
t2.valeur - 1 AS Maxi
FROM tFamilles AS T1
INNER JOIN tFamilles AS T2
ON ( t1.donnee = t2.donnee )
AND ( t1.famille = t2.famille )
WHERE t1.valeur < T2.valeur - 1
AND (
SELECT COUNT(*)
FROM tfamilles T
WHERE t.famille = t1.famille
AND t.donnee = t1.donnee
AND t.valeur BETWEEN t1.valeur AND t2.valeur
) = 2;
Explications
Le principe de base (auto jointure interne) est le même que pour le billet précédent.
Par la clause WHERE suivante ‘t1.valeur < T2.valeur – 1′, on ne retient que les combinaisons de valeurs distantes (1 et 3 par exemple et non 1 et 2) puis la sous-requête compte le nombre de valeurs entre ces deux limites incluses. Si le compte est exactement égal à 2 on a donc un trou (gap pour les anglophones et autres…) dans la série.
La plage de trous est comprise entre la valeur mini + 1 et la valeur maxi – 1.
Si l’on souhaite aussi inclure les plages de trous externes aux valeurs de la table, une solution consiste à utiliser une requête UNION entre les deux limites choisies.
Par exemple, pour retourner toutes les plages de trous entre 1 et 2000 :
t1.donnee,
t1.valeur + 1 AS Mini,
t2.valeur - 1 AS Maxi
FROM tFamilles AS T1
INNER JOIN tFamilles AS T2
ON ( t1.donnee = t2.donnee )
AND ( t1.famille = t2.famille )
WHERE t1.valeur < T2.valeur - 1
AND (
SELECT COUNT(*)
FROM tfamilles T
WHERE t.famille = t1.famille
AND t.donnee = t1.donnee
AND t.valeur BETWEEN t1.valeur AND t2.valeur
) = 2
UNION
SELECT famille,
donnee,
1 AS Mini,
MIN(valeur) - 1 AS Maxi
FROM tFamilles
GROUP BY famille,
donnee
HAVING MIN(valeur) > 1
UNION
SELECT famille,
donnee,
MAX(valeur) + 1 AS Mini,
2000 AS Maxi
FROM tFamilles
GROUP BY famille,
donnee
HAVING MAX(valeur) < 2000;
@+
Philippe