Solutions pour le petit jeu des requêtes SQL

SQLPro

SQLpro, c'est aussi un expert langage SQL, SGBD relationnels, Microsoft SQL Server à votre service :
expertise, audit, conseil, assistance, formation, analyse, modélisation de données & développement... MVP SQL Server

- Titre : Solutions pour le petit jeu des requêtes SQL

- Auteur : SQLPro

- Parution : 28 septembre 2011

- Licence : Copyright ®  SQLPro. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.

Sommaire

- I. Solutions - 1° partie

- I-A. Solution au problème n° 1 - un dans dix

- I-A-1. Solution question 1 : les lignes de la table dont toutes les cellules f1 à f10 sont à zéro sauf une

- I-A-1-a. Solution 1

- I-A-1-b. Solution 2

- I-A-1-c. Solution 3

- I-A-1-d. Solution 4

- I-A-1-e. Solution 5

- I-A-1-f. Solution 6

- I-A-2. Solution question 2 : les lignes de la table dont toutes les cellules f1 à f10 sont à zéro sauf une valuée à UN...

- I-A-2-a. Solution 1

- I-A-2-b. Solution 2

- I-A-2-c. Solution 3

- I-A-2-d. Solution 4

- I-A-2-e. Solution 5

- I-A-2-f. Solution 6

- I-B. Solution au problème n° 2 : le publipostage

- I-C. Solution au problème n° 3 : la date an 2000

- I-D. Solution au problème n° 4 : les chambres libres

- I-D-1. Les chambres qui sont libres pendant toute la période allant du 11 au 14 janvier 2000 ?

- I-D-2. L'occupation des chambres pour la journée du 13 janvier 2000 ?

- I-D-3. Le planning des occupations pour toutes les chambres et toutes les dates du 11 au 14 janvier 2000 ?

- I-E. Problème n° 5 : date d'anniversaire

- I-E-1. Les anniversaires entre le 21 février et le 20 mars

- I-E-2. Les anniversaires entre le 21 décembre et le 20 janvier

- I-E-3. La requête paramétrée générique

- I-F. Solution au problème n° 6 - énumération

- I-G. Solution au problème n° 7 - Le comptage

- I-H. Solution au problème n° 8 - linéarisation

- I-I. Solution au problème n° 9 - les Trous

- I-J. Solution au problème n° 10 - symétrie négative

- II. Solutions - 2° partie

- II-A. Solution au problème n° 11 - premiers

- II-B. Solution au problème n° 12 - traduction

- II-C. Solution au problème n° 13 - les bons joeurs

- II-D. Solution au problème n° 14 - tranches d'âge

- II-E. Solution au problème n° 15 - Les articles

- II-F. Solution au problème n° 16 - tri alphabétique

- II-G. Solution au problème n° 17 - appariement

- II-H. Solution au problème n° 18 - meilleure correspondance partielle

- II-I. Solution au problème n° 19 - la médiane

- II-I-1. Solution question 1 : les données à calculées sont toutes différentes (il n'y a pas de doublon) et en nombre impaires :

- II-I-2. Solution question 2 : les données à calculées sont toutes différentes (il n'y a pas de doublon) et en nombre paires :

- II-I-3. Solution question 3 : les données à calculer admettent des doublons et un nombre de lignes impairs

- II-J. Solution au problème n° 20 - insertion en bloc

- III. Solutions - 3° partie

- III-A. Solution au problème n° 21 - Ordonner et réordonner

- III-A-1. Solution question 1 : Un UPDATE combiné à un CASE permet de solutionner le problème

- III-A-2. Solution question 2 : Écrivons maintenant cette requête de manière générique

- III-A-3. Solution question 3 : Renuméroter l'ordre en partant de 1 avec continuité

- III-B. Solution au problème n° 22 - Jointure hétérogène multiple

- III-C. Solution au problème n° 23 - Insertion conditionnelle

- III-D. Solution au problème n° 24 - Un arbre à deux niveaux

- III-E. Solution au problème n° 25 - Éclater des lignes

- III-F. Solution au problème n° 26 - Noms incrémentés

- III-G. Solution au problème n° 27 - Une lettre un nom

- III-G-1. Solution brute :

- III-G-2. Variante 1 : obtenir les noms des "secondes" personnes

- III-G-3. Variante 2 : obtenir les noms des niemes personnes

- III-G-4. Variante 3 : obtenir les noms des niemes personnes, mais si elle n'existe pas, alors la dernière !

- III-G-5. Solutions avec fonctions analytiques de fenêtrage (norme SQL:2003)

- III-H. Solution au problème n° 28 - Filtrer les adresses IP

- III-I. Solution au problème n° 29 - Calculer l'adresses IP suivante

- IV. Solutions - 4° partie

- IV-A. Solution au problème n° 30 - Nombre de jours contigus

- IV-A-1. Étape n°1 : On recherche les débuts de plage ayant des dates contiguës

- IV-A-2. Étape n° 2 : la seconde requête n'est guère plus complexe, pour trouver les fins de périodes

- IV-A-3. Étape n°3 : Voici maintenant le temps venu de joindre ces éléments... Cela semble plus facile à dire qu'à faire !

- IV-A-4. Étape n°4 : la suite, et donc la touche finale, est on ne peut plus simple puisqu'il s'agit de compter le nombre de jours de chaque période ainsi extraite !

- IV-B. Solution au problème n° 31 - plus proche valeur

- IV-C. Solution au problème n° 32 - primes de salaires

- IV-D. Solution au problème n° 33 - affectations comptables

- IV-E. Solution au problème n° 33 - précédents (ou suivants)

- IV-F. Solution au problème n° 35 - matchs et victoires

- IV-G. Solution au problème n° 36 - tri bâtard

- IV-H. Solution au problème n° 37 - vote contraint

- IV-I. Solution au problème n° 38 - propriété bien gardée

- IV-I-1. Étape préliminaire

- IV-I-2. Étape 1 : bornage des tranches sur la période à scruter

- IV-I-3. Étape 2 : calcul de toutes les périodes possibles partant des périodes existantes

- 38-D. Étape 3 : on supprime maintenant les périodes incluses dans les autres afin de ne laisser que des périodes non "redondantes"

- 38-E. Étape 4 : en sus de de débarrasser de la redondance, on se débarrase des tranches faisant des "trous" au sein d'une même période

- 38-F. Étape 5 (finale): recherche des intervalles inverses

- 39. Solution au problème n° 39 - abstract et mots clefs

- 39-A. Le découpage n'est pas itératif

- 39-B. Le découpage n'est pas "fini"

I - Solutions - 1° partie

I-A - Solution au problème n° 1 - un dans dix

I-A-1 - Solution question 1 : les lignes de la table dont toutes les cellules f1 à f10 sont à zéro sauf une

I-A-1-a - Solution 1

Créez une vue pour "coloniser" les dix valeurs :

CREATE VIEW V_CELKO_TEN_IN_ON_TIO
AS
SELECT TIO_ID, TIO_1 AS TIO_N
FROM   T_CELKO_TEN_IN_ON_TIO
UNION  ALL
SELECT TIO_ID, TIO_2 AS TIO_N
FROM   T_CELKO_TEN_IN_ON_TIO
UNION  ALL
SELECT TIO_ID, TIO_3 AS TIO_N
FROM   T_CELKO_TEN_IN_ON_TIO
UNION  ALL
SELECT TIO_ID, TIO_4 AS TIO_N
FROM   T_CELKO_TEN_IN_ON_TIO
UNION  ALL
SELECT TIO_ID, TIO_5 AS TIO_N
FROM   T_CELKO_TEN_IN_ON_TIO
UNION  ALL
SELECT TIO_ID, TIO_6 AS TIO_N
FROM   T_CELKO_TEN_IN_ON_TIO
UNION  ALL
SELECT TIO_ID, TIO_7 AS TIO_N
FROM   T_CELKO_TEN_IN_ON_TIO
UNION  ALL
SELECT TIO_ID, TIO_8 AS TIO_N
FROM   T_CELKO_TEN_IN_ON_TIO
UNION  ALL
SELECT TIO_ID, TIO_9 AS TIO_N
FROM   T_CELKO_TEN_IN_ON_TIO
UNION  ALL
SELECT TIO_ID, TIO_10 AS TIO_N
FROM   T_CELKO_TEN_IN_ON_TIO

Dès lors la requête devient simple :

SELECT T.*
FROM   T_CELKO_TEN_IN_ON_TIO T
INNER JOIN V_CELKO_TEN_IN_ON_TIO V
ON T.TIO_ID = V.TIO_ID
WHERE  V.TIO_N = 0
GROUP  BY T.TIO_ID, T.TIO_1, T.TIO_2, T.TIO_3, T.TIO_4,
T.TIO_5, T.TIO_6, T.TIO_7, T.TIO_8, T.TIO_9, T.TIO_10
HAVING COUNT(*) = 9

I-A-1-b - Solution 2

Utilisez le Row Value Constructor pour globaliser la comparaison :

SELECT *
FROM   T_CELKO_TEN_IN_ON_TIO
WHERE  (TIO_1, TIO_2, TIO_3, TIO_4, TIO_5, TIO_6, TIO_7, TIO_8, TIO_9, TIO_10)
IN ((TIO_1, 0, 0, 0, 0, 0, 0, 0, 0, 0),
(0, TIO_2, 0, 0, 0, 0, 0, 0, 0, 0),
(0, 0, TIO_3, 0, 0, 0, 0, 0, 0, 0),
(0, 0, 0, TIO_4, 0, 0, 0, 0, 0, 0),
(0, 0, 0, 0, TIO_5, 0, 0, 0, 0, 0),
(0, 0, 0, 0, 0, TIO_6, 0, 0, 0, 0),
(0, 0, 0, 0, 0, 0, TIO_7, 0, 0, 0),
(0, 0, 0, 0, 0, 0, 0, TIO_8, 0, 0),
(0, 0, 0, 0, 0, 0, 0, 0, TIO_9, 0),
(0, 0, 0, 0, 0, 0, 0, 0, 0, TIO_10));

I-A-1-c - Solution 3

Certains SGBDR possèdent la fonction SIGN qui renvoie -1, 0 ou 1 si un nombre est respectivement négatif, zéro ou positif. Combiné à la fonction SQL ABS (valeur absolue), vous pouvez écrire :

SELECT *
FROM   T_CELKO_TEN_IN_ON_TIO
WHERE    SIGN(ABS(TIO_1)) + SIGN(ABS(TIO_2))
+ SIGN(ABS(TIO_3)) + SIGN(ABS(TIO_4))
+ SIGN(ABS(TIO_5)) + SIGN(ABS(TIO_6))
+ SIGN(ABS(TIO_7)) + SIGN(ABS(TIO_8))
+ SIGN(ABS(TIO_9)) + SIGN(ABS(TIO_10)) = 1 ;

Si la fonction SIGN n'est pas présente dans votre SGBDR, vous pouvez utiliser la structure CASE :

SELECT *
FROM   T_CELKO_TEN_IN_ON_TIO
WHERE    CASE WHEN ABS(TIO_1) > 0 THEN 1 ELSE 0 END
+ CASE WHEN ABS(TIO_2) > 0 THEN 1 ELSE 0 END
+ CASE WHEN ABS(TIO_3) > 0 THEN 1 ELSE 0 END
+ CASE WHEN ABS(TIO_4) > 0 THEN 1 ELSE 0 END
+ CASE WHEN ABS(TIO_5) > 0 THEN 1 ELSE 0 END
+ CASE WHEN ABS(TIO_6) > 0 THEN 1 ELSE 0 END
+ CASE WHEN ABS(TIO_7) > 0 THEN 1 ELSE 0 END
+ CASE WHEN ABS(TIO_8) > 0 THEN 1 ELSE 0 END
+ CASE WHEN ABS(TIO_9 )> 0 THEN 1 ELSE 0 END
+ CASE WHEN ABS(TIO_10) > 0 THEN 1 ELSE 0 END = 1 ;

I-A-1-d - Solution 4

Petit truc mathématique : si une et seulement une colonne est différente de zéro, alors il y a forcément une collection de 9 colonnes qui possèdent la valeur zéro lapalissade !

SELECT *
FROM   T_CELKO_TEN_IN_ON_TIO
WHERE  0 IN (ABS(TIO_1) + ABS(TIO_2) + ABS(TIO_3) + ABS(TIO_4) + ABS(TIO_5)
+ ABS(TIO_6) + ABS(TIO_7) + ABS(TIO_8) + ABS(TIO_9),
ABS(TIO_1) + ABS(TIO_2) + ABS(TIO_3) + ABS(TIO_4) + ABS(TIO_5)
+ ABS(TIO_6) + ABS(TIO_7) + ABS(TIO_8) + ABS(TIO_10),
ABS(TIO_1) + ABS(TIO_2) + ABS(TIO_3) + ABS(TIO_4) + ABS(TIO_5)
+ ABS(TIO_6) + ABS(TIO_7) + ABS(TIO_9) + ABS(TIO_10),
ABS(TIO_1) + ABS(TIO_2) + ABS(TIO_3) + ABS(TIO_4) + ABS(TIO_5)
+ ABS(TIO_7) + ABS(TIO_8) + ABS(TIO_9) + ABS(TIO_10),
ABS(TIO_1) + ABS(TIO_2) + ABS(TIO_3) + ABS(TIO_4) + ABS(TIO_6)
+ ABS(TIO_7) + ABS(TIO_8) + ABS(TIO_9) + ABS(TIO_10),
ABS(TIO_1) + ABS(TIO_2) + ABS(TIO_3) + ABS(TIO_5) + ABS(TIO_6)
+ ABS(TIO_7) + ABS(TIO_8) + ABS(TIO_9) + ABS(TIO_10),
ABS(TIO_1) + ABS(TIO_2) + ABS(TIO_4) + ABS(TIO_5) + ABS(TIO_6)
+ ABS(TIO_7) + ABS(TIO_8) + ABS(TIO_9) + ABS(TIO_10),
ABS(TIO_1) + ABS(TIO_3) + ABS(TIO_4) + ABS(TIO_5) + ABS(TIO_6)
+ ABS(TIO_7) + ABS(TIO_8) + ABS(TIO_9) + ABS(TIO_10),
ABS(TIO_2) + ABS(TIO_3) + ABS(TIO_4) + ABS(TIO_5) + ABS(TIO_6)
+ ABS(TIO_7) + ABS(TIO_8) + ABS(TIO_9) + ABS(TIO_10))
AND  ABS(TIO_1) + ABS(TIO_2) + ABS(TIO_3) + ABS(TIO_4) + ABS(TIO_5) +
ABS(TIO_6) + ABS(TIO_7) + ABS(TIO_8) + ABS(TIO_9) + ABS(TIO_10) <> 0 ;

I-A-1-e - Solution 5

On peut utiliser la structure CASE d'une manière plus subtile :

SELECT *
FROM   T_CELKO_TEN_IN_ON_TIO
WHERE  CASE WHEN TIO_1 = 0 THEN 1 ELSE 0 END +
CASE WHEN TIO_2 = 0 THEN 1 ELSE 0 END +
CASE WHEN TIO_3 = 0 THEN 1 ELSE 0 END +
CASE WHEN TIO_4 = 0 THEN 1 ELSE 0 END +
CASE WHEN TIO_5 = 0 THEN 1 ELSE 0 END +
CASE WHEN TIO_6 = 0 THEN 1 ELSE 0 END +
CASE WHEN TIO_7 = 0 THEN 1 ELSE 0 END +
CASE WHEN TIO_8 = 0 THEN 1 ELSE 0 END +
CASE WHEN TIO_9 = 0 THEN 1 ELSE 0 END +
CASE WHEN TIO_10 = 0 THEN 1 ELSE 0 END = 9 ;

C'est-à-dire en comptabilisant les zéro !

I-A-1-f - Solution 6

Certaines propriétés mathématiques peuvent être utilisées. Par exemple la multiplication des valeurs +1 de toutes les colonnes doit être égale à l'addition de toutes les valeurs de colonnes à condition que la somme des colonnes soit différent de zéro :

SELECT  *
FROM   T_CELKO_TEN_IN_ON_TIO
WHERE (TIO_1 + 1) * (TIO_2 + 1) * (TIO_3 + 1) * (TIO_4 + 1) * (TIO_5 + 1) *
(TIO_6 + 1) * (TIO_7 + 1) * (TIO_8 + 1) * (TIO_9 + 1) * (TIO_10 + 1)
=
TIO_1 + TIO_2 + TIO_3 + TIO_4 + TIO_5 + TIO_6 + TIO_7 + TIO_8 + TIO_9 + TIO_10 + 1
AND TIO_1 + TIO_2 + TIO_3 + TIO_4 + TIO_5 + TIO_6 + TIO_7 + TIO_8 + TIO_9 + TIO_10 <> 0 ;

Bien entendu, cela ne fonctionne que si les nombres ne sont que des entiers...

I-A-2 - Solution question 2 : les lignes de la table dont toutes les cellules f1 à f10 sont à zéro sauf une valuée à UN...

Reprenons les requêtes déjà vues et essayons de les amender pour obtenir la solution...

I-A-2-a - Solution 1

Par rapport à notre vue, on obtient le résultat en déplaçant le filtre WHERE dans un HAVING combiné à CASE pour comptabilisé les zéros associé à une somme qui doit être valuée à 1 :

SELECT T.*
FROM   T_CELKO_TEN_IN_ON_TIO T
INNER JOIN V_CELKO_TEN_IN_ON_TIO V
ON T.TIO_ID = V.TIO_ID
GROUP  BY T.TIO_ID, T.TIO_1, T.TIO_2, T.TIO_3, T.TIO_4, T.TIO_5,
T.TIO_6, T.TIO_7, T.TIO_8, T.TIO_9, T.TIO_10
HAVING SUM(CASE WHEN V.TIO_N = 0 THEN 1 ELSE 0 END) = 9
AND SUM(V.TIO_N) = 1 ;

I-A-2-b - Solution 2

Avec le Row Value Constructor c'est encore plus trivial :

SELECT *
FROM   T_CELKO_TEN_IN_ON_TIO
WHERE  (TIO_1, TIO_2, TIO_3, TIO_4, TIO_5, TIO_6, TIO_7, TIO_8, TIO_9, TIO_10)
IN ((1, 0, 0, 0, 0, 0, 0, 0, 0, 0),
(0, 1, 0, 0, 0, 0, 0, 0, 0, 0),
(0, 0, 1, 0, 0, 0, 0, 0, 0, 0),
(0, 0, 0, 1, 0, 0, 0, 0, 0, 0),
(0, 0, 0, 0, 1, 0, 0, 0, 0, 0),
(0, 0, 0, 0, 0, 1, 0, 0, 0, 0),
(0, 0, 0, 0, 0, 0, 1, 0, 0, 0),
(0, 0, 0, 0, 0, 0, 0, 1, 0, 0),
(0, 0, 0, 0, 0, 0, 0, 0, 1, 0),
(0, 0, 0, 0, 0, 0, 0, 0, 0, 1)) ;

I-A-2-c - Solution 3

Avec SIGN ou CASE il suffit d'ajouter une condition supplémentaire

SELECT *
FROM   T_CELKO_TEN_IN_ON_TIO
WHERE  SIGN(ABS(TIO_1)) + SIGN(ABS(TIO_2)) + SIGN(ABS(TIO_3)) + SIGN(ABS(TIO_4))
+ SIGN(ABS(TIO_5)) + SIGN(ABS(TIO_6)) + SIGN(ABS(TIO_7)) + SIGN(ABS(TIO_8))
+ SIGN(ABS(TIO_9)) + SIGN(ABS(TIO_10)) = 1
AND TIO_1 + TIO_2 + TIO_3 + TIO_4 + TIO_5 + TIO_6 + TIO_7 + TIO_8 + TIO_9 + TIO_10 = 1 ;
SELECT *
FROM   T_CELKO_TEN_IN_ON_TIO
WHERE    CASE WHEN ABS(TIO_1) > 0 THEN 1 ELSE 0 END
+ CASE WHEN ABS(TIO_2) > 0 THEN 1 ELSE 0 END
+ CASE WHEN ABS(TIO_3) > 0 THEN 1 ELSE 0 END
+ CASE WHEN ABS(TIO_4) > 0 THEN 1 ELSE 0 END
+ CASE WHEN ABS(TIO_5) > 0 THEN 1 ELSE 0 END
+ CASE WHEN ABS(TIO_6) > 0 THEN 1 ELSE 0 END
+ CASE WHEN ABS(TIO_7) > 0 THEN 1 ELSE 0 END
+ CASE WHEN ABS(TIO_8) > 0 THEN 1 ELSE 0 END
+ CASE WHEN ABS(TIO_9 )> 0 THEN 1 ELSE 0 END
+ CASE WHEN ABS(TIO_10) > 0 THEN 1 ELSE 0 END = 1
AND TIO_1 + TIO_2 + TIO_3 + TIO_4 + TIO_5 + TIO_6 + TIO_7 + TIO_8 + TIO_9 + TIO_10 = 1 ;

I-A-2-d - Solution 4

Il suffit de modifier la dernière clause

SELECT *
FROM   T_CELKO_TEN_IN_ON_TIO
WHERE  0 IN (ABS(TIO_1) + ABS(TIO_2) + ABS(TIO_3) + ABS(TIO_4) + ABS(TIO_5)
+ ABS(TIO_6) + ABS(TIO_7) + ABS(TIO_8) + ABS(TIO_9),
ABS(TIO_1) + ABS(TIO_2) + ABS(TIO_3) + ABS(TIO_4) + ABS(TIO_5)
+ ABS(TIO_6) + ABS(TIO_7) + ABS(TIO_8) + ABS(TIO_10),
ABS(TIO_1) + ABS(TIO_2) + ABS(TIO_3) + ABS(TIO_4) + ABS(TIO_5)
+ ABS(TIO_6) + ABS(TIO_7) + ABS(TIO_9) + ABS(TIO_10),
ABS(TIO_1) + ABS(TIO_2) + ABS(TIO_3) + ABS(TIO_4) + ABS(TIO_5)
+ ABS(TIO_7) + ABS(TIO_8) + ABS(TIO_9) + ABS(TIO_10),
ABS(TIO_1) + ABS(TIO_2) + ABS(TIO_3) + ABS(TIO_4) + ABS(TIO_6)
+ ABS(TIO_7) + ABS(TIO_8) + ABS(TIO_9) + ABS(TIO_10),
ABS(TIO_1) + ABS(TIO_2) + ABS(TIO_3) + ABS(TIO_5) + ABS(TIO_6)
+ ABS(TIO_7) + ABS(TIO_8) + ABS(TIO_9) + ABS(TIO_10),
ABS(TIO_1) + ABS(TIO_2) + ABS(TIO_4) + ABS(TIO_5) + ABS(TIO_6)
+ ABS(TIO_7) + ABS(TIO_8) + ABS(TIO_9) + ABS(TIO_10),
ABS(TIO_1) + ABS(TIO_3) + ABS(TIO_4) + ABS(TIO_5) + ABS(TIO_6)
+ ABS(TIO_7) + ABS(TIO_8) + ABS(TIO_9) + ABS(TIO_10),
ABS(TIO_2) + ABS(TIO_3) + ABS(TIO_4) + ABS(TIO_5) + ABS(TIO_6)
+ ABS(TIO_7) + ABS(TIO_8) + ABS(TIO_9) + ABS(TIO_10))
AND  TIO_1 + TIO_2 + TIO_3 + TIO_4 + TIO_5 +
TIO_6 + TIO_7 + TIO_8 + TIO_9 + TIO_10 = 1 ;

I-A-2-e - Solution 5

Là encore, le simple ajout d'un prédicat complémentaire suffit :

SELECT *
FROM   T_CELKO_TEN_IN_ON_TIO
WHERE  CASE WHEN TIO_1 = 0 THEN 1 ELSE 0 END +
CASE WHEN TIO_2 = 0 THEN 1 ELSE 0 END +
CASE WHEN TIO_3 = 0 THEN 1 ELSE 0 END +
CASE WHEN TIO_4 = 0 THEN 1 ELSE 0 END +
CASE WHEN TIO_5 = 0 THEN 1 ELSE 0 END +
CASE WHEN TIO_6 = 0 THEN 1 ELSE 0 END +
CASE WHEN TIO_7 = 0 THEN 1 ELSE 0 END +
CASE WHEN TIO_8 = 0 THEN 1 ELSE 0 END +
CASE WHEN TIO_9 = 0 THEN 1 ELSE 0 END +
CASE WHEN TIO_10 = 0 THEN 1 ELSE 0 END  = 9
AND  TIO_1 + TIO_2 + TIO_3 + TIO_4 + TIO_5 +
TIO_6 + TIO_7 + TIO_8 + TIO_9 + TIO_10 = 1 ;

I-A-2-f - Solution 6

Même topo pour la solution 6, une modification mineure suffit :

SELECT  *
FROM   T_CELKO_TEN_IN_ON_TIO
WHERE (TIO_1 + 1) * (TIO_2 + 1) * (TIO_3 + 1) * (TIO_4 + 1) * (TIO_5 + 1) *
(TIO_6 + 1) * (TIO_7 + 1) * (TIO_8 + 1) * (TIO_9 + 1) * (TIO_10 + 1)
=
TIO_1 + TIO_2 + TIO_3 + TIO_4 + TIO_5 + TIO_6 + TIO_7 + TIO_8 + TIO_9 + TIO_10 + 1
AND TIO_1 + TIO_2 + TIO_3 + TIO_4 + TIO_5 + TIO_6 + TIO_7 + TIO_8 + TIO_9 + TIO_10 = 1 ;

I-B - Solution au problème n° 2 : le publipostage

Il suffit de créer une table dotée d'une unique colonne contenant la séquence continue des nombres de 1 à n, n étant une valeur égale ou supérieure à la colonne PRS_NOMBRE :

CREATE TABLE T_ENUMERE_NMR
(NMR_NOMBRE INTEGER);

INSERT INTO T_ENUMERE_NMR VALUES (1);
INSERT INTO T_ENUMERE_NMR VALUES (2);
INSERT INTO T_ENUMERE_NMR VALUES (3);
INSERT INTO T_ENUMERE_NMR VALUES (4);
INSERT INTO T_ENUMERE_NMR VALUES (5);
INSERT INTO T_ENUMERE_NMR VALUES (6);
INSERT INTO T_ENUMERE_NMR VALUES (7);
INSERT INTO T_ENUMERE_NMR VALUES (8);
...
INSERT INTO T_ENUMERE_NMR VALUES (99);
...

Dès lors, la requête devient basique avec un non équi jointure :

SELECT PRS_NOM, PRS_VILLE
FROM   T_PERSONNE_PRS PRS
INNER JOIN T_ENUMERE_NMR NMR
ON PRS.PRS_NOMBRE >= NMR.NMR_NOMBRE

Simple, non ???

I-C - Solution au problème n° 3 : la date an 2000

En utilisant les propriété de la division entière on peut ajouter 100 ans lorsque si la division par 60 donne une valeur supérieure à zéro. Ainsi la requête :

SELECT *, CAST(SUBSTRING(AMT_FIN FROM 1 FOR 2)AS INTEGER) + 2000 - 100 * (CAST(SUBSTRING(AMT_FIN FROM 1 FOR 2)AS INTEGER)/60) AS AN,
SUBSTRING(AMT_FIN ,3 , 2) AS MOIS, SUBSTRING(AMT_FIN ,5 , 2) AS JOUR
FROM   T_AMORTISSEMENT_AMT ;

Donne :

AMT_FIN AMT_FIN_Y2K AN          MOIS JOUR
------- ----------- ----------- ---- ----
990601  NULL        1999        06   01
970201  NULL        1997        02   01
021201  NULL        2002        12   01
941101  NULL        1994        11   01
920715  NULL        1992        07   15

Il est alors facile de construite une requête UPDATE dessus :

UPDATE T_AMORTISSEMENT_AMT
SET    AMT_FIN_Y2K =
CAST(CAST(SUBSTRING(AMT_FIN FROM 1 FOR 2)AS INTEGER) + 2000
- 100 * (CAST(SUBSTRING(AMT_FIN FROM 1 FOR 2)AS INTEGER)/60) AS CHAR(4)) + '-'
+ SUBSTRING(AMT_FIN FROM 3 FOR 2) + '-' + SUBSTRING(AMT_FIN FROM 5 FOR 2) ;

La même requête pour MS SQL Server :

UPDATE T_AMORTISSEMENT_AMT
SET    AMT_FIN_Y2K =
CAST(CAST(SUBSTRING(AMT_FIN, 1, 2)AS INTEGER) + 2000
- 100 * (CAST(SUBSTRING(AMT_FIN, 1, 2)AS INTEGER)/60) AS CHAR(4)) + '-'
+ SUBSTRING(AMT_FIN, 3, 2) + '-' + SUBSTRING(AMT_FIN, 5, 2) ;

Un internaute, Akshell, nous a donné une solution via l'opération ensembliste UNION :

UPDATE AMORTISSEMENT
SET FIN_AMORT_Y2K = (SELECT '20' ||
SUBSTRING(FIN_AMORT FROM 1 FOR 2) || '-' ||
SUBSTRING(FIN_AMORT FROM 3 FOR 2) || '-' ||
SUBSTRING(FIN_AMORT FROM 5 FOR 2)
FROM   AMORTISSEMENT A1
WHERE  SUBSTRING(FIN_AMORT FROM 1 FOR 2) < '60'
AND  A1.FIN_AMORT = AMORTISSEMENT.FIN_AMORT
UNION  ALL
SELECT '19' ||
SUBSTRING(FIN_AMORT FROM 1 FOR 2) || '-' ||
SUBSTRING(FIN_AMORT FROM 3 FOR 2) || '-' ||
SUBSTRING(FIN_AMORT FROM 5 FOR 2)
FROM   AMORTISSEMENT A2
WHERE  SUBSTRING(FIN_AMORT FROM 1 FOR 2) >= '60'
AND  A2.FIN_AMORT = AMORTISSEMENT.FIN_AMORT) ;

Mais une solution plus simple consiste à utiliser le CASE :

UPDATE AMORTISSEMENT
SET FIN_AMORT_Y2K = (SELECT CASE
WHEN SUBSTRING(FIN_AMORT FROM 1 FOR 2) < '60'
THEN '20'
ELSE '19'
END ||
SUBSTRING(FIN_AMORT FROM 1 FOR 2) ||  '-' ||
SUBSTRING(FIN_AMORT FROM 3 FOR 2) || '-' ||
SUBSTRING(FIN_AMORT FROM 5 FOR 2) ||

FROM AMORTISSEMENT A
WHERE A.FIN_AMORT = AMORTISSEMENT.FIN_AMORT) ;

I-D - Solution au problème n° 4 : les chambres libres

I-D-1 - Les chambres qui sont libres pendant toute la période allant du 11 au 14 janvier 2000 ?

La recherche du vide ou de la non existence fait toujours partie des choses difficiles à exprimer en SQL...
Mais l'opérateur IN et plus particulièrement sa négation le NOT IN permet souvent de se tirer de ce genre d'embarras...

SELECT CHB_NUM
FROM   T_CHAMBRE_CHB
WHERE  CHB_NUM NOT IN (SELECT CHB_NUM
FROM   T_PLANNING_PLN
WHERE  PLN_JOUR BETWEEN '2000-01-11' AND '2000-01-14'
AND  PLN_LIBRE = 'False') ;

I-D-2 - L'occupation des chambres pour la journée du 13 janvier 2000 ?

Là la problématique est différente. Il nous faut toutes les chambres et si possible l'indication d'occupation. Et s'il n'y a pas d'indication d'occupation, alors on renseigne avec la valeur True la colonne PLN_LIBRE. Bien entendu il faut utiliser une jointure externe :

SELECT CHB.CHB_NUM, COALESCE(PLN_LIBRE, 'True') AS PLN_LIBRE
FROM   T_CHAMBRE_CHB CHB
LEFT OUTER JOIN T_PLANNING_PLN PLN
ON CHB.CHB_NUM = PLN.CHB_NUM
WHERE  PLN_JOUR = '2000-01-13' OR PLN_JOUR IS NULL ;

Ou encore :

SELECT CHB.CHB_NUM, COALESCE(PLN_LIBRE, 'True') AS PLN_LIBRE
FROM   T_CHAMBRE_CHB CHB
LEFT OUTER JOIN T_PLANNING_PLN PLN
ON CHB.CHB_NUM = PLN.CHB_NUM
AND  PLN_JOUR = '2000-01-13' ;

I-D-3 - Le planning des occupations pour toutes les chambres et toutes les dates du 11 au 14 janvier 2000 ?

Il n'est pas possible de faire cette requête directement car une partie de l'information est manquante. Par exemple le 11 janvier ne figure nulle part. Il faut donc rajouter cette information à notre base de données sous la forme d'une table de date que nous allons appeler T_CALENCRIER_CLD

CREATE TABLE T_CALENDRIER_CLD
(CLD_JOUR DATETIME);

INSERT INTO  T_CALENDRIER_CLD VALUES ('2000-01-01');
...
INSERT INTO  T_CALENDRIER_CLD VALUES ('2000-01-10');
INSERT INTO  T_CALENDRIER_CLD VALUES ('2000-01-11');
INSERT INTO  T_CALENDRIER_CLD VALUES ('2000-01-12');
INSERT INTO  T_CALENDRIER_CLD VALUES ('2000-01-13');
INSERT INTO  T_CALENDRIER_CLD VALUES ('2000-01-14');
INSERT INTO  T_CALENDRIER_CLD VALUES ('2000-01-15');
...
INSERT INTO  T_CALENDRIER_CLD VALUES ('2000-12-31');

Pour obtenir toutes les dates considérées et toutes les chambre un produit cartésien semble parfait...

SELECT CLD.CLD_JOUR, CHB.CHB_NUM, COALESCE(PLN.PLN_LIBRE, 'True') AS PLN_LIBRE
FROM   T_CALENDRIER_CLD CLD
CROSS JOIN T_CHAMBRE_CHB CHB
LEFT OUTER JOIN T_PLANNING_PLN PLN
ON CLD.CLD_JOUR = PLN.PLN_JOUR
AND CHB.CHB_NUM = PLN.CHB_NUM
WHERE  CLD.CLD_JOUR BETWEEN '2000-01-11' AND '2000-01-14' ;

et on fait la jointure externe de ce produit cartésien avec la table T_PLANNING_PLN

I-E - Problème n° 5 : date d'anniversaire

Connaissez-vous la fonction EXTRACT ? Non, sinon elle vous aurait grandement facilité la vie. Elle permet d'extraire une partie de date comme le mois ou le jour... Pour MS SQL Server cette fonction s'appelle DATEPART.

I-E-1 - Les anniversaires entre le 21 février et le 20 mars

SELECT *
FROM   T_CLIENT_CLI
WHERE  ((EXTRACT(MONTH FROM CLI_DATE_NAISSANCE) - 1) * 31 + EXTRACT(DAY FROM CLI_DATE_NAISSANCE))
BETWEEN  52 AND 82 ;

Les chiffes 83 et 113 sont calculés de la façon suivante : partant de février = 1 (2eme mois -1) et mars = 2 (3eme mois -1)
1 * 31 j + 21 j = 52 jours
et
2 * 31 j + 20 j = 82 jours

La même requête pour MS SQL Server :

SELECT *
FROM   T_CLIENT_CLI
WHERE  ((DATEPART(MONTH, CLI_DATE_NAISSANCE) - 1) * 31 + DATEPART(DAY, CLI_DATE_NAISSANCE))
BETWEEN  52 AND 82 ;

I-E-2 - Les anniversaires entre le 21 décembre et le 20 janvier

SELECT *
FROM   T_CLIENT_CLI
WHERE  MOD(((EXTRACT (MONTH FROM CLI_DATE_NAISSANCE) - 1) * 31
+ EXTRACT(DAY FROM CLI_DATE_NAISSANCE)),  362 )
BETWEEN 0 AND 20 ;

MOD étant la fonction modulo (reste de la division entière).
La difficulté est le passage à l'année suivante. Mais une simple translation via la fonction modulo suffit :
21 décembre => 11 * 31 + 21 = 362,
20 janvier => 31 * 0 + 20 = 20

La même requête pour MS SQL Server :

SELECT *
FROM   T_CLIENT_CLI
WHERE  ((DATEPART(MONTH, CLI_DATE_NAISSANCE) - 1) * 31
+ DATEPART(DAY, CLI_DATE_NAISSANCE)) % 362
BETWEEN 0 AND 20 ;

I-E-3 - La requête paramétrée générique

Il suffit de combiner les deux requêtes à l'aide de la structure CASE :

SELECT *
FROM T_CLIENT_CLI
WHERE  MOD(((EXTRACT(MONTH FROM CLI_DATE_NAISSANCE) - 1) * 31
+ EXTRACT(DAY FROM CLI_DATE_NAISSANCE)),
CASE WHEN (EXTRACT(MONTH FROM :dd) - 1) * 31 + EXTRACT(DAY FROM :dd)
> (EXTRACT(MONTH FROM :df) - 1) * 31 + EXTRACT(DAY FROM :df)
THEN (EXTRACT(MONTH FROM :dd) - 1) * 31 + EXTRACT(DAY FROM :dd)
ELSE ((EXTRACT(MONTH FROM CLI_DATE_NAISSANCE) - 1) * 31
+ EXTRACT(DAY FROM CLI_DATE_NAISSANCE)) + 1
END )
BETWEEN CASE
WHEN (EXTRACT(MONTH FROM :dd) - 1) * 31 + EXTRACT(DAY FROM :dd)
< (EXTRACT(MONTH FROM :df) - 1) * 31 + EXTRACT(DAY FROM :df)
THEN (EXTRACT(MONTH FROM :dd) - 1) * 31 + EXTRACT(DAY FROM :dd)
ELSE 0
END AND (EXTRACT(MONTH FROM :df) - 1) * 31 + EXTRACT(DAY FROM :df) ;

La requête paramétrée pour MS SQL Server (dans une procédure stockée) :

CREATE PROCEDURE SP_ANNIVERSAIRES @dd DATETIME, @df DATETIME
AS
SELECT *
FROM T_CLIENT_CLI
WHERE  ((DATEPART(MONTH, CLI_DATE_NAISSANCE) - 1) * 31
+ DATEPART(DAY, CLI_DATE_NAISSANCE))
% CASE WHEN (DATEPART(MONTH, @dd) - 1) * 31 + DATEPART(DAY, @dd)
> (DATEPART(MONTH, @df) - 1) * 31 + DATEPART(DAY, @df)
THEN (DATEPART(MONTH, @dd) - 1) * 31 + DATEPART(DAY, @dd)
ELSE ((DATEPART(MONTH, CLI_DATE_NAISSANCE) - 1) * 31
+ DATEPART(DAY, CLI_DATE_NAISSANCE)) + 1
END
BETWEEN CASE
WHEN (DATEPART(MONTH, @dd) - 1) * 31 + DATEPART(DAY, @dd)
< (DATEPART(MONTH, @df) - 1) * 31 + DATEPART(DAY, @df)
THEN (DATEPART(MONTH, @dd) - 1) * 31 + DATEPART(DAY, @dd)
ELSE 0
END AND (DATEPART(MONTH, @df) - 1) * 31 + DATEPART(DAY, @df) ;

De plus, si vous disposez de la fonction CURRENT_DATE, avec un peu d'astuce, vous pouvez trouver une requête vous permettant de trouver tous les clients dont la date anniversaire sera dans les 15 jours qui viennent...

I-F - Solution au problème n° 6 - énumération

Finalement, n'importe quel nombre entre 10 et 9999 n'est que l'addition d'une unité, d'une dizaine, d'une centaine et d'un millier. Il suffit donc de placer dans la requête 4 fois la table pour représenter chacun des membres de cette addition. Pour cela, nous allons utiliser la jointure croisée qui réalise le produit cartésien des tables :

INSERT INTO T_ENTIER_ENT
SELECT TEU.ENT_N                      -- les unités
+ 10   * TED.ENT_N             -- les dizaines
+ 100  * TEC.ENT_N             -- les centaines
+ 1000 * TEM.ENT_N             -- les milliers
FROM   T_ENTIER_ENT TEU               -- table des entiers pour les unités
CROSS JOIN T_ENTIER_ENT TED    -- table des entiers pour les dizaines
CROSS JOIN T_ENTIER_ENT TEC    -- table des entiers pour les centaines
CROSS JOIN T_ENTIER_ENT TEM    -- table des entiers pour les milliers
WHERE  TEU.ENT_N
+ 10   * TED.ENT_N
+ 100  * TEC.ENT_N
+ 1000 * TEM.ENT_N  > 9        -- empêche d'insérer les doublons des unités

Si votre SGBDR ne dispose pas de la jointure croisée, il suffit d'écrire votre requête comme ceci :

INSERT INTO T_ENTIER_ENT
SELECT TEU.ENT_N                      -- les unités
+ 10   * TED.ENT_N             -- les dizaines
+ 100  * TEC.ENT_N             -- les centaines
+ 1000 * TEM.ENT_N             -- les milliers
FROM   T_ENTIER_ENT TEU,              -- table des entiers pour les unités
T_ENTIER_ENT TED,       -- table des entiers pour les dizaines
T_ENTIER_ENT TEC,       -- table des entiers pour les centaines
T_ENTIER_ENT TEM        -- table des entiers pour les milliers
WHERE  TEU.ENT_N
+ 10   * TED.ENT_N
+ 100  * TEC.ENT_N
+ 1000 * TEM.ENT_N  > 9        -- empêche d'insérer les doublons des unités ;

Une autre solution m'a été donnée par un internaute, Akshell. Elle reste similaire dans son principe :

INSERT INTO T_ENTIER_ENT
SELECT CAST(CAST(TEM.ENT_N AS CHAR(1)) ||
CAST(TEC.ENT_N AS CHAR(1)) ||
CAST(TED.ENT_N AS CHAR(1)) ||
CAST(TEU.ENT_N AS CHAR(1))  AS INTEGER)
FROM   T_ENTIER_ENT TEU               -- table des entiers pour les unités
CROSS JOIN T_ENTIER_ENT TED    -- table des entiers pour les dizaines
CROSS JOIN T_ENTIER_ENT TEC    -- table des entiers pour les centaines
CROSS JOIN T_ENTIER_ENT TEM    -- table des entiers pour les milliers
WHERE  (CAST(TEM.ENT_N AS CHAR(1)) ||
CAST(TEC.ENT_N AS CHAR(1)) ||
CAST(TED.ENT_N AS CHAR(1)) ||
CAST(TEU.ENT_N AS CHAR(1))) NOT BETWEEN '0000' AND '0009' ;

Elle propose de réaliser cette énumération en combinant les chiffres sous forme littérale. Mais elle est moins performante du fait de l'utilisation nonuple de la fonction CAST.

Même requête que ci-dessus pour MS SQL Server :

INSERT INTO T_ENTIER_ENT
SELECT CAST(CAST(TEM.ENT_N AS CHAR(1)) + CAST(TEC.ENT_N AS CHAR(1)) + CAST(TED.ENT_N AS CHAR(1)) + CAST(TEU.ENT_N AS CHAR(1))  AS INTEGER)
FROM   T_ENTIER_ENT TEU               -- table des entiers pour les unités
CROSS JOIN T_ENTIER_ENT TED    -- table des entiers pour les dizaines
CROSS JOIN T_ENTIER_ENT TEC    -- table des entiers pour les centaines
CROSS JOIN T_ENTIER_ENT TEM    -- table des entiers pour les milliers
WHERE  (CAST(TEM.ENT_N AS CHAR(1)) +
CAST(TEC.ENT_N AS CHAR(1)) +
CAST(TED.ENT_N AS CHAR(1)) +
CAST(TEU.ENT_N AS CHAR(1))) NOT BETWEEN '0000' AND '0009' ;

I-G - Solution au problème n° 7 - Le comptage

Le problème de ce genre de requête est la présence de doublons absolus. En l'occurrence nous avons deux fois la présence du nom DUPONT dans nos prospects. Si nous faisons une requête classique de comptage pour numéroter les lignes et que DUPONT ne figure qu'une seule fois dans la table, alors tout va bien :

SELECT PP1.PSP_NOM, COUNT(PP2.PSP_NOM) AS N
FROM   T_PROSPECT_PSP PP1
INNER JOIN T_PROSPECT_PSP PP2
ON PP1.PSP_NOM > PP2.PSP_NOM
GROUP BY PP1.PSP_NOM ;

PSP_NOM          N
---------------- -----------
BAILLE           1
CLAUDE           2
DUPONT           3
GAUTIER          4
MARTIN           5

Mais en présence du doublon DUPONT, cette même requête donne un résultat qui n'est pas exploitable :

PSP_NOM          N
---------------- -----------
BAILLE           1
CLAUDE           2
DUPONT           6
GAUTIER          5
MARTIN           6

Dès lors, il faut ajouter une information capable de discriminer les deux DUPONT.
Une idée consiste à prendre une table de nombre uniques et de faire une jointure croisée (produit cartésien), puis de prendre un nombre différent pour chaque valeur de même occurrence.
En partant d'une table telle que celle-ci :

CREATE TABLE T_ENTIER_ENT
(ENT_N INTEGER);

INSERT INTO T_ENTIER_ENT VALUES (1);
INSERT INTO T_ENTIER_ENT VALUES (2);
INSERT INTO T_ENTIER_ENT VALUES (3);
INSERT INTO T_ENTIER_ENT VALUES (4);
INSERT INTO T_ENTIER_ENT VALUES (5);
INSERT INTO T_ENTIER_ENT VALUES (6);
INSERT INTO T_ENTIER_ENT VALUES (7);
INSERT INTO T_ENTIER_ENT VALUES (8);
INSERT INTO T_ENTIER_ENT VALUES (9);
...

Dès lors, la requête suivante réalise le produit cartésien :

SELECT PSP_NOM, ENT_N
FROM   T_PROSPECT_PSP
CROSS JOIN T_ENTIER_ENT ;

PSP_NOM          ENT_N
---------------- -----------
ARMAND           1
ARMAND           2
ARMAND           3
ARMAND           4
ARMAND           5
ARMAND           6
ARMAND           7
ARMAND           8
ARMAND           9
BAILLE           1
BAILLE           2
BAILLE           3
...

Mais il y a bien trop de lignes pour que ce résultat soit exploitable. Il faut donc restreindre le produit cartésien sur le nombre d'occurrences des valeurs de mons. Ceci est possible à l'aide de la requête suivante :

SELECT PSP_NOM, N, ENT_N
FROM   (SELECT PSP_NOM, COUNT(*) AS N
FROM   T_PROSPECT_PSP
GROUP  BY PSP_NOM) T
CROSS JOIN T_ENTIER_ENT
WHERE  ENT_N <= T.N ;

PSP_NOM          N           ENT_N
---------------- ----------- -----------
ARMAND           1           1
BAILLE           1           1
CLAUDE           1           1
DUPONT           2           1
DUPONT           2           2
GAUTIER          1           1
MARTIN           1           1

Nous avons maintenant notre discriminant d'occurrence de nom constitué par la colonne ENT_N
Par facilité, constituons une vue :

CREATE VIEW V_PROSPECT_PSP
AS
SELECT PSP_NOM, ENT_N AS PSP_DISCRIMINANT,
CAST(PSP_NOM AS CHAR(16)) + CAST(ENT_N AS CHAR(16)) AS PSP_NOM_DIFFERENT
FROM   (SELECT PSP_NOM, COUNT(*) AS N
FROM   T_PROSPECT_PSP
GROUP  BY PSP_NOM) T
CROSS JOIN T_ENTIER_ENT
WHERE ENT_N <= T.N ;

Une extraction brute de cette vue nous donne :

PSP_NOM          PSP_DISCRIMINANT PSP_NOM_DIFFERENT
---------------- ---------------- -----------------
ARMAND           1                ARMAND          1
BAILLE           1                BAILLE          1
CLAUDE           1                CLAUDE          1
DUPONT           1                DUPONT          1
DUPONT           2                DUPONT          2
GAUTIER          1                GAUTIER         1
MARTIN           1                MARTIN          1

Ce qui est suffisant maintenant pour traiter notre problème initial :

SELECT PP1.PSP_NOM, COUNT(*) N
FROM   V_PROSPECT_PSP PP1
INNER JOIN V_PROSPECT_PSP PP2
ON PP1.PSP_NOM_DIFFERENT > PP2.PSP_NOM_DIFFERENT
GROUP BY PP1.PSP_NOM, PP1.PSP_NOM_DIFFERENT ;

Et donne le résultat attendu :

PSP_NOM          N
---------------- -----------
BAILLE           1
CLAUDE           2
DUPONT           3
DUPONT           4
GAUTIER          5
MARTIN           6

Depuis la norme SQL:2003, de nouvelles fonction ont été ajoutées pour traiter ce genre de cas : RANK(), DENSE_RANK() et ROW_NUMBER(). Voici un exemple de leur fonctionnement sur notre table exercice :

SELECT PSP_NOM,
RANK()       OVER(ORDER BY PSP_NOM) AS RANG,
DENSE_RANK() OVER(ORDER BY PSP_NOM) AS RANG_DENSE,
ROW_NUMBER() OVER(ORDER BY PSP_NOM) AS NUMERO
FROM   T_PROSPECT_PSP ;

PSP_NOM          RANG        RANG_SENSE  NUMERO
---------------- ----------- ----------- -----------
BAILLE           1           1           1
CLAUDE           2           2           2
DUPONT           3           3           3
DUPONT           3           3           4
GAUTIER          5           4           5
MARTIN           6           5           6

C'est plus pratique il faut l'avouer, mais ces fonctions dites de fenêtrage ne peuvent pas apparaître dans les sous requêtes en ce sens qu'elle n'opèrent qu'en présence des résultats devant être affichés.

I-H - Solution au problème n° 8 - linéarisation

Une première possibilité, la plus traditionnelle est d'utiliser les sous requêtes dans la clause select :

SELECT COUNT(*) AS 'TBL1',
(SELECT COUNT(*) FROM TBL2) AS 'TBL2',
(SELECT COUNT(*) FROM TBL3) AS 'TBL3'
FROM   TBL1 ;

Mais certains SGBDR acceptent de ne pas faire figurer de clause FROM, par exemple MS SQL Server :

SELECT (SELECT COUNT(*) FROM TBL1) AS 'TBL1',
(SELECT COUNT(*) FROM TBL2) AS 'TBL2',
(SELECT COUNT(*) FROM TBL3) AS 'TBL3';

D'autres nécessitent l'usage d'une "fausse" table. C'est le cas d'Oracle avec la table DUAL :

SELECT (SELECT COUNT(*) FROM TBL1) AS 'TBL1',
(SELECT COUNT(*) FROM TBL2) AS 'TBL2',
(SELECT COUNT(*) FROM TBL3) AS 'TBL3'
FROM DUAL

Mais que faire en l'absence de sous requêtes ?

On peut y arriver en faisant une table regroupant toutes les informations puis en allant à la pêche pour compter les clefs non redondantes.

L'idée est de faire une jointure qui permette de retrouver toutes les clefs de toutes les tables jointes.

La seule jointure capable de cela c'est de faire un produit cartésien (une multiplication des lignes des tables) à l'aide du CROSS JOIN :

Avec la syntaxe des jointures normalisées, cela donne :

SELECT COUNT (DISTINCT TBL1.TBL_ID) as TBL1,
COUNT (DISTINCT TBL2.TBL_ID) as TBL2,
COUNT (DISTINCT TBL3.TBL_ID) as TBL3
FROM TBL1 CROSS JOIN TBL2 CROSS JOIN TBL3

Avec la syntaxe "ancienne" :

SELECT COUNT (DISTINCT TBL1.TBL_ID) as TBL1,
COUNT (DISTINCT TBL2.TBL_ID) as TBL2,
COUNT (DISTINCT TBL3.TBL_ID) as TBL3
FROM TBL1, TBL2, TBL3

Mais attention : cette requête est explosive !

En effet dans notre exemple, nous avons respectivement 5, 4 et 5 lignes. Le produit cartésien de ces 3 tables va donc donner : 100 lignes.

Imaginez ce que cela pourra donner si les tables comptent 80, 100 et 140 lignes seulement... Plus d'un million de lignes à traiter !

I-I - Solution au problème n° 9 - les Trous

Une solution simple et rapide est donnée par :

SELECT NMR + 1 as NUM_MANQUANT
FROM   T_NUMERO_NMR
WHERE  NMR + 1 NOT IN (SELECT NMR
FROM   T_NUMERO_NMR)
AND  NMR + 1 < (SELECT MAX(NMR)
FROM T_NUMERO_NMR) ;

Mais cette solution suppose que le trou ne fasse jamais qu'un seul n°. Imaginez ce qui se passerait si la table des n° était ainsi constituée :

DELETE FROM T_NUMERO_NMR;

INSERT INTO T_NUMERO_NMR VALUES (2);
INSERT INTO T_NUMERO_NMR VALUES (3);
INSERT INTO T_NUMERO_NMR VALUES (5);
INSERT INTO T_NUMERO_NMR VALUES (8);
INSERT INTO T_NUMERO_NMR VALUES (9);
INSERT INTO T_NUMERO_NMR VALUES (10);
INSERT INTO T_NUMERO_NMR VALUES (14);
INSERT INTO T_NUMERO_NMR VALUES (15);

Alors la requête ci-dessus appliquée à ce nouveau jeu de données donnerait :

NUM_MANQUANT
------------
4
6
11

Ce qui ne correspond qu'aux 1er n° de chaque trou...

Une solution passe par la création d'une liste contenant tous les nombres de 1 à n, n étant au moins la plus forte valeur dans la table considérée puis faire une requête imbriquée avec un NOT in en sélectant le min !

Pour créer une table d'entiers de 1 à n, on peut partir d'une table contenant les 10 premiers entiers de 0 à 9 comme nous l'avons fait lors du problème n°6 :

CREATE TABLE T_ENTIER_ENT
(ENT_N INTEGER);

INSERT INTO T_ENTIER_ENT VALUES (0);
INSERT INTO T_ENTIER_ENT VALUES (1);
INSERT INTO T_ENTIER_ENT VALUES (2);
INSERT INTO T_ENTIER_ENT VALUES (3);
INSERT INTO T_ENTIER_ENT VALUES (4);
INSERT INTO T_ENTIER_ENT VALUES (5);
INSERT INTO T_ENTIER_ENT VALUES (6);
INSERT INTO T_ENTIER_ENT VALUES (7);
INSERT INTO T_ENTIER_ENT VALUES (8);
INSERT INTO T_ENTIER_ENT VALUES (9);

INSERT INTO T_ENTIER_ENT
SELECT TEU.ENT_N
+ 10   * TED.ENT_N
+ 100  * TEC.ENT_N
+ 1000 * TEM.ENT_N
FROM   T_ENTIER_ENT TEU
CROSS JOIN T_ENTIER_ENT TED
CROSS JOIN T_ENTIER_ENT TEC
CROSS JOIN T_ENTIER_ENT TEM
WHERE  TEU.ENT_N
+ 10   * TED.ENT_N
+ 100  * TEC.ENT_N
+ 1000 * TEM.ENT_N  > 9 ;

La requête pour récupérer tous les trous est la suivante :

SELECT ENT_N
FROM   T_ENTIER_ENT
WHERE  ENT_N NOT IN (SELECT NMR
FROM T_NUMERO_NMR) ;

ENT_N
-----------
0
4
7
20
30
40
50
60
70
80
90
100
110
...

Le problème est que cette requête va bien au delà des bornes des valeurs de la table concernée. Nous devons donc restreindre le résultat aux valeurs max et min de la table d'origine...

SELECT ENT_N
FROM   T_ENTIER_ENT
WHERE  ENT_N NOT IN (SELECT NMR
FROM   T_NUMERO_NMR)
AND  ENT_N BETWEEN (SELECT MIN(NMR)
FROM   T_NUMERO_NMR)
AND (SELECT MAX(NMR)
FROM   T_NUMERO_NMR)

I-J - Solution au problème n° 10 - symétrie négative

Rien de sorcier. Il suffit de négativer l'identifiant numérique, comme le montant :

INSERT INTO T_LIGNE_FACTURE_LIF
SELECT LIF_NUM * -1,
FAC_NUM, LIF_ARTICLE, LIF_MONTANT * -1
FROM   T_LIGNE_FACTURE_LIF L
WHERE  FAC_NUM = 79
AND  LIF_NUM > 0 ;

Dès lors, la requête d'extraction pour présenter les informations dans le bon ordre est la suivante :

SELECT FAC_NUM, LIF_ARTICLE, LIF_MONTANT
FROM   T_LIGNE_FACTURE_LIF
ORDER  BY FAC_NUM, ABS(LIF_NUM), LIF_NUM DESC ;

Mais certains SGBDR ne supportent pas d'expression, ni de fonctions, dans la clause de tri, ni même le tri externe. Dans ce cas il faut calculer les nouvelles colonnes :

SELECT FAC_NUM, LIF_ARTICLE, LIF_MONTANT,  ABS(LIF_NUM) AS LIF_NUM1, LIF_NUM AS LIF_NUM2
FROM   T_LIGNE_FACTURE_LIF
ORDER  BY FAC_NUM, LIF_NUM1, LIF_NUM2 DESC ;

II - Solutions - 2° partie

II-A - Solution au problème n° 11 - premiers

Il suffit de prendre le problème à l'envers et de récupérer facilement tous les nombres non premiers, c'est-à-dire ceux pour lesquels le reste de le division entière par au moins un des nombres qui lui est inférieur vaut 0 :

SELECT EN1.ENT_N
FROM   T_ENTIER_ENT EN1
CROSS JOIN T_ENTIER_ENT EN2
WHERE  MOD(EN1.ENT_N, EN2.ENT_N) = 0
AND  EN2.ENT_N BETWEEN 2 AND EN1.ENT_N -1 ;

Pour ce faire, on réalise le produit cartésien du nombre avec tous les nombres compris entre 2 et ce nombre moins un et l'on teste si le reste de la division entière vaut zéro. Si ce reste vaut zéro, alors preuve est faite que ce nombre n'est pas premier.

Dès que nous avons tous les nombres non premiers, il ne s'agit plus que de trouver le complément :

SELECT *
FROM   T_ENTIER_ENT
WHERE  ENT_N NOT IN (SELECT EN1.ENT_N
FROM   T_ENTIER_ENT EN1
CROSS JOIN T_ENTIER_ENT EN2
WHERE  MOD(EN1.ENT_N, EN2.ENT_N) = 0
AND  EN2.ENT_N BETWEEN 2 AND EN1.ENT_N -1)
ORDER BY 1 ;

Ceci peut aussi être fait à l'aide de l'opérateur ensembliste EXCEPT

SELECT *
FROM   T_ENTIER_ENT
EXCEPT
SELECT EN1.ENT_N
FROM   T_ENTIER_ENT EN1
CROSS JOIN T_ENTIER_ENT EN2
WHERE  MOD(EN1.ENT_N, EN2.ENT_N) = 0
AND  EN2.ENT_N BETWEEN 2 AND EN1.ENT_N -1
ORDER BY 1 ;

La requête pour MS SQL Server :

SELECT *
FROM   T_ENTIER_ENT
WHERE  ENT_N NOT IN (SELECT EN1.ENT_N
FROM   T_ENTIER_ENT EN1
CROSS JOIN T_ENTIER_ENT EN2
WHERE  EN1.ENT_N % EN2.ENT_N = 0
AND  EN2.ENT_N BETWEEN 2 AND EN1.ENT_N -1)
ORDER BY 1 ;

II-B - Solution au problème n° 12 - traduction

Facile avec la fonction COALESCE et des sous requêtes. Voici un premier exemple de solution.
Solution 1 :

SELECT TDR_LIBELLE, TDR_ID, TDR_LANGUE
FROM   T_TRADUCTION_TDR TDR
WHERE  TDR_LANGUE = COALESCE((SELECT DISTINCT TDR_LANGUE
FROM   T_TRADUCTION_TDR
WHERE  TDR_LANGUE = 'Français'
AND  TDR_ID = TDR.TDR_ID), (SELECT DISTINCT TDR_LANGUE
FROM   T_TRADUCTION_TDR
WHERE  TDR_LANGUE = 'Anglais'
AND  TDR_ID = TDR.TDR_ID)) ;

Une solution élégante nous a été donnée par Sonia.

Solution 2 :

SELECT TDR_LIBELLE, TDR_ID, TDR_LANGUE
FROM   T_TRADUCTION_TDR
WHERE  TDR_LANGUE = 'Français'
UNION
SELECT TDR_LIBELLE, TDR_ID, TDR_LANGUE
FROM   T_TRADUCTION_TDR
WHERE  TDR_LANGUE='Anglais'
AND  TDR_ID NOT IN (SELECT TDR_ID
FROM   T_TRADUCTION_TDR
WHERE  TDR_LANGUE='Français')

Cette solution n'utilise pas la fonction COALESCE.
Enfin, une solution sans utiliser de sous requêtes.

Solution 3 :

SELECT COALESCE(fr.TDR_LIBELLE, en.TDR_LIBELLE) AS TDR_LIBELLE,
COALESCE(fr.TDR_LANGUE, en.TDR_LANGUE) AS TDR_LANGUE,
COALESCE(fr.TDR_ID, en.TDR_ID) AS TDR_ID
FROM   T_TRADUCTION_TDR t
LEFT OUTER JOIN T_TRADUCTION_TDR as en
ON en.TDR_ID = t.TDR_ID
AND en.TDR_LANGUE='Anglais'
LEFT OUTER JOIN T_TRADUCTION_TDR as fr
ON fr.TDR_ID = t.TDR_ID
AND fr.TDR_LANGUE='Français'
GROUP BY t.TDR_ID, fr.TDR_LIBELLE, en.TDR_LIBELLE, fr.TDR_LANGUE, en.TDR_LANGUE, fr.TDR_ID, en.TDR_ID

Si l'on analyse les performances des trois requêtes à l'aide d'un outil qui visualise le plan d'exécution (MS SQL Server), on est frappé par la grande différence qui existe entre la première version et les deux suivantes...

Solution 1 (24 phases de traitement) :

Solution 2 (7 phases de traitement) :

Solution 3 (8 phases de traitement) :

Mais la manière de faire n'indique pas toujours le coût exact. Mieux vaut s'interroger sur le nombre de lectures effectuées pour chacune des requêtes :

Solution 1 :

Table 'T_TRADUCTION_TDR'. Compte d'analyses 7, lectures logiques 7, lectures physiques 0, lectures anticipées 0.

Solution 2 :

Table 'T_TRADUCTION_TDR'. Compte d'analyses 4, lectures logiques 4, lectures physiques 0, lectures anticipées 0.

Solution 3 :

Table 'T_TRADUCTION_TDR'. Compte d'analyses 9, lectures logiques 9, lectures physiques 0, lectures anticipées 0.

Autrement dit, la solution 3, pourtant assez économe en phases de traitement, est pire en terme de lecture que la première.

II-C - Solution au problème n° 13 - les bons joeurs

En fait, la difficulté réside dans la jointure... Mais un simple CROSS JOIN suffit à résoudre le problème.

Pour compter les bonne réponses il suffit d'utiliser le CASE et d'attribuer la valeur 1 si la réponse est bonne et la valeur 0 si elle est fausse. La somme de ces valeurs donne le score.

Voici donc une solution :

SELECT PNL_NOM,
CASE WHEN J.PNL_REPONSE1 = R.RPS_REPONSE1 THEN 1 ELSE 0 END +
CASE WHEN J.PNL_REPONSE2 = R.RPS_REPONSE2 THEN 1 ELSE 0 END +
CASE WHEN J.PNL_REPONSE3 = R.RPS_REPONSE3 THEN 1 ELSE 0 END +
CASE WHEN J.PNL_REPONSE4 = R.RPS_REPONSE4 THEN 1 ELSE 0 END +
CASE WHEN J.PNL_REPONSE5 = R.RPS_REPONSE5 THEN 1 ELSE 0 END AS BONNE_REPONSE
FROM  T_PANEL_PNL J
CROSS JOIN T_REPONSES_RPS R
ORDER BY BONNE_REPONSE DESC ;

Une solution dans le même genre nous a été donnée par Fabien C. :

SELECT PNL_NOM,
((SELECT COUNT(*) FROM T_REPONSES_RPS R WHERE R.RPS_REPONSE1 = J.PNL_REPONSE1) +
(SELECT COUNT(*) FROM T_REPONSES_RPS R WHERE R.RPS_REPONSE2 = J.PNL_REPONSE2) +
(SELECT COUNT(*) FROM T_REPONSES_RPS R WHERE R.RPS_REPONSE3 = J.PNL_REPONSE3) +
(SELECT COUNT(*) FROM T_REPONSES_RPS R WHERE R.RPS_REPONSE4 = J.PNL_REPONSE4) +
(SELECT COUNT(*) FROM T_REPONSES_RPS R WHERE R.RPS_REPONSE5 = J.PNL_REPONSE5)) AS T_REPONSES_RPS
FROM T_PANE id="XV"L_PNL J ;

II-D - Solution au problème n° 14 - tranches d'âge

Plusieurs petites difficultés :

- le calcul de l'âge ;

- la réalisation des tranches ;

- les effets de bords...

Pour le calcul de l'âge, je me sert de la formule suivante :

CAST(CURRENT_DATE - CLI_DATE_NAISSANCE AS INTERVAL DAY) / 365.2425

qui me donne un age décimal.

Pour la réalisation des tranches, il faut ajouter l'information puisqu'elle n'y est pas. Construisons donc une nouvelle table :

CREATE TABLE T_TRANCHE_AGE_TAG
(TAG_LIMITE INTEGER) ;

INSERT INTO T_TRANCHE_AGE_TAG VALUES (18) ;
INSERT INTO T_TRANCHE_AGE_TAG VALUES (40) ;

Ces informations nous suffisent, mais il serait plus pratique d'avoir la représentation des tranches sous la forme :

TRANCHE_MIN   TRANCHE_MAX
------------- -------------
0             18
18            40
40            NULL

Car nous pourrons alors joindre la table des clients par une fourchette de valeur à cette représentation. Bien entendu nous ferrons en sorte de représenter le NULL comme étant une valeur loin dans le futur.

Cette requête nous donne presque la solution :

SELECT *
FROM   T_TRANCHE_AGE_TAG TA1
LEFT OUTER JOIN  T_TRANCHE_AGE_TAG TA2
ON TA1.TAG_LIMITE < TA2.TAG_LIMITE ;

TAG_LIMITE  TAG_LIMITE
----------- -----------
18          40
40          NULL

Il faut juste rajouter la tranche commençant par zéro. Cela peut être fait à l'aide d'une opération d'UNION. Profitons-en pour mettre tout cela dans une vue :

CREATE VIEW V_TRANCHE_AGE_TAG
AS
SELECT TA1.TAG_LIMITE AS TRANCHE_MIN, TA2.TAG_LIMITE AS TRANCHE_MAX
FROM   T_TRANCHE_AGE_TAG TA1
LEFT OUTER JOIN  T_TRANCHE_AGE_TAG TA2
ON TA1.TAG_LIMITE < TA2.TAG_LIMITE
UNION
SELECT 0, MIN(TAG_LIMITE)
FROM   T_TRANCHE_AGE_TAG ;

Assemblons maintenant tout ceci dans la requête finale :

SELECT TRANCHE_MIN, COALESCE(TRANCHE_MAX, 9999) AS TRANCHE_MAX, COUNT(*) AS NOMBRE
FROM   V_TRANCHE_AGE_TAG TAG
INNER JOIN T_CLIENT_CLI
ON  CAST(CURRENT_DATE - CLI_DATE_NAISSANCE AS INTERVAL DAY) / 365.2425
BETWEEN TRANCHE_MIN AND COALESCE(TRANCHE_MAX, 9999)
GROUP BY TRANCHE_MIN, TRANCHE_MAX
ORDER BY TRANCHE_MIN ;

TRANCHE_MIN TRANCHE_MAX NOMBRE
----------- ----------- -----------
0           18          6
18          40          4
40          9999        13

Néanmoins cette solution n'est pas correcte. En effet ceux qui ont exactement 18 ans ou 40 ans sont comptabilisé 2 fois. Voici ce qui serait beaucoup plus correct :

SELECT TRANCHE_MIN, COALESCE(TRANCHE_MAX, 9999) AS TRANCHE_MAX, COUNT(*) AS NOMBRE
FROM   V_TRANCHE_AGE_TAG TAG
INNER JOIN T_CLIENT_CLI
ON  CAST(CURRENT_DATE - CLI_DATE_NAISSANCE AS INTERVAL DAY) / 365.2425 > TRANCHE_MIN AND
CAST(CURRENT_DATE - CLI_DATE_NAISSANCE AS INTERVAL DAY) / 365.2425 <= COALESCE(TRANCHE_MAX, 999)
GROUP BY TRANCHE_MIN, TRANCHE_MAX
ORDER BY 1 ;

La requête pour MS SQL Server :

SELECT TRANCHE_MIN, COALESCE(TRANCHE_MAX, 9999) AS TRANCHE_MAX, COUNT(*) AS NOMBRE
FROM   V_TRANCHE_AGE_TAG TAG
INNER JOIN T_CLIENT_CLI
ON  DATEDIFF(DAY, CLI_DATE_NAISSANCE, CURRENT_TIMESTAMP)  / 365.2425 > TRANCHE_MIN AND
DATEDIFF(DAY, CLI_DATE_NAISSANCE, CURRENT_TIMESTAMP)  / 365.2425 <= COALESCE(TRANCHE_MAX, 999)
GROUP BY TRANCHE_MIN, TRANCHE_ id="XVI"MAX
ORDER BY 1 ;

II-E - Solution au problème n° 15 - Les articles

La encore la technique consiste à rajouter de l'information à notre base de données. Notamment en créant une tables des articles parasites. Pour cela nous créons une table de nom T_ARTICLE_ATC avec l'article considéré et sa longueur incluant soit le blanc soit l'apostrophe avec le type SQL CHAR et non VARCHAR qui élimine les blancs finaux, et nous stockons la longueur considérée de cet article avec le blanc ou l'apostrophe, comme ceci :

CREATE TABLE T_ARTICLE_ATC
(ATC_ARTICLE  VARCHAR(8),
ATC_LONGUEUR INTEGER);

On y insère les principaux articles de la langue française :

INSERT INTO T_ARTICLE_ATC VALUES ('L''', 2) ;
INSERT INTO T_ARTICLE_ATC VALUES ('LE',  3) ;
INSERT INTO T_ARTICLE_ATC VALUES ('LA',  3) ;
INSERT INTO T_ARTICLE_ATC VALUES ('LES', 4) ;
INSERT INTO T_ARTICLE_ATC VALUES ('UN',  3) ;
INSERT INTO T_ARTICLE_ATC VALUES ('UNE', 4) ;
INSERT INTO T_ARTICLE_ATC VALUES ('DE',  3) ;
INSERT INTO T_ARTICLE_ATC VALUES ('DES', 4) ;
INSERT INTO T_ARTICLE_ATC VALUES ('D''', 2) ;

Ensuite, la requête consiste à lier les deux tables par jointure externe afin de conserver tous les titres en utilisant comme critère de jointure le début du titre par rapport au contenu de la table des articles. Si jointure il y a (donc colonne ATC_ARTICLE non vide), alors on transforme le titre en supprimant l'article en tête du titre et en le repoussant en fin entre parenthèses. La requête suivante répond exactement à la demande du sieur Bouchon :

SELECT CASE
WHEN ATC.ATC_ARTICLE IS NULL THEN OVG.OVG_TITRE
ELSE SUBSTRING(OVG.OVG_TITRE FROM ATC.ATC_LONGUEUR + 1 FOR 64 - ATC.ATC_LONGUEUR)
+ ' (' + TRIM(RIGHT, SUBSTRING(OVG.OVG_TITRE FROM 1 FOR ATC.ATC_LONGUEUR)) + ')'
END AS TITRE
FROM   T_OUVRAGES_OVG OVG
LEFT OUTER JOIN T_ARTICLE_ATC ATC
ON UPPER(SUBSTRING(OVG.OVG_TITRE FROM 1 FOR ATC.ATC_LONGUEUR))
= SUBSTRING(ATC.ATC_ARTICLE FROM 1 FOR ATC.ATC_LONGUEUR)  ;

La requête pour MS SQL Server :

SELECT CASE
WHEN ATC.ATC_ARTICLE IS NULL THEN OVG.OVG_TITRE
ELSE SUBSTRING(OVG.OVG_TITRE, ATC.ATC_LONGUEUR + 1, 64 - ATC.ATC_LONGUEUR)
+ ' (' + RTRIM(SUBSTRING(OVG.OVG_TITRE, 1, ATC.ATC_LONGUEUR)) + ')'
END AS TITRE
FROM   T_OUVRAGES_OVG OVG
LEFT OUTER JOIN T_ARTICLE_ATC ATC
ON UPPER(SUBSTRING(OVG.OVG_TITRE,   1, ATC.ATC_LONGUEUR))
= SUBSTRING(ATC.ATC_ARTIC id="XVII"LE, 1, ATC.ATC_LONGUEUR) ;

II-F - Solution au problème n° 16 - tri alphabétique

La solution consiste, comme souvent, à rajouter des informations afin de piloter l'ordre...

Ajoutons une table avec les nombres et leur correspondance littérale :

CREATE TABLE T_NOMBRE_LETTRE_NBL
(NBL_NOMBRE CHAR(1),
NBL_LETTRES VARCHAR(16));

Et insérons-y les valeurs suivantes :

INSERT INTO T_NOMBRE_LETTRE_NBL VALUES ('1', 'Un');
INSERT INTO T_NOMBRE_LETTRE_NBL VALUES ('2', 'Deux');
INSERT INTO T_NOMBRE_LETTRE_NBL VALUES ('3', 'Trois');
INSERT INTO T_NOMBRE_LETTRE_NBLVALUES ('4', 'Quatre');
INSERT INTO T_NOMBRE_LETTRE_NBL VALUES ('5', 'Cinq');
INSERT INTO T_NOMBRE_LETTRE_NBL VALUES ('6', 'Six');
INSERT INTO T_NOMBRE_LETTRE_NBLVALUES ('7', 'Sept');
INSERT INTO T_NOMBRE_LETTRE_NBLVALUES ('8', 'Huit');
INSERT INTO T_NOMBRE_LETTRE_NBLVALUES ('9', 'Neuf');
INSERT INTO T_NOMBRE_LETTRE_NBL VALUES ('0', 'Zéro');

Si votre SGBDR dispose des spécifications de collations conforme à la norme SQL, alors cette table suffit.

Dès lors, la requête s'exprime de la sorte :

SELECT LVR_TITRE,
CASE
WHEN NBL.NBL_NOMBRE IS NULL THEN LVR_TITRE
ELSE NBL_LETTRES || ' ' || SUBSTRING(LVR.LVR_TITRE FROM 2 FOR CHARACTER_LENGTH(LVR.LVR_TITRE)-1)
END COLLATE SQL_latin_DictionnaryUsage
FROM   T_LIVRE_LVR LVR
LEFT OUTER JOIN T_NOMBRE_LETTRE_NBL NBL
ON SUBSTRING(LVR.LVR_TITRE FROM 1 FOR 1) = NBL.NBL_NOMBRE
ORDER  BY 2 ;

NOTA : Le nom de la collation est donné à titre indicatif, les éditeurs ayant toute latitude à ce sujet.

La requête pour MS SQL Server :

SELECT LVR_TITRE
FROM   T_LIVRE_LVR LVR
LEFT OUTER JOIN T_NOMBRE_LETTRE_NBL NBL
ON SUBSTRING(LVR.LVR_TITRE, 1, 1) = NBL.NBL_NOMBRE
ORDER  BY CASE
WHEN NBL.NBL_NOMBRE IS NULL THEN LVR_TITRE
ELSE NBL_LETTRES + ' ' + SUBSTRING(LVR.LVR_TITRE, 2, LEN(LVR.LVR_TITRE)-1)
END  COLLATE French_CI_AI ;

SQL Server possède l'avantage de pouvoir effectuer un tri externe, c'est à dire sans que la colonne ne figure dans la clause SELECT. Ici la collation choisie permet de faire confusion entre les caractères accentuées ou non (et plus généralement les caractères diacritiques ou non : cédille, tilde, ligature...) et de confondre aussi la casse (majuscule/minuscule).

Si telle n'était pas le cas; il faudrait alors allonger la table T_NOMBRE_LETTRE_NBL avec de nouvelles entrées telles que :

INSERT INTO T_NOMBRE_LETTRE_NBL VALUES ('À', 'a');
INSERT INTO T_NOMBRE_LETTRE_NBL VALUES ('Â', 'a');
INSERT INTO T_NOMBRE_LETTRE_NBL VALUES ('â', 'a');

Etc.

Un seul problème subsisterait alors, celui de la gestion de l'espace pour le remplacement des nombres par les littéraux alors que cela n'est pas nécessaire pour id="XVIII" les caractères.

II-G - Solution au problème n° 17 - appariement

En fait une double condition doit être simultanément vraie : que tous les PER_2 de la valeur PER_1 scrutée soit dans les valeur de PER_2 en regard ET que tous les PER_2 en regard soit dans les valeurs des PER_2 de la valeur PER_1 scrutée. En négativant cette double condition, on obtient :

On joint la table sur elle-même avec équivalence pour PER_1 et différence pour PER_2. Pour plus de clarté, appelons les deux occurrences de notre table ORIGINE pour la première et DESTINATION pour la seconde :

SELECT DISTINCT ORIGINE.PER_1, DESTINATION.PER_1
FROM   T_PAIRE_PER ORIGINE
INNER JOIN T_PAIRE_PER DESTINATION
ON ORIGINE.PER_2 = DESTINATION.PER_2
AND ORIGINE.PER_1 <> DESTINATION.PER_1
-- Le premier filtre considère que toutes les valeurs PER_2 de ORIGINE doivent être présentent dans PER_2 de DESTINATION.
-- Avec cette clause A1 et A9 sont éliminées.
WHERE NOT EXISTS (SELECT *
FROM   T_PAIRE_PER
WHERE  PER_1 = ORIGINE.PER_1
AND PER_2 NOT IN (SELECT PER_2
FROM   T_PAIRE_PER
WHERE  PER_1 = DESTINATION.PER_1))
-- Le second filtre considère que toutes les valeurs PER_2 de DESTINATION doivent être présentent dans PER_2 de ORIGINE.
-- Avec cette clause A5 est éliminée.
AND  NOT EXISTS (SELECT *
FROM   T_PAIRE_PER
WHERE  PER_1 = DESTINATION.PER_1
AND PER_2 NOT IN (SELECT PER_2
FROM   T_PAIRE_PER
WHERE  PER_1 = ORIGINE.PER_1))
ORDER BY 1, 2 ;

Le premier filtre
conduit au jeu de
résultat suivant :

Le second filtre
conduit au jeu de
résultat suivant :

Solution

PER_1 PER_1
----- -----

A2    A3
A2    A4
A2    A6
A2    A8
A2    A9

A3    A6
A3    A8

A4    A2
A4    A3
A4    A6
A4    A8
A4    A9

A5    A1

A6    A3

A6    A8

A8    A3

A8    A6

PER_1 PER_1
----- -----
A1    A5

A2    A4

A3    A2
A3    A4
A3    A6
A3    A8

A4    A2

A6    A2
A6    A3
A6    A4
A6    A8

A8    A2
A8    A3
A8    A4
A8    A6

A9    A2
A9    A4

PER_1 PER_1
----- -----

A2    A4

A3    A6
A3    A8

A4    A2

A6    A3

A6    A8

A8    A3

A8    A6

Les données communes ont été mise en évidence par une couleur blanche et en gras.

On peut lire ces filtres de la manière suivante : il ne faut pas qu'il existe une valeur de PER_2 de la table origine qui ne soit pas dans la table de destination. Et vice versa pour le second filtre.

En fait le problème s'apparente à la division relationnelle et plus particulièrement à la division de Todd que Joe Celko évoque dans son ouvrage SQL for smarties (SQ id="XIX"L Avancé, Vuibert éditeur).

II-H - Solution au problème n° 18 - meilleure correspondance partielle

Pour trouver la réponse à ce problème, il faut décomposer la chaîne de caractère composant le code en autant de chaînes de caractères de longueur allant de 1 à la valeur maximum permet le format du type SQL, ici 16. Puis de comptabiliser pour chacune de ces combinaisons, celle qui sont communes à l'aide d'un GROUP BY. Enfin de retenir de ce calcul celles ayant le nombre le plus élevé d'occurrences avec la longueur la plus grande.

Pour décomposer la chaîne représentant le code, il faut une table des nombres allant de 1 à la longueur maximale de la colonnes RTE_CODE soit 16. Pour cela reprenons la table T_ENTIER_ENT de l'exercice 6 :

CREATE TABLE T_ENTIER_ENT
(ENT_N INTEGER);

INSERT INTO T_ENTIER_ENT VALUES (0);
INSERT INTO T_ENTIER_ENT VALUES (1);
INSERT INTO T_ENTIER_ENT VALUES (2);
INSERT INTO T_ENTIER_ENT VALUES (3);
INSERT INTO T_ENTIER_ENT VALUES (4);
INSERT INTO T_ENTIER_ENT VALUES (5);
INSERT INTO T_ENTIER_ENT VALUES (6);
INSERT INTO T_ENTIER_ENT VALUES (7);
INSERT INTO T_ENTIER_ENT VALUES (8);
INSERT INTO T_ENTIER_ENT VALUES (9) ;

INSERT INTO T_ENTIER_ENT
SELECT TEU.ENT_N                      -- les unités
+ 10   * TED.ENT_N             -- les dizaines
+ 100  * TEC.ENT_N             -- les centaines
+ 1000 * TEM.ENT_N             -- les milliers
FROM   T_ENTIER_ENT TEU               -- table des entiers pour les unités
CROSS JOIN T_ENTIER_ENT TED    -- table des entiers pour les dizaines
CROSS JOIN T_ENTIER_ENT TEC    -- table des entiers pour les centaines
CROSS JOIN T_ENTIER_ENT TEM    -- table des entiers pour les milliers
WHERE  TEU.ENT_N
+ 10   * TED.ENT_N
+ 100  * TEC.ENT_N
+ 1000 * TEM.ENT_N  > 9        -- empêche d'insérer les doublons des unités

Volontairement, je la peuple avec des valeurs nettement supérieures à la taille maximale de la colonne concernée...

La requête est alors la suivante :

SELECT DISTINCT R.RTE_DESTINATION, SUBSTRING(RTE_CODE FROM 1 FOR LNG) as RTE_CODE
FROM   T_ROUTE_RTE R
INNER JOIN (SELECT RTE_DESTINATION, MAX(LNG) AS LNG
FROM   (SELECT RTE_DESTINATION, RTE_CODE2, LNG, COUNT(*) AS NBR
FROM   (SELECT RTE_DESTINATION,
SUBSTRING(RTE_CODE FROM 1 FOR ENT_N) as RTE_CODE2,
ENT_N AS LNG
FROM   T_ROUTE_RTE
CROSS JOIN T_ENTIER_ENT
WHERE  ENT_N BETWEEN 1 AND CHARACTER_LENGTH(RTE_CODE)) T
GROUP  BY RTE_DESTINATION, RTE_CODE2, LNG
HAVING COUNT(*) = (SELECT COUNT(*)
FROM   T_ROUTE_RTE
WHERE  RTE_DESTINATION = T.RTE_DESTINATION
GROUP  BY RTE_DESTINATION)) TT
GROUP BY RTE_DESTINATION) TTT
ON R.RTE_DESTINATION = TTT.RTE_DESTINATION

La même requête pour MS SQL Server :

SELECT DISTINCT R.RTE_DESTINATION, SUBSTRING(RTE_CODE, 1, LNG) as RTE_CODE
FROM   T_ROUTE_RTE R
INNER JOIN (SELECT RTE_DESTINATION, MAX(LNG) AS LNG
FROM   (SELECT RTE_DESTINATION, RTE_CODE2, LNG, COUNT(*) AS NBR
FROM   (SELECT RTE_DESTINATION,
SUBSTRING(RTE_CODE, 1, ENT_N) as RTE_CODE2,
ENT_N AS LNG
FROM   T_ROUTE_RTE
CROSS JOIN T_ENTIER_ENT
WHERE  ENT_N BETWEEN 1 AND LEN(RTE_CODE)) T
GROUP  BY RTE_DESTINATION, RTE_CODE2, LNG
HAVING COUNT(*) = (SELECT COUNT(*)
FROM   T_ROUTE_RTE
WHERE  RTE_DESTINATION = T.RTE_DESTINATION
GROUP  BY RTE_DESTINATION)) TT
GROUP BY RTE_DESTINATION) TTT
ON R.RTE_DESTINATION = TTT.RTE_DESTINATION

Décomposition :

Le calcul de toutes les sous chaines se fait à l'aide d'un produit cartésien avec la table des nombres.
Pour un maximum d'efficacité on ne découpe pas les sous chaînes dont la longueur est supérieur à la taille de la données.

On spécifie aussi la longueur dans la colonne LNG

SELECT RTE_DESTINATION,
SUBSTRING(RTE_CODE FROM 1 FOR ENT_N) as RTE_CODE2,
ENT_N AS LNG
FROM   T_ROUTE_RTE
CROSS JOIN T_ENTIER_ENT
WHERE  ENT_N BETWEEN 1 AND CHARACTER_LENGTH(RTE_CODE) ;

La même requête pour MS SQL Server :

SELECT RTE_DESTINATION,
SUBSTRING(RTE_CODE, 1, ENT_N) as RTE_CODE2,
ENT_N AS LNG
FROM   T_ROUTE_RTE
CROSS JOIN T_ENTIER_ENT
WHERE  ENT_N BETWEEN 1 AND LEN(RTE_CODE) ;

Ce qui donne :

RTE_DESTINATION                  RTE_CODE2        LNG
-------------------------------- ---------------- -----------
albania-Mobile                   0                1
albania-Mobile                   00               2
albania-Mobile                   003              3
albania-Mobile                   0035             4
albania-Mobile                   00355            5
albania-Mobile                   003553           6
albania-Mobile                   0035538          7
albania-Mobile                   0                1
albania-Mobile                   00               2
albania-Mobile                   003              3
albania-Mobile                   0035             4
albania-Mobile                   00355            5
albania-Mobile                   003556           6
albania-Mobile                   0035569          7
albania-Mobile                   0                1
albania-Mobile                   00               2
albania-Mobile                   003              3
albania-Mobile                   0035             4
albania-Mobile                   00355            5
albania-Mobile                   003556           6
albania-Mobile                   0035560          7
SFR-Mobile                       0                1
SFR-Mobile                       00               2
SFR-Mobile                       007              3
SFR-Mobile                       0077             4
SFR-Mobile                       00772            5
SFR-Mobile                       007728           6
SFR-Mobile                       0077280          7
SFR-Mobile                       0                1
SFR-Mobile                       00               2
SFR-Mobile                       007              3
SFR-Mobile                       0077             4
SFR-Mobile                       00773            5
SFR-Mobile                       007739           6
SFR-Mobile                       0077390          7
BOUYGE_TEL                       0                1
BOUYGE_TEL                       00               2
BOUYGE_TEL                       007              3
BOUYGE_TEL                       0078             4
BOUYGE_TEL                       00784            5
BOUYGE_TEL                       007845           6
BOUYGE_TEL                       0078452          7

De ce résultat passé en sous requête, on compte le nombre de lignes identiques groupées par RTE_DESTINATION, RTE_CODE et longueur de la chaîne :

SELECT RTE_DESTINATION, RTE_CODE2, LNG, COUNT(*) AS NBR
FROM   (SELECT RTE_DESTINATION,
SUBSTRING(RTE_CODE FROM 1 FOR ENT_N) as RTE_CODE2,
ENT_N AS LNG
FROM   T_ROUTE_RTE
CROSS JOIN T_ENTIER_ENT
WHERE  ENT_N BETWEEN 1 AND CHARACTER_LENGTH(RTE_CODE)) T
GROUP  BY RTE_DESTINATION, RTE_CODE2, LNG ;

La même requête pour MS SQL Server :

SELECT RTE_DESTINATION, RTE_CODE2, LNG, COUNT(*) AS NBR
FROM   (SELECT RTE_DESTINATION,
SUBSTRING(RTE_CODE, 1, ENT_N) as RTE_CODE2,
ENT_N AS LNG
FROM   T_ROUTE_RTE
CROSS JOIN T_ENTIER_ENT
WHERE  ENT_N BETWEEN 1 AND LEN(RTE_CODE)) T
GROUP  BY RTE_DESTINATION, RTE_CODE2, LNG ;

RTE_DESTINATION                  RTE_CODE2        LNG         NBR
-------------------------------- ---------------- ----------- -----------
BOUYGE_TEL                       0                1           1
BOUYGE_TEL                       00               2           1
BOUYGE_TEL                       007              3           1
BOUYGE_TEL                       0078             4           1
BOUYGE_TEL                       00784            5           1
BOUYGE_TEL                       007845           6           1
BOUYGE_TEL                       0078452          7           1

SFR-Mobile                       0                1           2
SFR-Mobile                       00               2           2
SFR-Mobile                       007              3           2
SFR-Mobile                       0077             4           2

SFR-Mobile                       00772            5           1
SFR-Mobile                       007728           6           1
SFR-Mobile                       0077280          7           1
SFR-Mobile                       00773            5           1
SFR-Mobile                       007739           6           1
SFR-Mobile                       0077390          7           1

albania-Mobile                   0                1           3
albania-Mobile                   00               2           3
albania-Mobile                   003              3           3
albania-Mobile                   0035             4           3
albania-Mobile                   00355            5           3

albania-Mobile                   003553           6           1
albania-Mobile                   0035538          7           1
albania-Mobile                   003556           6           2
albania-Mobile                   0035560          7           1
albania-Mobile                   0035569          7           1

Le résultat nous apparaît comme étant celui pour lequel pour chaque RTE_DESTINATION, NBR est maximum et en second LNG est maximum. C'est ce que les lignes mise en exergue en blanc gras indique.

Filtrons d'abord sur nombre au moyen d'une clause HAVING :

SELECT RTE_DESTINATION, RTE_CODE2, LNG, COUNT(*) AS NBR
FROM   (SELECT RTE_DESTINATION,
SUBSTRING(RTE_CODE FROM 1 FOR ENT_N) as RTE_CODE2,
ENT_N AS LNG
FROM   T_ROUTE_RTE
CROSS JOIN T_ENTIER_ENT
WHERE  ENT_N BETWEEN 1 AND CHARACTER_LENGTH(RTE_CODE)) T
GROUP  BY RTE_DESTINATION, RTE_CODE2, LNG
HAVING COUNT(*) = (SELECT COUNT(*)
FROM   T_ROUTE_RTE
WHERE  RTE_DESTINATION = T.RTE_DESTINATION
GROUP  BY RTE_DESTINATION)

La même requête pour MS SQL Server :

SELECT RTE_DESTINATION, RTE_CODE2, LNG, COUNT(*) AS NBR
FROM   (SELECT RTE_DESTINATION,
SUBSTRING(RTE_CODE, 1, ENT_N) as RTE_CODE2,
ENT_N AS LNG
FROM   T_ROUTE_RTE
CROSS JOIN T_ENTIER_ENT
WHERE  ENT_N BETWEEN 1 AND LEN(RTE_CODE)) T
GROUP  BY RTE_DESTINATION, RTE_CODE2, LNG
HAVING COUNT(*) = (SELECT COUNT(*)
FROM   T_ROUTE_RTE
WHERE  RTE_DESTINATION = T.RTE_DESTINATION
GROUP  BY RTE_DESTINATION) ;

Qui donne :

RTE_DESTINATION                  RTE_CODE2        LNG         NBR
-------------------------------- ---------------- ----------- -----------
BOUYGE_TEL                       0                1           1
BOUYGE_TEL                       00               2           1
BOUYGE_TEL                       007              3           1
BOUYGE_TEL                       0078             4           1
BOUYGE_TEL                       00784            5           1
BOUYGE_TEL                       007845           6           1
BOUYGE_TEL                       0078452          7           1
SFR-Mobile                       0                1           2
SFR-Mobile                       00               2           2
SFR-Mobile                       007              3           2
SFR-Mobile                       0077             4           2
albania-Mobile                   0                1           3
albania-Mobile                   00               2           3
albania-Mobile                   003              3           3
albania-Mobile                   0035             4           3
albania-Mobile                   00355            5           3

Dans ce résultat passé en sous requête, on recherche ensuite la longueur la plus grande pour chaque RTE_DESTINATION :

SELECT RTE_DESTINATION, MAX(LNG) AS LNG
FROM   (SELECT RTE_DESTINATION, RTE_CODE2, LNG, COUNT(*) AS NBR
FROM   (SELECT RTE_DESTINATION,
SUBSTRING(RTE_CODE FROM 1 FOR ENT_N) as RTE_CODE2,
ENT_N AS LNG
FROM   T_ROUTE_RTE
CROSS JOIN T_ENTIER_ENT
WHERE  ENT_N BETWEEN 1 AND CHARACTER_LENGTH(RTE_CODE)) T
GROUP  BY RTE_DESTINATION, RTE_CODE2, LNG
HAVING COUNT(*) = (SELECT COUNT(*)
FROM   T_ROUTE_RTE
WHERE  RTE_DESTINATION = T.RTE_DESTINATION
GROUP  BY RTE_DESTINATION)) TT
GROUP BY RTE_DESTINATION ;

La même requête pour MS SQL Server :

SELECT RTE_DESTINATION, MAX(LNG) AS LNG
FROM   (SELECT RTE_DESTINATION, RTE_CODE2, LNG, COUNT(*) AS NBR
FROM   (SELECT RTE_DESTINATION,
SUBSTRING(RTE_CODE, 1, ENT_N) as RTE_CODE2,
ENT_N AS LNG
FROM   T_ROUTE_RTE
CROSS JOIN T_ENTIER_ENT
WHERE  ENT_N BETWEEN 1 AND LEN(RTE_CODE)) T
GROUP  BY RTE_DESTINATION, RTE_CODE2, LNG
HAVING COUNT(*) = (SELECT COUNT(*)
FROM   T_ROUTE_RTE
WHERE  RTE_DESTINATION = T.RTE_DESTINATION
GROUP  BY RTE_DESTINATION)) TT
GROUP BY RTE_DESTINATION ;

Qui donne :

RTE_DESTINATION                  LNG
-------------------------------- -----------
BOUYGE_TEL                       7
albania-Mobile                   5
SFR-Mobile                       4

Il ne suffit plus que de recoller les données avec la table d'origine et c'est ce qui donne la requête vue au début.

Il est important que le type de la colonne RTE_CODE soit en fixe (CH id="XX"AR) et non en alfa variable (VARCHAR).

II-I - Solution au problème n° 19 - la médiane

La définition stricte de la médiane est la suivante : une valeur pour laquelle, lorsque toutes les valeurs sont classées, il y a autant de valeurs ordonnées avant qu'après. Pour réaliser ce traitement, on peut considérer que la médiane consiste à classer un ensemble en deux sous ensembles :

- l'un contenant les valeurs en dessous de la valeur cherchée,

- l'autre contenant les valeurs au dessus des valeurs cherchées,

et à condition que ces deux ensembles comportent le même nombre de lignes.

II-I-1 - Solution question 1 : les données à calculées sont toutes différentes (il n'y a pas de doublon) et en nombre impaires :

SSTT_ID      STT_VALEUR
----------- -----------------------------------------------------
1           22.0
3           22.5
5           23.0 <-- la médiane est ici : 23
4           24.0
2           27.5

Pour calculer le nombre de valeurs en dessous, on peut faire :

SELECT COUNT(*)
FROM   T_STATISTIQUES_STT SOU
WHERE  STT_VALEUR < [la valeur cherchée]  ;

Idem pour les valeurs au dessus :

SELECT COUNT(*)
FROM   T_STATISTIQUES_STT SUR
WHERE  STT_VALEUR > [la valeur cherchée]  ;

Lorsque les deux sous ensemble compte le même nombre de lignes à une ligne près, alors la valeur cherchée est la bonne. Une autre manière de formuler cela est de dire que le nombre de ligne de l'ensemble SUR moins le nombre de lignes de l'ensemble SOU doit être 0, 1 ou -1, suivant que le nombre de lignes total est pair ou impair. On peut exprime cela avec l'expression suivante :

ABS((SELECT COUNT(*)
FROM   T_STATISTIQUES_STT SOU
WHERE  STT_VALEUR < [la valeur cherchée]) -
(SELECT COUNT(*)
FROM   T_STATISTIQUES_STT SUR
WHERE  STT_VALEUR > [la valeur cherchée])) <= 1

Pour obtenir la valeur cherchée, il suffit d'encapsuler cette expression en tant que sous requête corrélée :

SELECT *
FROM   T_STATISTIQUES_STT STT
WHERE  ABS((SELECT COUNT(*)
FROM   T_STATISTIQUES_STT SOU
WHERE  SOU.STT_VALEUR < STT.STT_VALEUR) -
(SELECT COUNT(*)
FROM   T_STATISTIQUES_STT SUR
WHERE  SUR.STT_VALEUR > STT.STT_VALEUR)) <= 1 ;

II-I-2 - Solution question 2 : les données à calculées sont toutes différentes (il n'y a pas de doublon) et en nombre paires :

Seulement cette solution ne permet de trouver la bonne valeur que dans le cas ou les occurrences sont en nombre impaires. Dans le cas ou les lignes sont en nombre pair, par exemple en rajoutant la ligne (INSERT INTO T_STATISTIQUES1_ST1 VALUES (8, 23.5)), la médiane doit maintenant être calculée comme suit :

STT_ID      STT_VALEUR
----------- -----------------------------------------------------
1           22.0
3           22.5
5           23.0
<-- la médiane est ici : moyenne de 23.0 et 23.5, soit 23.25
6           23.5
4           24.0
2           27.5

la requête précédente fournit le résultat suivant :

STT_ID      STT_VALEUR
----------- -----------------------------------------------------
5           23.0
6           23.5

Il convient donc de réaliser la moyenne de ce résultat pour exprimer la médiane. Cela s'effectue à l'aide de l'expression :

SELECT AVG(STT_VALEUR) AS MEDIANE
FROM   (SELECT STT_VALEUR
FROM   T_STATISTIQUES_STT STT
WHERE  ABS((SELECT COUNT(*)
FROM   T_STATISTIQUES_STT SOU
WHERE  SOU.STT_VALEUR < STT.STT_VALEUR) -
(SELECT COUNT(*)
FROM   T_STATISTIQUES_STT SUR
WHERE  SUR.STT_VALEUR > STT.STT_VALEUR)) <= 1 ) T

MEDIANE
--------------
23.75

II-I-3 - Solution question 3 : les données à calculer admettent des doublons et un nombre de lignes impairs

On a ajouté des lignes à notre jeu d'essai :

INSERT INTO T_STATISTIQUES_STT VALUES (7, 22.0);
INSERT INTO T_STATISTIQUES_STT VALUES (8, 22.0);
INSERT INTO T_STATISTIQUES_STT VALUES (9, 22.0);
INSERT INTO T_STATISTIQUES_STT VALUES (10, 22.0);
INSERT INTO T_STATISTIQUES_STT VALUES (11, 22.0);

Dès lors, la médiane à calculer se trouve ici :

STT_ID      STT_VALEUR
----------- -----------------------------------------------------
1           22.0
10          22.0
11          22.0
8           22.0
7           22.0

9           22.0 <-- la médiane est ici : 22.0

3           22.5
5           23.0
6           23.5
4           24.0
2           27.5

L'application de notre requête précédemment mise au point nous donne :

MEDIANE
--------------
NULL

Ce qui n'est pas tout à fait la bonne réponse.... ! Que s'est-il passé ?

Constatons tout d'abord que l'ensemble inférieur (SOU) est formé d'une seule valeur. Dès lors les opérations ensemblistes ne sont pas capable de faire la distinction entre les différentes valeurs. La limite entre les deux ensembles se positionne donc en dehors (ici dessous) de l'équilibre des lignes, et rien n'est retourné par la requête principale.

Une solution pour contourner le problème des lignes paires et impaires est de dédoubler le nombre des lignes. Cela ne change rien à la médiane de multiplier par deux les occurrences si ces nouvelles occurrences sont des lignes dupliquées, mais cela permet d'avoir l'assurance que l'on travaille sur un nombre le lignes paires. Faisons cela à l'aide d'une vue :

CREATE VIEW V_STATISTIQUES_STT
AS
SELECT *
FROM   T_STATISTIQUES_STT
UNION ALL
SELECT *
FROM   T_STATISTIQUES_STT

Dès lors, on peut exprimer notre précédente requête par :

SELECT STT_VALEUR
FROM   V_STATISTIQUES_STT STT
WHERE  (SELECT COUNT(*)
FROM   T_STATISTIQUES_STT) <= (SELECT COUNT(*)
FROM   V_STATISTIQUES_STT AS SOU
WHERE  SOU.STT_VALEUR >= STT.STT_VALEUR)
AND (SELECT COUNT(*)
FROM   T_STATISTIQUES_STT) <= (SELECT COUNT(*)
FROM   V_STATISTIQUES_STT AS SOU
WHERE  SOU.STT_VALEUR <= STT.STT_VALEUR)

Remarquez comment nous avons changé notre requête pour nous adapté au nouveau cas en exigeant que le nombre de lignes de la vue en dessous (ou au dessus) de la valeur soit égal au nombre de ligne original de la table... Bien entendu cette requête renvoie systématiquement un multiple de deux lignes puisqu'elle opère sur un dédoublement des valeurs de la table. Il faut alors faire une moyenne des valeurs retournées :

SELECT AVG(STT_VALEUR) AS MEDIANE
FROM   (SELECT STT_VALEUR
FROM   V_STATISTIQUES_STT STT
WHERE  (SELECT COUNT(*)
FROM   T_STATISTIQUES_STT) <= (SELECT COUNT(*)
FROM   V_STATISTIQUES_STT AS SOU
WHERE  SOU.STT_VALEUR <= STT.STT_VALEUR)
AND (SELECT COUNT(*)
FROM   T_STATISTIQUES_STT) <= (SELECT COUNT(*)
FROM   V_STATISTIQUES_STT AS SUR
WHERE  SUR.STT_VALEUR >= STT.STT_VALEUR) ) T

Mais cette requête n'est pas encore parfaite... En effet, si nous ajoutons la ligne suivante :

INSERT INTO T_STATISTIQUES_STT VALUES (12, 22.5)

La médiane devrait être :

STT_ID      STT_VALEUR
----------- -----------------------------------------------------
1           22.0
7           22.0
8           22.0
9           22.0
10          22.0
11          22.0
<-- la médiane est ici : moyenne de 22.0 et 22.5, soit 22.25
3           22.5
12          22.5
5           23.0
6           23.5
4           24.0
2           27.5

Or la requête précédente nous donne pour valeur :

MEDIANE
-----------
22.125

Simplement parce que le dédoublement des valeurs peut conduire à des occurrences quadruples ou plus encore des valeurs. Mais par définition la médiane dans ce cas étant la moyenne des deux valeurs, il suffit d'ajouter un DISTINCT dans le calcul de la fonction SQL AVG...

Finalement notre requête pour exprimer la médiane est en définitive :

SELECT AVG(DISTINCT STT_VALEUR) AS MEDIANE
FROM   (SELECT STT_VALEUR
FROM   V_STATISTIQUES_STT STT
WHERE  (SELECT COUNT(*)
FROM   T_STATISTIQUES_STT) <= (SELECT COUNT(*)
FROM   V_STATISTIQUES_STT AS SOU
WHERE  SOU.STT_VALEUR <= STT.STT_VALEUR)
AND (SELECT COUNT(*)
FROM   T_STATISTIQUES_STT) <= (SELECT COUNT(*)
FROM   V_STATISTIQUES_STT AS SUR
WHERE  SUR.STT_VALEUR >= STT.STT_VALEUR) ) T ;

Et si nous ne voulons pas de vue, cette requête peut finalement s'écrire :

SELECT AVG(DISTINCT STT_VALEUR) AS MEDIANE
FROM   (SELECT STT_VALEUR
FROM   (SELECT *
FROM   T_STATISTIQUES_STT
UNION  ALL
SELECT *
FROM   T_STATISTIQUES_STT) STT
WHERE  (SELECT COUNT(*)
FROM   T_STATISTIQUES_STT) <= (SELECT COUNT(*)
FROM   (SELECT *
FROM   T_STATISTIQUES_STT
UNION  ALL
SELECT *
FROM   T_STATISTIQUES_STT) AS SOU
WHERE  SOU.STT_VALEUR <= STT.STT_VALEUR)
AND (SELECT COUNT(*)
FROM   T_STATISTIQUES_STT) <= (SELECT COUNT(*)
FROM   (SELECT *
FROM   T_STATISTIQUES_STT
UNION  ALL
SELECT *
FROM   T_STATISTIQUES_STT) AS SUR
WHERE  SUR.STT_VALEUR >= STT.STT_VALEUR) ) T ;

Chris Date et Joe Celko se sont bien battus par papiers interposés pour trouver les failles de ces différentes problématiques de l'expression de la médiane. Ils en ont tirés différentes expressions, dont celle que je viens de vous donner est la première version corrigés de la médiane de Date.

Joe Celko propose de calculer le nombre de lignes à retourner par les deux sous ensembles à l'aide de la demi valuation du nombre total des lignes. La, requête est intéressante à décomposer :

1. Les valeurs au dessus sont exprimées par :

SELECT ST1.STT_ID, ST1.STT_VALEUR
FROM   T_STATISTIQUES_STT AS ST1
INNER JOIN T_STATISTIQUES_STT AS ST2
ON ST1.STT_VALEUR <= ST2.STT_VALEUR
GROUP  BY ST1.STT_ID, ST1.STT_VALEUR
HAVING COUNT(*) <= (SELECT COUNT(*) / 2 + 0.5
FROM T_STATISTIQUES_STT);

STT_ID      STT_VALEUR
----------- -----------------------------------------------------
3           22.5
12          22.5
5           23.0
6           23.5
4           24.0
2           27.5

2. Les valeurs en dessous sont exprimées par :

SELECT ST1.STT_ID, ST1.STT_VALEUR
FROM   T_STATISTIQUES_STT AS ST1
INNER JOIN T_STATISTIQUES_STT AS ST2
ON ST1.STT_VALEUR >= ST2.STT_VALEUR
GROUP  BY ST1.STT_ID, ST1.STT_VALEUR
HAVING COUNT(*) <= (SELECT COUNT(*) / 2 + 0.5
FROM T_STATISTIQUES_STT)

STT_ID      STT_VALEUR
----------- -----------------------------------------------------
1           22.0
7           22.0
8           22.0
9           22.0
10          22.0
11          22.0

Il ne suffit plus que de prendre le minimum des valeurs au dessus et le maximum des valeurs en dessous pour obtenir notre solution en une ou deux lignes :

SELECT MIN(STT_VALEUR) AS STT_VALEUR
-- valeurs au dessus
FROM   (SELECT ST1.STT_ID, ST1.STT_VALEUR
FROM   T_STATISTIQUES_STT AS ST1
INNER JOIN T_STATISTIQUES_STT AS ST2
ON ST1.STT_VALEUR <= ST2.STT_VALEUR
GROUP  BY ST1.STT_ID, ST1.STT_VALEUR
HAVING COUNT(*) <= (SELECT COUNT(*) / 2 + 0.5
FROM T_STATISTIQUES_STT) ) SUR
UNION  ALL
SELECT MAX(STT_VALEUR) AS STT_VALEUR
-- valeurs en dessous
FROM   (SELECT ST1.STT_ID, ST1.STT_VALEUR
FROM   T_STATISTIQUES_STT AS ST1
INNER JOIN T_STATISTIQUES_STT AS ST2
ON ST1.STT_VALEUR >= ST2.STT_VALEUR
GROUP  BY ST1.STT_ID, ST1.STT_VALEUR
HAVING COUNT(*) <= (SELECT COUNT(*) / 2 + 0.5
FROM T_STATISTIQUES_STT) ) SOU ;

Qui donne :

STT_VALEUR
-----------------------------------------------------
22.5
22.0

Nous savons désormais quoi faire, et la requête globale s'exprime de la sorte :

SELECT AVG(STT_VALEUR) AS MEDIANE
FROM (SELECT MIN(STT_VALEUR) AS STT_VALEUR
-- valeurs au dessus
FROM   (SELECT ST1.STT_ID, ST1.STT_VALEUR
FROM   T_STATISTIQUES_STT AS ST1
INNER JOIN T_STATISTIQUES_STT AS ST2
ON ST1.STT_VALEUR <= ST2.STT_VALEUR
GROUP  BY ST1.STT_ID, ST1.STT_VALEUR
HAVING COUNT(*) <= (SELECT COUNT(*) / 2 + 0.5
FROM T_STATISTIQUES_STT) ) SUR
UNION
SELECT MAX(STT_VALEUR) AS STT_VALEUR
-- valeurs en dessous
FROM   (SELECT ST1.STT_ID, ST1.STT_VALEUR
FROM   T_STATISTIQUES_STT AS ST1
INNER JOIN T_STATISTIQUES_STT AS ST2
ON ST1.STT_VALEUR >= ST2.STT_VALEUR
GROUP  BY ST1.STT_ID, ST1.STT_VALEUR
HAVING COUNT(*) <= (SELECT COUNT(*) / 2 + 0.5
FROM T_STATISTIQUES_STT) ) SOU ) T ;

Une façon plus concise d'exprimer la médiane est celle-ci :

SELECT AVG(STT_VALEUR)
FROM (SELECT E.STT_VALEUR
FROM   T_STATISTIQUES_STT E
CROSS JOIN T_STATISTIQUES_STT D
GROUP BY E.STT_VALEUR
HAVING SUM(CASE
WHEN E.STT_VALEUR = D.STT_VALEUR THEN 1
ELSE 0
END) >= ABS(SUM(SIGN(E.STT_VALEUR - D.STT_VALEUR)))
) AS T

D'autres formulations sont possibles, notamment en faisant usage d'une fonction de fenêtrage (norme SQL:2003) :

SELECT AVG(STT_VALEUR) AS MEDIANE
FROM   (SELECT *
FROM   (SELECT *, ROW_NUMBER() OVER(ORDER BY STT_VALEUR) AS RANG,
COUNT(*) OVER() AS NOMBRE
FROM   T_STATISTIQUES_STT) AS T
WHERE  (    NOMBRE % 2 = 0
AND RANG IN (NOMBRE / 2, (NOMBRE / 2) + 1 ))
OR   (    NOMBRE % 2 = 1
AND RANG = NOMBRE / 2) ) AS T ;

Enfin, si votre SGBDR supporte une fonction limitant le nombre de lignes retournées par une requête telle que TOP (MS SQL Server) ou LIMIT (MySQL), alors vous pouvez construire votre requête ainsi :

SELECT AVG(VAL)
FROM   (SELECT MAX(STT_VALEUR) AS VAL
FROM  (SELECT TOP(SELECT  CASE
WHEN N % 1 = 0 THEN (N /2) + 1
ELSE N / 2
END AS TOPN
FROM (SELECT COUNT(*) AS N
FROM   dbo.T_STATISTIQUES_STT) AS T)
STT_VALEUR
FROM   dbo.T_STATISTIQUES_STT
ORDER  BY STT_VALEUR) AS T1
UNION
SELECT MIN(STT_VALEUR) AS VAL
FROM  (SELECT TOP(SELECT  CASE
WHEN N % 1 = 0 THEN (N /2) + 1
ELSE N / 2
END AS TOPN
FROM (SELECT COUNT(*) AS N
FROM   dbo.T_STATISTIQUES_STT) AS T)
STT_VALEUR
FROM   dbo.T_STATISTIQUES_STT
ORDER  BY STT_VALEUR DESC) AS T2
) AS T ;

Un bon exercice complémentaire serait de voir quel est le coût de chacune de ces formulations et d'en déduire la meilleure. Cela risque de vous réserver que id=""lques surprises... !

II-J - Solution au problème n° 20 - insertion en bloc

Une première manière de faire est d'utiliser le produit cartésien des deux tables restreinte à l'univers visé :

INSERT INTO T_OBJET_COULEUR_OBC (OBJ_OBJET, CLR_COULEUR)
SELECT OBJ_OBJET, CLR_COULEUR
FROM   T_OBJET_OBJ
CROSS JOIN T_COULEUR_CLR
WHERE  OBJ_OBJET = 'feu tricolore'    AND CLR_COULEUR IN ('rouge', 'vert', 'orange')
OR   OBJ_OBJET = 'drapeau français' AND CLR_COULEUR IN ('bleu', 'blanc', 'rouge') ;

Une façon plus basique est de réaliser une union des lignes à insérer :

INSERT INTO T_OBJET_COULEUR_OBC (OBJ_OBJET, CLR_COULEUR)
SELECT OBJ_OBJET, 'rouge' FROM T_OBJET_OBJ WHERE OBJ_OBJET = 'feu tricolore'
UNION
SELECT OBJ_OBJET, 'vert' FROM T_OBJET_OBJ WHERE OBJ_OBJET = 'feu tricolore'
UNION
SELECT OBJ_OBJET, 'orange' FROM T_OBJET_OBJ WHERE OBJ_OBJET = 'feu tricolore'
UNION
SELECT OBJ_OBJET, 'bleu' FROM T_OBJET_OBJ WHERE OBJ_OBJET = 'drapeau français'
UNION
SELECT OBJ_OBJET, 'blanc' FROM T_OBJET_OBJ WHERE OBJ_OBJET = 'drapeau français'
UNION
SELECT OBJ_OBJET, 'rouge' FROM T_OBJET_OBJ WHERE OBJ_OBJET = 'drapeau français' ;

C'est très verbeux, mais certains SGBDR acceptent de s'affranchir de la clause FROM. Dans ce cas c'est plus rapide.

Exemple pour MS SQL Server :

INSERT INTO T_OBJET_COULEUR_OBC (OBJ_OBJET, CLR_COULEUR)
SELECT 'feu tricolore', 'rouge'
UNION
SELECT 'feu tricolore', 'vert'
UNION
SELECT 'feu tricolore', 'orange'
UNION
SELECT 'drapeau français', 'bleu'
UNION
SELECT 'drapeau français', 'blanc'
UNION
SELECT 'drapeau français', 'rouge' ;

Oracle, quant à lui, exige la clause FROM, mais autorise de préciser une table virtuelle de nom DUAL :

INSERT INTO T_OBJET_COULEUR_OBC (OBJ_OBJET, CLR_COULEUR)
SELECT 'feu tricolore', 'rouge' FROM DUAL
UNION
SELECT 'feu tricolore', 'vert' FROM DUAL
UNION
SELECT 'feu tricolore', 'orange' FROM DUAL
UNION
SELECT 'drapeau français', 'bleu' FROM DUAL
UNION
SELECT 'drapeau français', 'b id=""lanc' FROM DUAL
UNION
SELECT 'drapeau français', 'rouge' FROM DUAL ;

III - Solutions - 3° partie

III-A - Solution au problème n° 21 - Ordonner et réordonner

III-A-1 - Solution question 1 : Un UPDATE combiné à un CASE permet de solutionner le problème

UPDATE T_PAYS_PAY
SET PAY_POSITION = CASE
WHEN PAY_POSITION = 5
THEN 1
WHEN PAY_POSITION >= 1 AND PAY_POSITION < 5
THEN PAY_POSITION + 1
ELSE PAY_POSITION
END ;

Le résultat après exécution de cette requête est :

SELECT *
FROM   T_PAYS_PAY
ORDER  BY PAY_POSITION ;

PAY_NOM          PAY_POSITION
---------------- ------------
France           1
Allemagne        2
Belgique         3
Croatie          4
Espagne          5
Grèce            6

Attention cependant si les items à échanger consiste à déplacer une valeur vers le bas il faut inverser le fonctionnement de cet UPDATE. Par exemple si nous voulons faire glisser l'Allemagne du 2e au 5e rang, il faut construire l'UPDATE comme suit :

UPDATE T_PAYS_PAY
SET PAY_POSITION = CASE
WHEN PAY_POSITION = 2
THEN 5
WHEN PAY_POSITION > 2 AND PAY_POSITION <= 5
THEN PAY_POSITION - 1
ELSE PAY_POSITION
END ;

III-A-2 - Solution question 2 : Écrivons maintenant cette requête de manière générique

Notons : ORIGINE la position originale de l'item à échanger et : DESTINATION la valeur finale. La requête s'écrit alors :

UPDATE T_PAYS_PAY
SET PAY_POSITION = CASE
WHEN :ORIGINE - :DESTINATION > 0
THEN
CASE
WHEN PAY_POSITION>= :DESTINATIONAND PAY_POSITION< :ORIGINE
THEN PAY_POSITION+ 1
WHEN PAY_POSITION= :ORIGINE
THEN :DESTINATION
ELSE PAY_POSITION
END
WHEN :ORIGINE - :DESTINATION < 0
THEN
CASE
WHEN PAY_POSITION= :ORIGINE
THEN :DESTINATION
WHEN PAY_POSITION> :ORIGINE AND PAY_POSITION<= :DESTINATION
THEN PAY_POSITION- 1
ELSE PAY_POSITION
END
ELSE PAY_POSITION
END ;

Exemple pour MS SQL Server - la requête paramétrée sous forme de procédure stockée :

CREATE PROCEDURE P_ECHANGE_POSITION @ORIGINE INTEGER, @DESTINATION INTEGER
AS
UPDATE T_PAYS_PAY
SET PAY_POSITION = CASE
WHEN @ORIGINE - @DESTINATION > 0
THEN
CASE
WHEN PAY_POSITION >= @DESTINATION AND PAY_POSITION < @ORIGINE
THEN PAY_POSITION + 1
WHEN PAY_POSITION = @ORIGINE
THEN @DESTINATION
ELSE PAY_POSITION
END
WHEN @ORIGINE - @DESTINATION < 0
THEN
CASE
WHEN PAY_POSITION = @ORIGINE
THEN @DESTINATION
WHEN PAY_POSITION > @ORIGINE AND PAY_POSITION <= @DESTINATION
THEN PAY_POSITION - 1
ELSE PAY_POSITION
END
ELSE PAY_POSITION
END;

III-A-3 - Solution question 3 : Renuméroter l'ordre en partant de 1 avec continuité

Avec le nouveau jeu d'essai, comment donc passer de :

PAY_NOM          PAY_POSITION
---------------- ------------
Espagne          5
Grèce            7
Belgique         8
Croatie          9
Allemagne        11
France           12

à :

PAY_NOM          PAY_POSITION
---------------- ------------
Espagne          1
Grèce            2
Belgique         3
Croatie          4
Allemagne        5
France           6

L'idée est de faire une non équi jointure pour exécuter un comptage :

SELECT T1.PAY_NOM, COUNT(T2.PAY_POSITION) NEW_POSITION
FROM   T_PAYS_PAY T1
LEFT OUTER JOIN T_PAYS_PAY T2
ON T1.PAY_POSITION  <= T2.PAY_POSITION
GROUP  BY T1.PAY_NOM ;

PAY_NOM          NEW_POSITION
---------------- ------------
Allemagne        2
Belgique         4
Croatie          3
Espagne          6
France           1
Grèce            5

Le calcul de la nouvelle position doit se faire en conservant l'ancienne en référence pour que l'UPDATE puisse se faire. Une telle requête le permet :

SELECT T1.PAY_NOM, T1.PAY_POSITION,
(SELECT COUNT(*) FROM T_PAYS_PAY) - COUNT(*) + 1 AS NEW_POSITION
FROM  T_PAYS_PAY T1
LEFT OUTER JOIN T_PAYS_PAY T2
ON T1.PAY_POSITION  <= T2.PAY_POSITION
GROUP  BY T1.PAY_NOM, T1.PAY_POSITION ;

PAY_NOM          PAY_POSITION NEW_POSITION
---------------- ------------ ------------
Espagne          5            1
Grèce            7            2
Belgique         8            3
Croatie          9            4
Allemagne        11           5
France           12           6

Requête qu'il faut maintenant corréler dans l'update de cette manière :

UPDATE T_PAYS_PAY
SET    PAY_POSITION = (SELECT (SELECT COUNT(*) FROM T_PAYS_PAY) - COUNT(*) + 1 AS NEW_POSITION
FROM   T_PAYS_PAY T1
LEFT OUTER JOIN T_PAYS_PAY T2
ON T1.PAY_POSITION  <= T2.PAY_POSITION
WHERE T1.PAY_POSITION = T_PAYS_PAY.PAY_POSITION
id=""               GROUP  BY T1.PAY_NOM, T1.PAY_POSITION) ;

III-B - Solution au problème n° 22 - Jointure hétérogène multiple

Plusieurs solutions sont possibles. Il suffit d'isoler par des extractions de sous chaîne les valeurs numériques. On utilise le SUBSTRING combiné au LIKE :

SELECT *
FROM   T_PUBLIC_PBL PBL
INNER JOIN T_PRESTATION_PST PST
ON PST.PST_P id=""UBLIC LIKE '%[' +CAST(PBL.PBL_ID AS VARCHAR(16))+']%'
ORDER  BY PBL_ID, PST_ID  ;

III-C - Solution au problème n° 23 - Insertion conditionnelle

On peut toujours insérer des données en utilisant une sous requête :

INSERT INTO...
SELECT ...

Dans la sous requête SELECT on peut placer des constantes comme 1, toto !

Le problème est de trouver la bonne requête SELECT qui retourne une seule fois la ligne 1, toto si cette ligne n'est pas déjà dans la table, sinon, aucune ligne, si ces données sont déjà dans la table. L'idée est d'utiliser le prédicat EXISTS, ou plutôt NOT EXISTS pour piloter l'apparition de la ligne à insérer.

En fait, le problème est plus compliqué qu'il n'y paraît. Il faut étudier deux cas bien distincts :

1. il n'y a rien dans la table

2. il y a des lignes dans la table.

Cette première requête fonctionne s'il y a des lignes dans la table :

INSERT INTO MATABLE (COL1, COL2)
SELECT DISTINCT 1, 'titi'
FROM   MATABLE AS M
WHERE  NOT EXISTS(SELECT *
FROM   MATABLE
WHERE  COL1 = 1
AND  COL2 = 'titi') ;

La difficulté maintenant c'est d'améliorer cette requête si la table est vide. Dans ce cas on ne peut faire appel à un filtre sur la table elle même car tout interrogation sur une table vide renvoie par principe aucune ligne ! L'idée étant alors d'utiliser une autre table. Dans ce cas, le mieux est d'utiliser une table du dictionnaire que l'on sait contenir au moins une ligne. Prenons par exemple la vue d'information de schéma INFORMATION_SCHEMA.TABLES :

INSERT INTO MATABLE (COL1, COL2)
SELECT DISTINCT 1, 'titi'
FROM   INFORMATION_SCHEMA.TABLES
WHERE  NOT EXISTS (SELECT *
FROM   MATABLE) ;

Le tout peut être aggloméré à l'aide de l'opérateur ensembliste UNION :

INSERT INTO MATABLE (COL1, COL2)
SELECT DISTINCT 1, 'tutu'
FROM   MATABLE AS M
WHERE  NOT EXISTS(SELECT *
FROM   MATABLE
WHERE  COL1 = 1
AND  COL2 = 'tutu')
UNION
SELECT DISTINCT 1, 'titi'
FROM   INFORMATION_SCHEMA.TABLES
WHERE  NOT EXISTS (SELECT *
FROM   MATABLE) ;

Si votre SGBDR le supporte, utilisez le constructeur de lignes valuées :

INSERT INTO MATABLE (COL1, COL2)
SELECT DISTINCT 1, 'tutu'
FROM   MATABLE AS M
WHERE  NOT EXISTS(SELECT *
FROM   MATABLE
WHERE (COL1 , COL2) = (1, 'tutu')
UNION
SELECT DISTINCT 1, 'titi'
FROM   INFO id=""RMATION_SCHEMA.TABLES
WHERE  NOT EXISTS (SELECT *
FROM   MATABLE) ;

III-D - Solution au problème n° 24 - Un arbre à deux niveaux

Fastoche avec une union :

SELECT ETP_ID, (SELECT MIN(EMP_ID) FROM T_EMPLOYEE_EMP) -1 AS EMP_ID, ETP_NOM AS NOM
FROM   T_ENTREPRISE_ETP
UNION
SELECT ETP_ID AS EMP_ID, EMP_ID, ' ' +EMP_NOM AS NOM
FROM   T_EMPLOYEE_EMP
ORDER  BY ETP_ID, EMP_ID ;

Mais ne pas oublier la clause de tri !

ETP_ID      EMP_ID      NOM
----------- ----------- -----------------
1           0           IBM
1           1            Durand
1           2            Dupont
1           3            Dubois
1           4            Duval
2           0           EDF
2    id=""        5            Dupond
2           6            Duhamel
2           7            Dufour

III-E - Solution au problème n° 25 - Éclater des lignes

Comme souvent dans ce genre de problème, il faut passer par une table de nombre pour éclater les lignes dans la table réponse. Mais ici la difficulté c'est que cet éclatement doit se faire avec une double condition : valeur unique de ID_REF et continuation en séquence même si ID_LIGNE varie...

Construisons tout d'abord notre table des nombres :

CREATE TABLE NUMBERS (N INT NOT NULL);

INSERT INTO NUMBERS VALUES (0);
INSERT INTO NUMBERS VALUES (1);
INSERT INTO NUMBERS VALUES (2);
INSERT INTO NUMBERS VALUES (3);
INSERT INTO NUMBERS VALUES (4);
INSERT INTO NUMBERS VALUES (5);
INSERT INTO NUMBERS VALUES (6);
INSERT INTO NUMBERS VALUES (7);
INSERT INTO NUMBERS VALUES (8);
INSERT INTO NUMBERS VALUES (9);

INSERT INTO NUMBERS
SELECT N1.N + 10 * N2.N  + 100 * N3.N + 1000 * N4.N
FROM   NUMBERS N1
CROSS JOIN NUMBERS N2
CROSS JOIN NUMBERS N3
CROSS JOIN NUMBERS N4
WHERE  N1.N + 10 * N2.N  + 100 * N3.N + 1000 * N4.N > 9 ;

ALTER TABLE NUMBERS
ADD   CONSTRAINT PK_NUMBERS PRIMARY KEY (N) ;

Constatez que j'y ai ajouté une contrainte de clef primaire assurant l'unicité. Mais cela n'était pas nécessaire...

Une première idée est de calculer la somme des quantités par ID_REF afin de préparer cet éclatement :

SELECT *, (SELECT SUM(QUANTITE)
FROM   LIGNE L1
WHERE  L1.ID_REF = L.ID_REF ) AS S
FROM LIGNE L
ORDER BY ID_REF, ID_LIGNE ;

ID_LIGNE    ID_REF      QUANTITE    S
----------- ----------- ----------- -----------
1           1           4           5
4           1           1           5
2           2           1           3
5           2           2           3
3           3           2           2
6           4           2           2

Cependant, nous constatons que S devrait valoir 4 pour la première ligne et non 5. De plus pour la seconde ligne S devrait soit valoir 1, soit être cadré entre 5 et 5. Dans ce dernier cas S devrait être cadré entre 1 et 4 pour la première ligne... Ajoutons au filtre WHERE une condition sur la valeur de la ligne :

SELECT *, (SELECT SUM(QUANTITE)
FROM   LIGNE L1
WHERE  L1.ID_REF = L.ID_REF
AND  L1.ID_LIGNE <= L.ID_LIGNE) AS SI
FROM LIGNE L
ORDER BY ID_REF, ID_LIGNE ;

ID_LIGNE    ID_REF      QUANTITE    SI
----------- ----------- ----------- -----------
1           1           4           4
4           1           1           5
2           2           1           1
5           2           2           3
3           3           2           2
6           4           2           2

Notez que j'ai appelé cette colonne SI pour "somme itérative". Transformons cette requête en vue SQL afin de pouvoir l'interroger à nouveau :

CREATE VIEW V_L1
AS
SELECT *, (SELECT SUM(QUANTITE)
FROM   LIGNE L1
WHERE  L1.ID_REF = L.ID_REF
AND  L1.ID_LIGNE <= L.ID_LIGNE) AS SI
FROM LIGNE L ;

Une nouvelle idée serait de prévoir la plage de variation de l'éclatement. Dans ce cas la plage MIN - MAX d'éclatement devrait valoir 1 - 4 pour la 1ere ligne et 5 - 5 pour la seconde. pouvons nous calculer cette plage avec les données présentent ? Oui :

SELECT ID_LIGNE, ID_REF, QUANTITE,  SI - QUANTITE + 1 AS MAX, SI AS MIN
FROM   V_L1 VL1
ORDER BY ID_REF, ID_LIGNE ;

ID_LIGNE    ID_REF      QUANTITE    MAX         MIN
----------- ----------- ----------- ----------- -----------
1           1           4           1           4
4           1           1           5           5
2           2           1           1           1
5           2           2           2           3
3           3           2           1           2
6           4           2           1           2

Dès lors, nous disposons de tous les éléments pour éclater ces lignes avec notre table de numérotation :

SELECT ID_LIGNE, ID_REF, N as SERIE
FROM   V_L1 VL1
INNER JOIN NUMBERS
ON N BETWEEN SI - QUANTITE + 1 AND SI
ORDER BY ID_REF, ID_LIGNE ;

ID_LIGNE    ID_REF      SERIE
----------- ----------- -----------
1           1           1
1           1           2
1           1           3
1           1           4
4           1           5
2           2           1
5           2           2
5           2           3
3           3           1
3           3           2
6           4           1
6           4           2

La dernière étape consiste à ajouter une numération en séquence des lignes de notre table basé sur l'ordre ID_REF, SERIE. Ceux qui possèdent une fonction de fenêtrage seront avantagés : il suffit d'ajouter à la requête précédente, la colonne :

RANK() OVER (ORDER BY ID_REF, SERIE) AS  ID_ARTICLE.

Dans le cas contraire, il faut s'inspirer de la technique du "row value constructor" pour piloter un comptage à l'aide d'une non équi jointure qui réalisera ce comptage.

Transformons d'abord la requête précédente en une seconde vue :

CREATE VIEW V_L2
AS
SELECT ID_LIGNE, ID_REF, N as SERIE
FROM   V_L1 VL1
INNER JOIN NUMBERS
ON N BETWEEN SI - QUANTITE + 1 AND SI
ORDER BY ID_REF, ID_LIGNE ;

Dès lors, cette numération de la colonne ID_ARTICLE s'exprime :

SELECT ID_REF, ID_LIGNE, SERIE,
(SELECT COUNT(*)
FROM   V_L2 VL22
WHERE  (VL22.ID_REF < VL21.ID_REF)
OR   (VL22.ID_REF = VL21.ID_REF AND VL22.SERIE <= VL21.SERIE)) as ID_ARTICLE

FROM   V_L2 VL21
ORDER BY ID_REF, ID_LIGNE ;

ID_REF      ID_LIGNE    SERIE       ID_ARTICLE
----------- ----------- ----------- -----------
1           1           1           1
1           1           2           2
1           1           3           3
1           1           4           4
1           4           5           5
2           2           1           6
2           5           2           7
2           5           3           8
3           3           1           9
3           3           2           10
4           6           1           11
4           6           2           12

Si l'on veut exprimer le tout en une seule requête, il suffit de ré encapsuler chacune des vues dans les requêtes d'origine à la manière des poupées russes :

SELECT (SELECT COUNT(*)
FROM   (SELECT ID_REF, N AS SERIE, ID_LIGNE
FROM   (SELECT *, (SELECT SUM(QUANTITE)
FROM   LIGNE L1
WHERE  L1.ID_REF = L.ID_REF
AND  L1.ID_LIGNE <= L.ID_LIGNE) AS SI
FROM   LIGNE L) VL1
INNER JOIN NUMBERS
ON N BETWEEN SI - QUANTITE + 1 AND SI) VL22
WHERE  (VL22.ID_REF < VL21.ID_REF)
OR  (VL22.ID_REF = VL21.ID_REF AND VL22.SERIE <= VL21.SERIE)) AS ID_ARTICLE,
ID_REF, SERIE, ID_LIGNE
FROM   (SELECT ID_REF, N AS SERIE, ID_LIGNE
FROM   (SELECT *, (SELECT SUM(QUANTITE)
FROM   LIGNE L1
WHERE  L1.ID_REF = L.ID_REF
AND  L1.ID_LIGNE <= L.ID_LIGNE) AS SI
FROM   LIGNE L) VL1
INNER JOIN NUMB id=""ERS
ON N BETWEEN SI - QUANTITE + 1 AND SI) VL21
ORDER  BY ID_REF, ID_LIGNE ;

III-F - Solution au problème n° 26 - Noms incrémentés

Bien évidemment, pour numéroter il faut avoir une table de nombres. Construisons-la :

CREATE TABLE T_NUM
(NUM INT NOT NULL PRIMARY KEY);

Insertion des nombres de 0 à 9

INSERT INTO T_NUM VALUES (0);
INSERT INTO T_NUM VALUES (1);
INSERT INTO T_NUM VALUES (2);
INSERT INTO T_NUM VALUES (3);
INSERT INTO T_NUM VALUES (4);
INSERT INTO T_NUM VALUES (5);
INSERT INTO T_NUM VALUES (6);
INSERT INTO T_NUM VALUES (7);
INSERT INTO T_NUM VALUES (8);
INSERT INTO T_NUM VALUES (9);

Insertion des nombres de 10 à 999

INSERT INTO T_NUM
SELECT DISTINCT T1.NUM + 10 * T2.NUM + 100 * T3.NUM
FROM   T_NUM T1
CROSS JOIN T_NUM T2
CROSS JOIN T_NUM T3
WHERE  T1.NUM + 10 * T2.NUM + 100 * T3.NUM BETWEEN 10 AND 999 ;

L'idée est de prévoir toutes les combinaisons possible de NOM + n° et de retenir la première qui n'est pas dans le lot ! mais nous allons voir que cette idée possède un petit effet de bord...

Exemple :

Tentons le coup avec 'DUPOND'

SELECT 'DUPOND' + CAST(NUM AS VARCHAR(3)) AS NEW_NOM,
NUM
FROM   T_NUM
WHERE  NOT EXISTS (SELECT *
FROM   T_UTILISATEUR_USR
WHERE  USR_NOM = 'DUPOND'+ CAST(NUM AS VARCHAR(3))) ;

NEW_NOM   NUM
--------- -----------
DUPOND0   0
DUPOND1   1
DUPOND2   2
DUPOND3   3
DUPOND4   4
DUPOND5   5
DUPOND6   6
DUPOND7   7
DUPOND8   8
DUPOND9   9
DUPOND10  10
DUPOND11  11
DUPOND12  12
DUPOND13  13
...

Mais dans notre table comme il n'y a pas de DUPOND avec un D, il faudrait insérer ce DUPOND sans numéro ! Comment faire ?

Tout simplement en transformant le zéro en chaine vide à l'aide de la structure CASE :

SELECT 'DUPOND' + CASE
WHEN NUM = 0 THEN ''
ELSE CAST(NUM AS VARCHAR(3))
END  AS NEW_NOM,
NUM
FROM   T_NUM
WHERE  NOT EXISTS (SELECT *
FROM   T_UTILISATEUR_USR
WHERE  USR_NOM = 'DUPOND' + CASE
WHEN NUM = 0 THEN ''
ELSE CAST(NUM AS VARCHAR(3))
END) ;

NEW_NOM   NUM
--------- -----------
DUPOND    0
DUPOND1   1
DUPOND2   2
DUPOND3   3
...

Il faut maintenant récupérer le premier. Ce premier est donné par la requête suivante :

SELECT MIN(NUM)
FROM   T_NUM
WHERE  NOT EXISTS (SELECT *
FROM   T_UTILISATEUR_USR
WHERE  USR_NOM = 'DUPOND' + CASE
WHEN NUM = 0 THEN ''
ELSE CAST(NUM AS VARCHAR(3))
END) ;

En raboutant ces deux requêtes, on obtient la formule d'insertion suivante :

INSERT INTO T_UTILISATEUR_USR
SELECT 4, 'DUPOND' + CASE
WHEN NUM = 0 THEN ''
ELSE CAST(NUM AS VARCHAR(3))
END  AS NEW_NOM
FROM   T_NUM
WHERE  NOT EXISTS (SELECT *
FROM   T_UTILISATEUR_USR
WHERE  USR_NOM = 'DUPOND' + CASE
WHEN NUM = 0 THEN ''
ELSE CAST(NUM AS VARCHAR(3))
END)
AND NUM = (SELECT MIN(NUM)
FROM   T_NUM
WHERE  NOT EXISTS (SELECT *
FROM   T_UTILISATEUR_USR
WHERE  USR_NOM = 'DUPOND' + CASE
WHEN NUM = 0 THEN ''
ELSE CAST(NUM AS VARCHAR(3))
END)) ;

Voici une autre formulation plus concise, qui possède l'avantage de ne nécessiter qu'une seule fois l'usage du nom à insérer :

INSERT INTO T_UTILISATEUR_USR
SELECT 4, NOM + CASE NUM
WHEN 0 THEN ''
ELSE CAST(NUM AS VARCHAR(3))
END
FROM (SELECT MIN(NOM) AS NOM, MIN(NUM) AS NUM
FROM   (SELECT 'DUPOND' AS NOM, NUM
FROM   T_NUM) AS T
WHERE   NOM + CASE NUM
WHEN 0 THEN ''
ELSE CAST(NUM AS VARCHAR(3))
END NOT IN (SELECT USR_NOM
FROM   T_UTILISATEUR_USR)) AS TT ;

Attention cependant, si vous voulez numéroter au delà de 999, prévoyez une table plus remplie et modifiez le transtypage de VARCHAR(3) pour l'adapter à votre problématique.

Si l'on analyse les plans d'exécution de ces différentes requêtes, voici ce que cela donne :

1) Solution avec NOT EXISTS :

SELECT 4, 'DUPOND' + CASE
WHEN NUM = 0 THEN ''
ELSE CAST(NUM AS VARCHAR(3))
END  AS NEW_NOM
FROM   T_NUM
WHERE  NOT EXISTS (SELECT *
FROM   T_UTILISATEUR_USR
WHERE  USR_NOM = 'DUPOND' + CASE
WHEN NUM = 0 THEN ''
ELSE CAST(NUM AS VARCHAR(3))
END)
AND NUM = (SELECT MIN(NUM)
FROM   T_NUM
WHERE  NOT EXISTS (SELECT *
FROM   T_UTILISATEUR_USR
WHERE  USR_NOM = 'DUPOND' + CASE
WHEN NUM = 0 THEN ''
ELSE CAST(NUM AS VARCHAR(3))
END)) ;

2) Solution avec sous requêtes dans la clause FROM

SELECT 4, NOM + CASE NUM
WHEN 0 THEN ''
ELSE CAST(NUM AS VARCHAR(3))
END
FROM (SELECT MIN(NOM) AS NOM, MIN(NUM) AS NUM
FROM   (SELECT 'DUPOND' AS NOM, NUM
FROM   T_NUM) AS T
WHERE   NOM + CASE NUM
WHEN 0 THEN ''
ELSE CAST(NUM AS VARCHAR(3))
END NOT IN (SELECT USR_NOM
FROM   T_UTILISATEUR_USR)) AS TT ;

Mais il faut prendre en compte les lectures dans les tables....

D'autres solutions existent notamment si vous pouvez générer un select sans table ce qui est possible sous SQL Server, ou encore si vous disposez d'une table d'une seule ligne faite pour cela, comme c'est le cas chez  ORACLE avec la célèbre table DUAL. De toutes façon, vous pouvez toujours créer une telle table ou bien même une vue suivant ce principe, par exemple :

CREATE TABLE DUAL (DUMMY NVARCHAR(1)); INSERT INTO DUAL VALUES ('X');

Comme nous avons déjà rajouté une table de nombres, je vous propose une vue comme ceci :

CREATE VIEW LIGNE
AS
SELECT SQRT(NUM) AS COLONNE
FROM   T_NUM
WHERE  NUM = 1

L'intérêt d'avoir fait une racine carrée de 1 est que la vue ne peut plus être mise à jour !

Voici maintenant quelques autres expressions de requêtes résolvant cet exercice :

SELECT MIN(NOUVEAU_NOM)
FROM   (SELECT RTRIM(USR_NOM) + CAST(NUM AS VARCHAR(34))  NOUVEAU_NOM
FROM   T_UTILISATEUR_USR U
CROSS JOIN T_NUM N
WHERE  USR_NOM = 'DUPOND'
AND    NUM > 0
UNION
SELECT 'DUPOND'
FROM   LIGNE) AS T
WHERE  NOUVEAU_NOM NOT IN (SELECT USR_NOM
FROM   T_UTILISATEUR_USR);

SELECT MIN(NOUVEAU_NOM)
FROM   (SELECT RTRIM(USR_NOM) + CAST(NUM AS VARCHAR(34))  NOUVEAU_NOM
FROM   T_UTILISATEUR_USR U
CROSS JOIN T_NUM N
WHERE  USR_NOM = 'DUPOND'
AND    NUM > 0
UNI id=""ON
SELECT 'DUPOND'
FROM   LIGNE) AS T
EXCEPT
SELECT USR_NOM
FROM   T_UTILISATEUR_USR;

III-G - Solution au problème n° 27 - Une lettre un nom

III-G-1 - Solution brute :

SELECT SUBSTRING(CTC_NOM FROM 1 FOR 1) AS LETTRE, MIN(CTC_NOM) AS CTC_NOM
FROM   T_CONTACT_CTC
GROUP  BY SUBSTRING(CTC_NOM, 1, 1) ;

Autre solution avec création d'une table des lettres : cette solution est intéressantes si nous voulons toutes les lettres de A à Z et qu'il n'y a pas de noms commençant en W par exemple...

Création table des lettres :

CREATE TABLE T_ALPHABET_ALB
(ALB_LETTRE CHAR(1) NOT NULL PRIMARY KEY);

Insérer toutes les lettres de a à z

INSERT INTO T_ALPHABET_ALB VALUES ('A');
INSERT INTO T_ALPHABET_ALB VALUES ('B');
INSERT INTO T_ALPHABET_ALB VALUES ('C');
INSERT INTO T_ALPHABET_ALB VALUES ('D');

INSERT INTO T_ALPHABET_ALB VALUES ('Z');

Solution (pour les premiers noms)

SELECT ALB_LETTRE AS LETTRE, MIN(CTC_NOM) AS CTC_NOM
FROM   T_CONTACT_CTC CTC
INNER JOIN T_ALPHABET_ALB ALB
ON UPPER(SUBSTRING(CTC_NOM FROM 1 FOR 1)) = UPPER(ALB_LETTRE)
GROUP  BY ALB_LETTRE

III-G-2 - Variante 1 : obtenir les noms des "secondes" personnes

SELECT LETTRE, CTC_NOM
FROM   (SELECT SUBSTRING(T1.CTC_NOM, 1, 1) AS LETTRE, T1.CTC_NOM AS CTC_NOM, COUNT(*) AS N
FROM   T_CONTACT_CTC T1
LEFT OUTER JOIN T_CONTACT_CTC T2
ON SUBSTRING(T1.CTC_NOM FROM 1 FOR 1) = SUBSTRING(T2.CTC_NOM FROM 1 FOR 1)
AND T1.CTC_NOM >= T2.CTC_NOM
GROUP  BY SUBSTRING(T1.CTC_NOM FROM 1 FOR 1), T1.CTC_NOM ) AS T
WHERE N = 2

III-G-3 - Variante 2 : obtenir les noms des niemes personnes

Dans la requête précédente, remplacez 2 par le chiffre voulu !

III-G-4 - Variante 3 : obtenir les noms des niemes personnes, mais si elle n'existe pas, alors la dernière !

SELECT LETTRE, MIN(CTC_NOM) AS CTC_NOM
FROM   (-- les nieme noms :
SELECT LETTRE, CTC_NOM
FROM   (SELECT SUBSTRING(T1.CTC_NOM FROM 1 FOR 1) AS LETTRE, T1.CTC_NOM AS CTC_NOM, COUNT(*) AS N
FROM   T_CONTACT_CTC T1
LEFT OUTER JOIN T_CONTACT_CTC T2
ON T1.CTC_NOM >= T2.CTC_NOM
AND SUBSTRING(T1.CTC_NOM FROM 1 FOR 1) = SUBSTRING(T2.CTC_NOM FROM 1 FOR 1)
GROUP  BY SUBSTRING(T1.CTC_NOM FROM 1 FOR 1), T1.CTC_NOM) AS T
WHERE N = 2
UNION ALL
-- les derniers noms
SELECT ALB_LETTRE AS LETTRE, MAX(CTC_NOM) AS CTC_NOM
FROM   T_CONTACT_CTC CTC
INNER JOIN T_ALPHABET_ALB ALB
ON UPPER(SUBSTRING(CTC_NOM FROM 1 FOR 1)) = UPPER(ALB_LETTRE)
GROUP  BY ALB_LETTRE) AS TT
GROUP BY LETTRE ;

III-G-5 - Solutions avec fonctions analytiques de fenêtrage (norme SQL:2003)

La solution originelle et variante 1 et 2, ici avec n = 2

SELECT LETTRE, CTC_NOM
FROM   (SELECT SUBSTRING(CTC_NOM, 1, 1) AS LETTRE, CTC_NOM,
RANK() OVER (PARTITION BY SUBSTRING(CTC_NOMFROM 1 FOR 1) ORDER BY CTC_NOM) AS N
FROM   T_CONTACT_CTC) AS T
WHERE  N = 2
-- pour la rendre générique, remplacer 2 par la valeur voulue dans le filtre WHERE

La solution avec les max si pas rang n

SELECT COALESCE(T1.LETTRE, T2.LETTRE) AS LETTRE,
COALESCE(T1.CTC_NOM, T2.CTC_NOM) AS CTC_NOM
FROM   (SELECT LETTRE, CTC_NOM
FROM   (SELECT SUBSTRING(CTC_NOM FROM 1 FOR 1) AS LETTRE, CTC_NOM,
RANK() OVER (PARTITION BY SUBSTRING(CTC_NOM FROM 1 FOR 1) ORDER BY CTC_NOM) AS N
FROM   T_CONTACT_CTC) AS T
WHERE  N = 2) AS T1
RIGHT OUTER JOIN (SELECT SUBSTRING(CTC_NOM FROM 1 FOR 1) AS LETTRE, MAX(CTC_NOM) AS CTC_NOM
FROM   T_CONTACT_CTC
GRO id=""UP BY SUBSTRING(CTC_NOM FROM 1 FOR 1)) AS T2
ON T1.LETTRE = T2.LETTRE

III-H - Solution au problème n° 28 - Filtrer les adresses IP

La table proposée viole notablement la première forme normale qui indique que tout attribut doit être atomique, c'est à dire non décomposable. Or le moins que l'on puisse dire c'est qu'une adresse IP est au moins composée de 4 parties, et devrait l'être de 6 dès que la norme IPV6 sera en place.

Qu'à cela ne tienne... Il suffit de substituer à la table une autre rectifiant l'erreur de modélisation. Voici donc dans un premier temps une requête faisant ce travail. Requête qui gagnerait à être transforme en vue !

Extraction du premier membre de l'adresse IP :

SELECT TIP_ID, SUBSTRING(TIP_ADR, 1, CHARINDEX('.', TIP_ADR) - 1) AS BIP_ADR1
FROM TIP

TIP_ID      BIP_ADR1
----------- ---------------
1           10
2           10
3           10
4           10

Pour extraire le suivant, il faut se débarrasser du premier :

SELECT TIP_ID,
SUBSTRING(TIP_ADR, 1, CHARINDEX('.', TIP_ADR) - 1) AS BIP_ADR1,
SUBSTRING(TIP_ADR, CHARINDEX('.', TIP_ADR) + 1, LEN(TIP_ADR) - CHARINDEX('.', TIP_ADR)) AS TIP_ADR
FROM   TIP

TIP_ID      BIP_ADR1        TIP_ADR
----------- --------------- ---------------
1           10              120.12.1
2           10              130.23.1
3           10              130.201.1
4           10              13.11.1

puis recommencer le processus par imbrication des requêtes.

Pour les deux premiers :

SELECT TIP_ID, BIP_ADR1, SUBSTRING(TIP_ADR, 1, CHARINDEX('.', TIP_ADR) - 1) AS BIP_ADR2,
SUBSTRING(TIP_ADR, CHARINDEX('.', TIP_ADR) + 1, LEN(TIP_ADR) - CHARINDEX('.', TIP_ADR)) AS TIP_ADR
FROM (SELECT TIP_ID,
SUBSTRING(TIP_ADR, 1, CHARINDEX('.', TIP_ADR) - 1) AS BIP_ADR1,
SUBSTRING(TIP_ADR, CHARINDEX('.', TIP_ADR) + 1, LEN(TIP_ADR) - CHARINDEX('.', TIP_ADR)) AS TIP_ADR
FROM   TIP) T

TIP_ID      BIP_ADR1        BIP_ADR2        TIP_ADR
----------- --------------- --------------- ---------------
1           10              120             12.1
2           10              130             23.1
3           10              130             201.1
4           10              13              11.1

Pour les trois premiers :

SELECT TIP_ID, BIP_ADR1, BIP_ADR2,
SUBSTRING(TIP_ADR, 1, CHARINDEX('.', TIP_ADR) - 1) AS BIP_ADR3,
SUBSTRING(TIP_ADR, CHARINDEX('.', TIP_ADR) + 1, LEN(TIP_ADR) - CHARINDEX('.', TIP_ADR)) AS TIP_ADR
FROM   (SELECT TIP_ID, BIP_ADR1, SUBSTRING(TIP_ADR, 1, CHARINDEX('.', TIP_ADR) - 1) AS BIP_ADR2,
SUBSTRING(TIP_ADR, CHARINDEX('.', TIP_ADR) + 1, LEN(TIP_ADR) - CHARINDEX('.', TIP_ADR)) AS TIP_ADR
FROM (SELECT TIP_ID,
SUBSTRING(TIP_ADR, 1, CHARINDEX('.', TIP_ADR) - 1) AS BIP_ADR1,
SUBSTRING(TIP_ADR, CHARINDEX('.', TIP_ADR) + 1, LEN(TIP_ADR) - CHARINDEX('.', TIP_ADR)) AS TIP_ADR
FROM   TIP) T) TT

TIP_ID      BIP_ADR1        BIP_ADR2        BIP_ADR3        TIP_ADR
----------- --------------- --------------- --------------- ---------------
1           10              120             12              1
2           10              130             23              1
3           10              130             201             1
4           10              13              11              1

Pour les quatre :

SELECT TIP_ID, BIP_ADR1, BIP_ADR2,
SUBSTRING(TIP_ADR, 1, CHARINDEX('.', TIP_ADR) - 1) AS BIP_ADR3,
SUBSTRING(TIP_ADR, CHARINDEX('.', TIP_ADR) + 1, LEN(TIP_ADR) - CHARINDEX('.', TIP_ADR)) AS BIP_ADR4
FROM   (SELECT TIP_ID, BIP_ADR1, SUBSTRING(TIP_ADR, 1, CHARINDEX('.', TIP_ADR) - 1) AS BIP_ADR2,
SUBSTRING(TIP_ADR, CHARINDEX('.', TIP_ADR) + 1, LEN(TIP_ADR) - CHARINDEX('.', TIP_ADR)) AS TIP_ADR
FROM (SELECT TIP_ID,
SUBSTRING(TIP_ADR, 1, CHARINDEX('.', TIP_ADR) - 1) AS BIP_ADR1,
SUBSTRING(TIP_ADR, CHARINDEX('.', TIP_ADR) + 1, LEN(TIP_ADR) - CHARINDEX('.', TIP_ADR)) AS TIP_ADR
FROM   TIP) T) TT

TIP_ID      BIP_ADR1        BIP_ADR2        BIP_ADR3        BIP_ADR4
----------- --------------- --------------- --------------- ---------------
1           10              120             12              1
2           10              130             23              1
3           10              130             201             1
4           10              13              11              1

Ajout du transtypage en entier :

SELECT TIP_ID,
CAST(BIP_ADR1 AS TINYINT) AS BIP_ADR1,
CAST(BIP_ADR2 AS TINYINT) AS BIP_ADR2,
CAST(SUBSTRING(TIP_ADR, 1, CHARINDEX('.', TIP_ADR) - 1) AS TINYINT) AS BIP_ADR3,
CAST(SUBSTRING(TIP_ADR, CHARINDEX('.', TIP_ADR) + 1, LEN(TIP_ADR) - CHARINDEX('.', TIP_ADR)) AS TINYINT) AS BIP_ADR4
FROM   (SELECT TIP_ID, BIP_ADR1, SUBSTRING(TIP_ADR, 1, CHARINDEX('.', TIP_ADR) - 1) AS BIP_ADR2,
SUBSTRING(TIP_ADR, CHARINDEX('.', TIP_ADR) + 1, LEN(TIP_ADR) - CHARINDEX('.', TIP_ADR)) AS TIP_ADR
FROM (SELECT TIP_ID,
SUBSTRING(TIP_ADR, 1, CHARINDEX('.', TIP_ADR) - 1) AS BIP_ADR1,
SUBSTRING(TIP_ADR, CHARINDEX('.', TIP_ADR) + 1, LEN(TIP_ADR) - CHARINDEX('.', TIP_ADR)) AS TIP_ADR
FROM   TIP) T) TT

TIP_ID      BIP_ADR1 BIP_ADR2 BIP_ADR3 BIP_ADR4
----------- -------- -------- -------- --------
1           10       120      12       1
2           10       130      23       1
3           10       130      201      1
4           10       13       11       1

Transformation en vue

CREATE VIEW VIP
AS
SELECT TIP_ID,
CAST(BIP_ADR1 AS TINYINT) AS BIP_ADR1,
CAST(BIP_ADR2 AS TINYINT) AS BIP_ADR2,
CAST(SUBSTRING(TIP_ADR, 1, CHARINDEX('.', TIP_ADR) - 1) AS TINYINT) AS BIP_ADR3,
CAST(SUBSTRING(TIP_ADR, CHARINDEX('.', TIP_ADR) + 1, LEN(TIP_ADR) - CHARINDEX('.', TIP_ADR)) AS TINYINT) AS BIP_ADR4
FROM   (SELECT TIP_ID, BIP_ADR1, SUBSTRING(TIP_ADR, 1, CHARINDEX('.', TIP_ADR) - 1) AS BIP_ADR2,
SUBSTRING(TIP_ADR, CHARINDEX('.', TIP_ADR) + 1, LEN(TIP_ADR) - CHARINDEX('.', TIP_ADR)) AS TIP_ADR
FROM (SELECT TIP_ID,
SUBSTRING(TIP_ADR, 1, CHARINDEX('.', TIP_ADR) - 1) AS BIP_ADR1,
SUBSTRING(TIP_ADR, CHARINDEX('.', TIP_ADR) + 1, LEN(TIP_ADR) - CHARINDEX('.', TIP_ADR)) AS TIP_ADR
FROM   TIP) T) TT

À ce stade, la requête devient triviale pour les SGBDR utilisant le concept de ROW VALUE CONSTRUCTOR :

SELECT *
FROM   VIP
WHERE  (BIP_ADR1, BIP_ADR2, BIP_ADR3, BIP_ADR4) BETWEEN (10, 120, 12, 1)
AND (10, 130, 23, 1) ;

Si ce n'est pas le cas, alors il faudra faire avec la décomposition suivante :

SELECT *
FROM   VIP
WHERE  (BIP_ADR1 > 10
OR BIP_ADR1 = 10 AND BIP_ADR2 > 120
OR BIP_ADR1 = 10 AND BIP_ADR2 = 120 AND BIP_ADR3 > 12
OR BIP_ADR1 = 10 AND BIP_ADR2 = 120 AND BIP_ADR3 = 12 AND BIP_ADR4 >= 1)
AND  (BIP_ADR1 < 10
OR BIP_ADR1 = 10 AND BIP_ADR2 < 130
OR BIP_ADR1 = 10 AND BIP_ADR2 = 130 AND BIP_ADR3 < 23
OR BIP_ADR1 = 10 AND BIP_ADR2 = 130 AND BIP_ADR3 = 23 AND BIP_ADR4 <= 1) ;

Avec un petite astuce de mathématicien, on peut arriver à une formulation plus concise... En effet, les différentes parties d'une adresse étant codées sur un entier limité à 256 positions, cela revient à une énumération en base 256 que l'on peut transformer en base 10 de la sorte :

CREATE VIEW DIP
AS
SELECT BIP_ADR1 * POWER(256, 3) + BIP_ADR2 * POWER(256, 2) + BIP_ADR3 * POWER(256, 1) + BIP_ADR4 * POWER(256, 0) AS ADR_IP,
BIP_ADR1, BIP_ADR2, BIP_ADR3, BIP_ADR4
FROM   VIP ;

Dès lors, la requête devient on ne peut plus simple :

SELECT BIP_ADR1, BIP_ADR2, BIP_ADR3, BIP_ADR4
FROM   DIP
WHERE  ADR_IP BETWEEN 10 * POWER(256, 3) id="" + 120 * POWER(256, 2) + 12 * 256 + 1
AND 10 * POWER(256, 3) + 130 * POWER(256, 2) + 23 * 256 + 1 ;

III-I - Solution au problème n° 29 - Calculer l'adresses IP suivante

Une première approche consiste à calculer cette adresse à l'aide d'addition et de modulo afin de produire les éléments de l'adresse IP suivante.

Voici cette première solution :

SELECT MAC_ID, MAC_NOM, MAC_ADRIP1, MAC_ADRIP2, MAC_ADRIP3, MAC_ADRIP4,
MOD(MAC_ADRIP1 + (MAC_ADRIP2 + (MAC_ADRIP3 + (MAC_ADRIP4 + 1) / 256) / 256) / 256, 256) AS NEW_ADRIP1,
MOD(MAC_ADRIP2 + (MAC_ADRIP3 + (MAC_ADRIP4 + 1) / 256) / 256,  256) AS NEW_ADRIP2,
MOD(MAC_ADRIP3 + (MAC_ADRIP4 + 1) / 256, 256) AS NEW_ADRIP3,
MOD(MAC_ADRIP4 + 1, 256) AS NEW_ADRIP4
FROM   T_MACHINE_MAC ;

MAC_ID      MAC_NOM          MAC_ADRIP1 MAC_ADRIP2 MAC_ADRIP3 MAC_ADRIP4 NEW_ADRIP1  NEW_ADRIP2  NEW_ADRIP3  NEW_ADRIP4
----------- ---------------- ---------- ---------- ---------- ---------- ----------- ----------- ----------- -----------
1           PC               123        12         1          200        123         12          1           201
2           PC               123        12         1          255        123         12          2           0
3           PC               123        12         255        255        123         13          0           0
4           PC               123        13         0          0          123         13          0           1
5           PC               123        255        255        255        124         0           0           0

Elle pose cependant un problème de taille : l'adresse suivante de la machine 3 est en fait celle attribuée à la machine 4 ! Comment dès lors résoudre ce détail ?

Le calcul effectué pour la machine 3 va violer la contrainte d'unicité, puisque cette adresse IP est déjà utilisée par la machine 4.

Si notre ajout avait été de 2, il n'y aurait pas eût ce problème. D'où l'idée d'incrémenter avec un pas variable et de sélectionner le première place libre...

Pour incrémenter d'un pas variable, il faut une table des pas...

CREATE TABLE T_NUM (NUM_I INT NOT NULL PRIMARY KEY);
INSERT INTO T_NUM VALUES (1);
INSERT INTO T_NUM VALUES (2);
INSERT INTO T_NUM VALUES (3);
...

La requête avec pas variable devient :

SELECT MAC_ID, MAC_NOM, MAC_ADRIP1, MAC_ADRIP2, MAC_ADRIP3, MAC_ADRIP4,
MOD(MAC_ADRIP1 + (MAC_ADRIP2 + (MAC_ADRIP3 + (MAC_ADRIP4 + NUM_I) / 256) / 256) / 256, 256) AS NEW_ADRIP1,
MOD(MAC_ADRIP2 + (MAC_ADRIP3 + (MAC_ADRIP4 + NUM_I) / 256) / 256, 256) AS NEW_ADRIP2,
MOD(MAC_ADRIP3 + (MAC_ADRIP4 + NUM_I) / 256, 256) AS NEW_ADRIP3,
MOD(MAC_ADRIP4 + NUM_I, 256) AS NEW_ADRIP4
FROM   T_MACHINE_MAC
CROSS JOIN T_NUM
ORDER BY 1, 3, 4, 5, 6 ;

MAC_ID      MAC_NOM          MAC_ADRIP1 MAC_ADRIP2 MAC_ADRIP3 MAC_ADRIP4 NEW_ADRIP1  NEW_ADRIP2  NEW_ADRIP3  NEW_ADRIP4
----------- ---------------- ---------- ---------- ---------- ---------- ----------- ----------- ----------- -----------
1           PC               123        12         1          200        123         12          1           201
1           PC               123        12         1          200        123         12          1           202
1           PC               123        12         1          200        123         12          1           203
2           PC               123        12         1          255        123         12          2           0
2           PC               123        12         1          255        123         12          2           1
2           PC               123        12         1          255        123         12          2           2
3           PC               123        12         255        255        123         13          0           0
3           PC               123        12         255        255        123         13          0           1
3           PC               123        12         255        255        123         13          0           2
4           PC               123        13         0          0          123         13          0           1
4           PC               123        13         0          0          123         13          0           2
4           PC               123        13         0          0          123         13          0           3
5           PC               123        255        255        255        124         0           0           0
5           PC               123        255        255        255        124         0           0           1
5           PC               123        255        255        255        124         0           0           2

Il faut maintenant restreindre ce résultat aux seuls valeurs non déjà présente, par exemple à l'aide de NOT EXISTS :

SELECT MAC_ID, MAC_NOM, MAC_ADRIP1, MAC_ADRIP2, MAC_ADRIP3, MAC_ADRIP4,
MOD(MAC_ADRIP1 + (MAC_ADRIP2 + (MAC_ADRIP3 + (MAC_ADRIP4 + NUM_I) / 256) / 256) / 256, 256) AS NEW_ADRIP1,
MOD(MAC_ADRIP2 + (MAC_ADRIP3 + (MAC_ADRIP4 + NUM_I) / 256) / 256, 256) AS NEW_ADRIP2,
MOD(MAC_ADRIP3 + (MAC_ADRIP4 + NUM_I) / 256, 256) AS NEW_ADRIP3,
MOD(MAC_ADRIP4 + NUM_I, 256) AS NEW_ADRIP4
FROM   T_MACHINE_MAC M1
CROSS JOIN T_NUM N
WHERE  NOT EXISTS (SELECT *
FROM   T_MACHINE_MAC M2
WHERE  M2.MAC_ADRIP1 = MOD(M1.MAC_ADRIP1 + (M1.MAC_ADRIP2 + (M1.MAC_ADRIP3 + (M1.MAC_ADRIP4 + N.NUM_I) / 256) / 256) / 256, 256)
AND  M2.MAC_ADRIP2 = MOD(M1.MAC_ADRIP2 + (M1.MAC_ADRIP3 + (M1.MAC_ADRIP4 + N.NUM_I) / 256) / 256, 256)
AND  M2.MAC_ADRIP4 = MOD(M1.MAC_ADRIP3 + (M1.MAC_ADRIP4 + N.NUM_I) / 256, 256)
AND  M2.MAC_ADRIP4 = MOD(M1.MAC_ADRIP4 + N.NUM_I, 256))
ORDER BY 1, 3, 4, 5, 6 ;

MAC_ID      MAC_NOM          MAC_ADRIP1 MAC_ADRIP2 MAC_ADRIP3 MAC_ADRIP4 NEW_ADRIP1  NEW_ADRIP2  NEW_ADRIP3  NEW_ADRIP4
----------- ---------------- ---------- ---------- ---------- ---------- ----------- ----------- ----------- -----------
1           PC               123        12         1          200        123         12          1           201
1           PC               123        12         1          200        123         12          1           202
1           PC               123        12         1          200        123         12          1           203
2           PC               123        12         1          255        123         12          2           0
2           PC               123        12         1          255        123         12          2           1
2           PC               123        12         1          255        123         12          2           2
3           PC               123        12         255        255        123         13          0           1
3           PC               123        12         255        255        123         13          0           2
4           PC               123        13         0          0          123         13          0           1
4           PC               123        13         0          0          123         13          0           2
4           PC               123        13         0          0          123         13          0           3
5           PC               123        255        255        255        124         0           0           0
5           PC               123        255        255        255        124         0           0           1
5           PC               123        255        255        255        124         0           0           2

Il faut maintenant rechercher parmi ces lignes, celles ayant les poids d'IP les moins forts afin de n'avoir qu'une seule ligne par machine.

Pour cela, nous pouvons ajouter la valeur de l'incrément dans le résultat :

SELECT MAC_ID, MAC_NOM, NUM_I, MAC_ADRIP1, MAC_ADRIP2, MAC_ADRIP3, MAC_ADRIP4,
MOD(MAC_ADRIP1 + (MAC_ADRIP2 + (MAC_ADRIP3 + (MAC_ADRIP4 + NUM_I) / 256) / 256) / 256, 256) AS NEW_ADRIP1,
MOD(MAC_ADRIP2 + (MAC_ADRIP3 + (MAC_ADRIP4 + NUM_I) / 256) / 256, 256) AS NEW_ADRIP2,
MOD(MAC_ADRIP3 + (MAC_ADRIP4 + NUM_I) / 256, 256) AS NEW_ADRIP3,
MOD(MAC_ADRIP4 + NUM_I, 256) AS NEW_ADRIP4
FROM   T_MACHINE_MAC M1
CROSS JOIN T_NUM N
WHERE  NOT EXISTS (SELECT *
FROM   T_MACHINE_MAC M2
WHERE  M2.MAC_ADRIP1 = MOD(M1.MAC_ADRIP1 + (M1.MAC_ADRIP2 + (M1.MAC_ADRIP3 + (M1.MAC_ADRIP4 + N.NUM_I) / 256) / 256) / 256, 256)
AND  M2.MAC_ADRIP2 = MOD(M1.MAC_ADRIP2 + (M1.MAC_ADRIP3 + (M1.MAC_ADRIP4 + N.NUM_I) / 256) / 256, 256)
AND  M2.MAC_ADRIP4 = MOD(M1.MAC_ADRIP3 + (M1.MAC_ADRIP4 + N.NUM_I) / 256, 256)
AND  M2.MAC_ADRIP4 = MOD(M1.MAC_ADRIP4 + N.NUM_I, 256))
ORDER BY 1, 3, 4, 5, 6, 7 ;

MAC_ID      MAC_NOM          NUM_I       MAC_ADRIP1 MAC_ADRIP2 MAC_ADRIP3 MAC_ADRIP4 NEW_ADRIP1  NEW_ADRIP2  NEW_ADRIP3  NEW_ADRIP4
----------- ---------------- ----------- ---------- ---------- ---------- ---------- ----------- ----------- ----------- -----------
1           PC               1           123        12         1          200        123         12          1           201
1           PC               2           123        12         1          200        123         12          1           202
1           PC               3           123        12         1          200        123         12          1           203
2           PC               1           123        12         1          255        123         12          2           0
2           PC               2           123        12         1          255        123         12          2           1
2           PC               3           123        12         1          255        123         12          2           2
3           PC               2           123        12         255        255        123         13          0           1
3           PC               3           123        12         255        255        123         13          0           2
4           PC               1           123        13         0          0          123         13          0           1
4           PC               2           123        13         0          0          123         13          0           2
4           PC               3           123        13         0          0          123         13          0           3
5           PC               1           123        255        255        255        124         0           0           0
5           PC               2           123        255        255        255        124         0           0           1
5           PC               3           123        255        255        255        124         0           0           2

Dés lors la réponse est données par chaque ligne pour laquelle la valeur de NUM_I est minimum pour chaque MAC_ID, ce qui s'écrit :

SELECT MAC_ID, MAC_NOM, NUM_I, MAC_ADRIP1, MAC_ADRIP2, MAC_ADRIP3, MAC_ADRIP4,
MOD(MAC_ADRIP1 + (MAC_ADRIP2 + (MAC_ADRIP3 + (MAC_ADRIP4 + NUM_I) / 256) / 256) / 256, 256) AS NEW_ADRIP1,
MOD(MAC_ADRIP2 + (MAC_ADRIP3 + (MAC_ADRIP4 + NUM_I) / 256) / 256, 256) AS NEW_ADRIP2,
MOD(MAC_ADRIP3 + (MAC_ADRIP4 + NUM_I) / 256, 256) AS NEW_ADRIP3,
MOD(MAC_ADRIP4 + NUM_I, 256) AS NEW_ADRIP4
FROM   T_MACHINE_MAC M1
CROSS JOIN T_NUM N
WHERE  NOT EXISTS (SELECT *
FROM   T_MACHINE_MAC M2
WHERE  M2.MAC_ADRIP1 = MOD(M1.MAC_ADRIP1 + (M1.MAC_ADRIP2 + (M1.MAC_ADRIP3 + (M1.MAC_ADRIP4 + N.NUM_I) / 256) / 256) / 256, 256)
AND  M2.MAC_ADRIP2 = MOD(M1.MAC_ADRIP2 + (M1.MAC_ADRIP3 + (M1.MAC_ADRIP4 + N.NUM_I) / 256) / 256, 256)
AND  M2.MAC_ADRIP4 = MOD(M1.MAC_ADRIP3 + (M1.MAC_ADRIP4 + N.NUM_I) / 256, 256)
AND  M2.MAC_ADRIP4 = MOD(M1.MAC_ADRIP4 + N.NUM_I, 256))
AND  NUM_I = (SELECT MIN(NUM_I)
FROM   T_MACHINE_MAC M3
CROSS JOIN T_NUM N2
WHERE  NOT EXISTS (SELECT *
FROM   T_MACHINE_MAC M4
WHERE  M4.MAC_ADRIP1 = MOD(M3.MAC_ADRIP1 + (M3.MAC_ADRIP2 + (M3.MAC_ADRIP3 + (M3.MAC_ADRIP4 + N2.NUM_I) / 256) / 256) / 256, 256)
AND  M4.MAC_ADRIP2 = MOD(M3.MAC_ADRIP2 + (M3.MAC_ADRIP3 + (M3.MAC_ADRIP4 + N2.NUM_I) / 256) / 256, 256)
AND  M4.MAC_ADRIP4 = MOD(M3.MAC_ADRIP3 + (M3.MAC_ADRIP4 + N2.NUM_I) / 256, 256)
AND  M4.MAC_ADRIP4 = MOD(M3.MAC_ADRIP4 + N2.NUM_I, 256))
AND  M3.MAC_ID = M1.MAC_ID
GROUP  BY M3.MAC_ID )
ORDER BY 1,  4, 5, 6, 7 ;

MAC_ID      MAC_NOM          NUM_I       MAC_ADRIP1 MAC_ADRIP2 MAC_ADRIP3 MAC_ADRIP4 NEW_ADRIP1  NEW_ADRIP2  NEW_ADRIP3  NEW_ADRIP4
----------- ---------------- ----------- ---------- ---------- ---------- ---------- ----------- ----------- ----------- -----------
1           PC               1           123        12         1          200        123         12          1           201
2           PC               1           123        12         1          255        123         12          2           0
3           PC               2           123        12         255        255        123         13          0           1
4           PC               1           123        13         0          0          123         13          0           1
5           PC               1           123        255        255        255        124         0           0           0

Cette requête possède encore un dernier petit inconvénient... Essayez donc de voir ce qui se passe si une machine est ajoutée à la table et possède la dernière adresse IP ?

INSERT INTO T_MACHINE_MAC VALUES (6, 'PC', 255, 255, 255, 255) ;

C'est ce que l'on appelle un effet de bord. Comment le résoudre ?

En fait de nombreuses adresses IP sont particulières comme 0, 0, 0, 0 ou 127, 0, 0, 1, ou encore 127, 0, 0, 0 et 255,255,255,255.

Il suffit donc de rajouter à notre modèle de données, une table des adresses IP à éviter, comme ceci :

CREATE TABLE T_BAD_IP_ADRESSE_BIA
(BIA_IP1 SMALLINT NOT NULL CHECK(BIA_IP1 BETWEEN 0 AND 255),
BIA_IP2 SMALLINT NOT NULL CHECK(BIA_IP2 BETWEEN 0 AND 255),
BIA_IP3 SMALLINT NOT NULL CHECK(BIA_IP3 BETWEEN 0 AND 255),
BIA_IP4 SMALLINT NOT NULL CHECK(BIA_IP4 BETWEEN 0 AND 255),
BIA_POURQUOI  VARCHAR(256),
CONSTRAINT PK_BIA PRIMARY KEY (BIA_IP1, BIA_IP2, BIA_IP3, BIA_IP4));

INSERT INTO T_BAD_IP_ADRESSE_BIA VALUES (0,   0,     0,   0, 'interface réseau générique') ;
INSERT INTO T_BAD_IP_ADRESSE_BIA VALUES (127, 0,     0,   1, 'bouclage') ;
INSERT INTO T_BAD_IP_ADRESSE_BIA VALUES (127, 0,     0,   0, 'locale') ;
INSERT INTO T_BAD_IP_ADRESSE_BIA VALUES (255, 255, 255, 255, 'diffusion') ;

La requête prend alors la forme :

SELECT MAC_ID, MAC_NOM, NUM_I, MAC_ADRIP1, MAC_ADRIP2, MAC_ADRIP3, MAC_ADRIP4,
MOD(MAC_ADRIP1 + (MAC_ADRIP2 + (MAC_ADRIP3 + (MAC_ADRIP4 + NUM_I) / 256) / 256) / 256, 256) AS NEW_ADRIP1,
MOD(MAC_ADRIP2 + (MAC_ADRIP3 + (MAC_ADRIP4 + NUM_I) / 256) / 256, 256) AS NEW_ADRIP2,
MOD(MAC_ADRIP3 + (MAC_ADRIP4 + NUM_I) / 256, 256) AS NEW_ADRIP3,
MOD(MAC_ADRIP4 + NUM_I, 256) AS NEW_ADRIP4
FROM   T_MACHINE_MAC M1
CROSS JOIN T_NUM N
WHERE  NOT EXISTS (SELECT *
FROM   T_MACHINE_MAC M2
WHERE  M2.MAC_ADRIP1 = MOD(M1.MAC_ADRIP1 + (M1.MAC_ADRIP2 + (M1.MAC_ADRIP3 + (M1.MAC_ADRIP4 + N.NUM_I) / 256) / 256) / 256, 256)
AND  M2.MAC_ADRIP2 = MOD(M1.MAC_ADRIP2 + (M1.MAC_ADRIP3 + (M1.MAC_ADRIP4 + N.NUM_I) / 256) / 256, 256)
AND  M2.MAC_ADRIP4 = MOD(M1.MAC_ADRIP3 + (M1.MAC_ADRIP4 + N.NUM_I) / 256, 256)
AND  M2.MAC_ADRIP4 = MOD(M1.MAC_ADRIP4 + N.NUM_I, 256))
AND  NUM_I = (SELECT MIN(NUM_I)
FROM   T_MACHINE_MAC M3
CROSS JOIN T_NUM N2
WHERE  NOT EXISTS (SELECT *
FROM   T_MACHINE_MAC M4
WHERE  M4.MAC_ADRIP1 = MOD(M3.MAC_ADRIP1 + (M3.MAC_ADRIP2 + (M3.MAC_ADRIP3 + (M3.MAC_ADRIP4 + N2.NUM_I) / 256) / 256) / 256, 256)
AND  M4.MAC_ADRIP2 = MOD(M3.MAC_ADRIP2 + (M3.MAC_ADRIP3 + (M3.MAC_ADRIP4 + N2.NUM_I) / 256) / 256, 256)
AND  M4.MAC_ADRIP4 = MOD(M3.MAC_ADRIP3 + (M3.MAC_ADRIP4 + N2.NUM_I) / 256, 256)
AND  M4.MAC_ADRIP4 = MOD(M3.MAC_ADRIP4 + N2.NUM_I, 256))
AND  M3.MAC_ID = M1.MAC_ID
GROUP  BY M3.MAC_ID )
AND  NOT EXISTS(SELECT *
FROM   T_BAD_IP_ADRESSE_BIA BAD
WHERE  BAD.BIA_IP1 = MOD(M1.MAC_ADRIP1 + (M1.MAC_ADRIP2 + (M1.MAC_ADRIP3 + (M1.MAC_ADRIP4 + N.NUM_I) / 256) / 256) / 256, 256)
AND  BAD.BIA_IP2 = MOD(M1.MAC_ADRIP2 + (M1.MAC_ADRIP3 + (M1.MAC_ADRIP4 + N.NUM_I) / 256) / 256, 256)
AND  BAD.BIA_IP3 = MOD(M1.MAC_ADRIP3 + (M1.MAC_ADRIP4 + N.NUM_I) / 256, 256)
AND  BAD.BIA_IP4 = MOD(M1.MAC_ADRIP4 + N.NUM_I, 256))
ORDER BY 1,  4, 5, 6, 7

Voici une requête candidate à être synthétisée par une vue :

CREATE VIEW V_NIP -- pour New IP
AS
SELECT MAC_ID, MAC_NOM, NUM_I, MAC_ADRIP1, MAC_ADRIP2, MAC_ADRIP3, MAC_ADRIP4,
MOD(MAC_ADRIP1 + (MAC_ADRIP2 + (MAC_ADRIP3 + (MAC_ADRIP4 + NUM_I) / 256) / 256) / 256, 256) AS NEW_ADRIP1,
MOD(MAC_ADRIP2 + (MAC_ADRIP3 + (MAC_ADRIP4 + NUM_I) / 256) / 256, 256) AS NEW_ADRIP2,
MOD(MAC_ADRIP3 + (MAC_ADRIP4 + NUM_I) / 256, 256) AS NEW_ADRIP3,
MOD(MAC_ADRIP4 + NUM_I, 256) AS NEW_ADRIP4
FROM   T_MACHINE_MAC M1
CROSS JOIN T_NUM N

La requête finale allégée (et notablement plus lisible), s'écrit :

SELECT *
FROM   V_NIP AS M1
WHERE  NOT EXISTS (SELECT *       -- pas dans les adresses déjà attribuées aux machines
FROM   T_MACHINE_MAC AS M2
WHERE  M2.MAC_ADRIP1 = M1.NEW_ADRIP1
AND  M2.MAC_ADRIP2 = M1.NEW_ADRIP2
AND  M2.MAC_ADRIP4 = M1.NEW_ADRIP3
AND  M2.MAC_ADRIP4 = M1.NEW_ADRIP4)
AND  NUM_I = (SELECT MIN(NUM_I) -- recherche du plus petit n° de nouvelle adresse
FROM   V_NIP M3
WHERE  NOT EXISTS (SELECT *
FROM   T_MACHINE_MAC M4
WHERE  M4.MAC_ADRIP1 = M3.NEW_ADRIP1
AND  M4.MAC_ADRIP2 = M3.NEW_ADRIP2
AND  M4.MAC_ADRIP4 = M3.NEW_ADRIP3
AND  M4.MAC_ADRIP4 = M3.NEW_ADRIP4)
AND  M3.MAC_ID = M1.MAC_ID
GROUP  BY M3.MAC_ID )
AND  NOT EXISTS(SELECT *        -- les nouvelles adresse ne doivent pas être dans les adresses IP interdites
FROM   T_BAD_IP_ADRESSE_BIA BAD
WHERE  BAD.BIA_IP1 = M1.NEW_ADRIP1
AND  BAD.BIA_IP2 = M1.NEW_ADRIP2
id=""          AND  BAD.BIA_IP3 = M1.NEW_ADRIP3
AND  BAD.BIA_IP4 = M1.NEW_ADRIP4)
ORDER BY 1,  4, 5, 6, 7 ;

IV - Solutions - 4° partie

IV-A - Solution au problème n° 30 - Nombre de jours contigus

Comme nous l'avons dit, il faut s'aider d'une table des dates, comme ceci :

CREATE TABLE T_CALENDRIER_CLD
(CLD_DATE DATE NOT NULL PRIMARY KEY);

INSERT INTO T_CALENDRIER_CLD VALUES ('2003-12-30');
INSERT INTO T_CALENDRIER_CLD VALUES ('2003-12-31');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-01');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-02');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-03');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-04');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-05');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-06');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-07');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-08');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-09');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-10');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-11');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-12');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-13');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-14');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-15');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-16');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-17');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-18');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-19');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-20');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-21');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-22');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-23');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-24');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-25');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-26');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-27');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-28');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-29');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-30');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-31');

Voici une solution :

SELECT T1.DateInterval AS DateDebut,
CAST((SELECT MIN(T2.DateInterval)
FROM   (SELECT DC1.PNG_DATE as DateInterval, 'D' as TypeDate
FROM   T_CALENDRIER_CLD TD1
INNER JOIN T_CALENDRIER_CLD TD2
ON TD1.CLD_DATE -1 = TD2.CLD_DATE
LEFT OUTER JOIN T_PLANNING_PNG DC1
ON DC1.PNG_DATE = TD1.CLD_DATE
LEFT OUTER JOIN T_PLANNING_PNG DC2
ON DC2.PNG_DATE = TD2.CLD_DATE
WHERE  DC1.PNG_DATE IS NOT NULL AND DC2.PNG_DATE IS NULL
UNION
SELECT DC1.PNG_DATE, 'F'
FROM   T_CALENDRIER_CLD TD1
INNER JOIN T_CALENDRIER_CLD TD2
ON TD1.CLD_DATE +1 = TD2.CLD_DATE
LEFT OUTER JOIN T_PLANNING_PNG DC1
ON DC1.PNG_DATE = TD1.CLD_DATE
LEFT OUTER JOIN T_PLANNING_PNG DC2
ON DC2.PNG_DATE = TD2.CLD_DATE
WHERE  DC1.PNG_DATE IS NOT NULL AND DC2.PNG_DATE IS NULL) T2
WHERE  TypeDate = 'F'
AND  T2.DateInterval >= T1.DateInterval
GROUP  BY T1.DateInterval ) AS FLOAT) - CAST(T1.DateInterval AS FLOAT) + 1 AS NbJours
FROM   (SELECT DC1.PNG_DATE as DateInterval, 'D' as TypeDate
FROM   T_CALENDRIER_CLD TD1
INNER JOIN T_CALENDRIER_CLD TD2
ON TD1.CLD_DATE -1 = TD2.CLD_DATE
LEFT OUTER JOIN T_PLANNING_PNG DC1
ON DC1.PNG_DATE = TD1.CLD_DATE
LEFT OUTER JOIN T_PLANNING_PNG DC2
ON DC2.PNG_DATE = TD2.CLD_DATE
WHERE  DC1.PNG_DATE IS NOT NULL AND DC2.PNG_DATE IS NULL
UNION
SELECT DC1.PNG_DATE, 'F'
FROM   T_CALENDRIER_CLD TD1
INNER JOIN T_CALENDRIER_CLD TD2
ON TD1.CLD_DATE +1 = TD2.CLD_DATE
LEFT OUTER JOIN T_PLANNING_PNG DC1
ON DC1.PNG_DATE = TD1.CLD_DATE
LEFT OUTER JOIN T_PLANNING_PNG DC2
ON DC2.PNG_DATE = TD2.CLD_DATE
WHERE  DC1.PNG_DATE IS NOT NULL AND DC2.PNG_DATE IS NULL) T1
WHERE  TypeDate = 'D'

Il va falloir maintenant l'expliquer en la décortiquant...

Tout d'abord, supposons que votre SGBDR soit capable de transformer une date en nombre, par exemple en nombre à virgule flottante (FLOAT) et que la différence entre deux dates ainsi transformées donne le nombre de jour... D'ailleurs si ce n'est pas le cas, il suffit d'utiliser le type INTERVAL de la norme SQL et faire une différence en jour pour que l'on revienne au même !

Ce point étant acquis, voyons comment notre requête est construite...

La première idée a été de calculer tous les débuts de périodes de jours consécutifs et la seconde la fin de ces mêmes périodes. Une fois cela traité il ne suffit plus que de calculer la différence en nombre de jours entre ces deux dates, à condition d'avoir mis en relation les bonnes périodes !

IV-A-1 - Étape n°1 : On recherche les débuts de plage ayant des dates contiguës

SELECT DC1.PNG_DATE as DateInterval, 'D' as TypeDate
FROM   T_CALENDRIER_CLD TD1
INNER JOIN T_CALENDRIER_CLD TD2
ON TD1.CLD_DATE -1 = TD2.CLD_DATE
LEFT OUTER JOIN T_PLANNING_PNG DC1
ON DC1.PNG_DATE = TD1.CLD_DATE
LEFT OUTER JOIN T_PLANNING_PNG DC2
ON DC2.PNG_DATE = TD2.CLD_DATE
WHERE  DC1.PNG_DATE IS NOT NULL AND DC2.PNG_DATE IS NULL;

Notez le -1 dans la première jointure qui permet de voir le jour d'avant. Si votre SGBDR ne l'accepte pas, rectifiez la jointure comme suit :

ON TD1.CLD_DATE -1 DAY = TD2.CLD_DATE

Notez aussi le filtre pour lequel on recherche une date existante pour le planning avec le fait que dans ce même planning la date précédent n'existe pas !

Voici ce que donne cette requête :

DateInterval TypeDate
------------ --------
2004-01-01   D
2004-01-15   D
2004-01-30   D

Dans la colonne TypeDate, nous avons mis "D" pour début.

Notez qu'une autre variante, peut être plus performante consiste à utiliser le EXISTS :

SELECT CLD_DATE as DateInterval, 'D' as TypeDate
FROM   T_CALENDRIER_CLD TD
WHERE  NOT EXISTS (SELECT *
FROM   T_PLANNING_PNG
WHERE  PNG_DATE = TD.CLD_DATE -1 )
AND  EXISTS     (SELECT *
FROM   T_PLANNING_PNG
WHERE  PNG_DATE = TD.CLD_DATE) ;

IV-A-2 - Étape n° 2 : la seconde requête n'est guère plus complexe, pour trouver les fins de périodes

SELECT DC1.PNG_DATE, 'F'
FROM   T_CALENDRIER_CLD TD1
INNER JOIN T_CALENDRIER_CLD TD2
ON TD1.CLD_DATE +1 = TD2.CLD_DATE
LEFT OUTER JOIN T_PLANNING_PNG DC1
ON DC1.PNG_DATE = TD1.CLD_DATE
LEFT OUTER JOIN T_PLANNING_PNG DC2
ON DC2.PNG_DATE = TD2.CLD_DATE
WHERE  DC1.PNG_DATE IS NOT NULL AND DC2.PNG_DATE IS NULL ;

PNG_DATE
------------------------------------------------------ ----
2004-01-03 00:00:00.000                                F
2004-01-18 00:00:00.000                                F
2004-01-30 00:00:00.000                                F

IV-A-3 - Étape n°3 : Voici maintenant le temps venu de joindre ces éléments... Cela semble plus facile à dire qu'à faire !

Commençons simple, en unionant ces deux requêtes :

SELECT DC1.PNG_DATE as DateInterval, 'D' as TypeDate
FROM   T_CALENDRIER_CLD TD1
INNER JOIN T_CALENDRIER_CLD TD2
ON TD1.CLD_DATE -1 = TD2.CLD_DATE
LEFT OUTER JOIN T_PLANNING_PNG DC1
ON DC1.PNG_DATE = TD1.CLD_DATE
LEFT OUTER JOIN T_PLANNING_PNG DC2
ON DC2.PNG_DATE = TD2.CLD_DATE
WHERE  DC1.PNG_DATE IS NOT NULL AND DC2.PNG_DATE IS NULL
UNION
SELECT DC1.PNG_DATE, 'F'
FROM   T_CALENDRIER_CLD TD1
INNER JOIN T_CALENDRIER_CLD TD2
ON TD1.CLD_DATE +1 = TD2.CLD_DATE
LEFT OUTER JOIN T_PLANNING_PNG DC1
ON DC1.PNG_DATE = TD1.CLD_DATE
LEFT OUTER JOIN T_PLANNING_PNG DC2
ON DC2.PNG_DATE = TD2.CLD_DATE
WHERE  DC1.PNG_DATE IS NOT NULL AND DC2.PNG_DATE IS NULL

DateInterval    TypeDate
--------------- --------
2004-01-01      D
2004-01-03      F
2004-01-15      D
2004-01-18      F
2004-01-30      D
2004-01-30      F

Nous ne sommes pas très avancés, car il va falloir synthétiser des paires de lignes différentes en une seule ligne.

Pour plus de compréhension, créons une vue à partir de cette requête :

CREATE VIEW V_DAT
AS
SELECT DC1.PNG_DATE as DateInterval, 'D' as TypeDate
FROM   T_CALENDRIER_CLD TD1
INNER JOIN T_CALENDRIER_CLD TD2
ON TD1.CLD_DATE -1 = TD2.CLD_DATE
LEFT OUTER JOIN T_PLANNING_PNG DC1
ON DC1.PNG_DATE = TD1.CLD_DATE
LEFT OUTER JOIN T_PLANNING_PNG DC2
ON DC2.PNG_DATE = TD2.CLD_DATE
WHERE  DC1.PNG_DATE IS NOT NULL AND DC2.PNG_DATE IS NULL
UNION
SELECT DC1.PNG_DATE, 'F'
FROM   T_CALENDRIER_CLD TD1
INNER JOIN T_CALENDRIER_CLD TD2
ON TD1.CLD_DATE +1 = TD2.CLD_DATE
LEFT OUTER JOIN T_PLANNING_PNG DC1
ON DC1.PNG_DATE = TD1.CLD_DATE
LEFT OUTER JOIN T_PLANNING_PNG DC2
ON DC2.PNG_DATE = TD2.CLD_DATE
WHERE  DC1.PNG_DATE IS NOT NULL AND DC2.PNG_DATE IS NULL

Il est maintenant assez facile de mettre en correspondance les débuts de plage et les fins :

SELECT T1.DateInterval AS DateDebut,
(SELECT MIN(T2.DateInterval)
FROM   V_DAT T2
WHERE  TypeDate = 'F'
AND  T2.DateInterval >= T1.DateInterval
GROUP  BY T1.DateInterval ) As DateFin
FROM   V_DAT T1
WHERE  TypeDate = 'D' ;

DateDebut          DateFin
------------------ -------------------
2004-01-01         2004-01-03
2004-01-15         2004-01-18
2004-01-30         2004-01-30

IV-A-4 - Étape n°4 : la suite, et donc la touche finale, est on ne peut plus simple puisqu'il s'agit de compter le nombre de jours de chaque période ainsi extraite !

SELECT T1.DateInterval AS DateDebut,
CAST((SELECT MIN(T2.DateInterval)
FROM   V_DAT T2
WHERE  TypeDate = 'F'
AND  T2.DateInterval >= T1.DateInterval
GROUP  BY T1.DateInterval ) AS FLOAT)
- CAST(T1.DateInterval AS FLoat) +1 As Nb_jours

FROM   V_DAT T1
WHERE  TypeDate = 'D' ;

DateDebut         Nb_jours
----------------- ----------------
2004-01-01        3.0
2004-01-15        4.0
2004-01-30        1.0

Bien entendu vous pouvez à nouveau substituer à la soustra id=""ction des dates transtypée en FLOAT, un calcul d'INTERVAL si votre SGBDR le supporte !

IV-B - Solution au problème n° 31 - plus proche valeur

La valeur la plus proche signifie la valeur avec lequel l'écart entre la date souhaitée et l'ensemble des dates possible est minimum. Pour cela il faut calculer une différence en jours et comme cette différence peut se faire en plus ou en moins (avant ou après la date demandée) alors il faut obtenir la valeur absolue de cet écart.

Ceci se traduit par la requête SQL :

SELECT *
FROM   T_RENDEZ_VOUS_RDV
WHERE  ABS(RDV_DATE - CAST('2008-01-12' AS DATE) AS DAY)
= (SELECT MIN(ABS(RDV_DATE - CAST('2008-01-12' AS DATE) AS DAY))
FROM T_RENDEZ_VOUS_RDV);

Qui donne bien la bonne réponse dans le cas ou la date est le 12 janvier 2008.

Cependant avec le 23 janvier 2008... tout change. Nous nous retrouvons avec :

RDV_DATE
---------------
2008-01-22
2008-01-24

Car il y a deux dates à égale distance de celle demandée.

Qu'à cela ne tienne, il suffit de rajouter que l'on veut celle d'avant ou d'après en appliquant un min ou un max !

La requête devient :

SELECT MIN(RDV_DATE) AS RDV_DATE
FROM   T_RENDEZ_VOUS_RDV
WHERE  ABS(RDV_DATE - CAST('2008-01-12' AS DATE) AS DAY)
= (SELECT MIN(ABS(RDV_DATE - CAST('2008-01-23' AS DATE) AS DAY))
FROM T_RENDEZ_VOUS_RDV);

Voici cette même requête traduite pour MS SQL Server :

SELECT MIN(RDV_DATE) AS RDV_DATE
FROM   T_RENDEZ_VOUS_RDV
WHERE  ABS(DATEDIFF(day, RDV_DATE, CAST('2008-01-23' id="" AS DATETIME)))
= (SELECT MIN(ABS(DATEDIFF(day, RDV_DATE, CAST('2008-01-12' AS DATETIME))))
FROM T_RENDEZ_VOUS id=""_RDV);

IV-C - Solution au problème n° 32 - primes de salaires

Tout d'abord, pour "éclater" la prime sur le nombre d'échéances il nous faut une table des nombres de 1 à n.

CREATE TABLE T_NUM
(NUM INT);

INSERT INTO T_NUM VALUES (1);
INSERT INTO T_NUM VALUES (2);
INSERT INTO T_NUM VALUES (3);
INSERT INTO T_NUM VALUES (4);
INSERT INTO T_NUM VALUES (5);
INSERT INTO T_NUM VALUES (6);
INSERT INTO T_NUM VALUES (7);
INSERT INTO T_NUM VALUES (8);
INSERT INTO T_NUM VALUES (9);
INSERT INTO T_NUM VALUES (10);
INSERT INTO T_NUM VALUES (11);
INSERT INTO T_NUM VALUES (12);

Une première impression est donnée par la requête suivante :

SELECT PRM_EMPLOYE, CAST(PRM_MONTANT / PRN_NB_ECHEANCES AS DECIMAL (16, 2)) AS PART_PRIME, NUM
FROM   T_PRIME_PRM
INNER JOIN T_NUM
ON NUM BETWEEN 1 AND PRN_NB_ECHEANCES

PRM_EMPLOYE      PART_PRIME         NUM
---------------- ------------------ -----------
JAK              1000.00            1
POL              333.33             1
POL              333.33             2
POL              333.33             3
LUC              83.33              1
LUC              83.33              2
LUC              83.33              3
LUC              83.33              4
LUC              83.33              5
LUC              83.33              6
LUC              83.33              7
LUC              83.33              8
LUC              83.33              9
LUC              83.33              10
LUC              83.33              11
LUC              83.33              12

Il suffit d'ajouter à cette table l'écart constaté entre la somme des échéances et la prime originelle. Voici comment on peut calculer cet écart :

SELECT PRM_EMPLOYE, CAST(PRM_MONTANT / PRN_NB_ECHEANCES AS DECIMAL (16, 2)) AS PART_PRIME, NUM,
PRM_MONTANT - (SELECT MAX(NUM) * ( CAST(PRM_MONTANT / PRN_NB_ECHEANCES AS DECIMAL (16, 2)) )
FROM   T_PRIME_PRM
INNER JOIN T_NUM
ON NUM BETWEEN 1 AND PRN_NB_ECHEANCES
WHERE  P.PRM_EMPLOYE = PRM_EMPLOYE
GROUP  BY PRM_MONTANT, PRN_NB_ECHEANCES) as ECART
FROM   T_PRIME_PRM P
INNER JOIN T_NUM
ON NUM BETWEEN 1 AND PRN_NB_ECHEANCES

PRM_EMPLOYE      PART_PRIME         NUM         ECART
---------------- ------------------ ----------- ------------------------------
JAK              1000.00            1           .00
POL              333.33             1           .01
POL              333.33             2           .01
POL              333.33             3           .01
LUC              83.33              1           .04
LUC              83.33              2           .04
LUC              83.33              3           .04
LUC              83.33              4           .04
LUC              83.33              5           .04
LUC              83.33              6           .04
LUC              83.33              7           .04
LUC              83.33              8           .04
LUC              83.33              9           .04
LUC              83.33              10          .04
LUC              83.33              11          .04
LUC              83.33              12          .04

Simplifions-nous la vie pour la suite en transformant cette requête en vue :

CREATE VIEW V_PRIMES
AS
SELECT PRM_EMPLOYE, CAST(PRM_MONTANT / PRN_NB_ECHEANCES AS DECIMAL (16, 2)) AS PART_PRIME, NUM,
PRM_MONTANT - (SELECT MAX(NUM) * ( CAST(PRM_MONTANT / PRN_NB_ECHEANCES AS DECIMAL (16, 2)) )
FROM   T_PRIME_PRM
INNER JOIN T_NUM
ON NUM BETWEEN 1 AND PRN_NB_ECHEANCES
WHERE  P.PRM_EMPLOYE = PRM_EMPLOYE
GROUP  BY PRM_MONTANT, PRN_NB_ECHEANCES) as ECART
FROM   T_PRIME_PRM P
INNER JOIN T_NUM
ON NUM BETWEEN 1 AND PRN_NB_ECHEANCES ;

Dès lors, on peut maintenant placer la prime au niveau de la dernière échéance, comme suit :

SELECT PRM_EMPLOYE, PART_PRIME, NUM,
(SELECT ECART
FROM   V_PRIMES
WHERE  PRM_EMPLOYE = P1.PRM_EMPLOYE
AND PART_PRIME = P1.PART_PRIME
GROUP  BY PART_PRIME, PRM_EMPLOYE, ECART
HAVING MAX(NUM) = P1.NUM) REPORT_ECART
FROM   V_PRIMES P1 ;

PRM_EMPLOYE      PART_PRIME         NUM         REPORT_ECART
---------------- ------------------ ----------- ------------------------------
JAK              1000.00            1           .00
POL              333.33             1           NULL
POL              333.33             2           NULL
POL              333.33             3           .01
LUC              83.33              1           NULL
LUC              83.33              2           NULL
LUC              83.33              3           NULL
LUC              83.33              4           NULL
LUC              83.33              5           NULL
LUC              83.33              6           NULL
LUC              83.33              7           NULL
LUC              83.33              8           NULL
LUC              83.33              9           NULL
LUC              83.33              10          NULL
LUC              83.33              11          NULL
LUC              83.33              12          .04

Il ne suffit plus maintenant que d'une addition, avec dénullification de l'écart !

SELECT PRM_EMPLOYE, NUM, PART_PRIME +
COALESCE((SELECT ECART
FROM   V_PRIMES
WHERE  PRM_EMPLOYE = P1.PRM_EMPLOYE
AND PART_PRIME = P1.PART_PRIME
GROUP  BY PART_PRIME, PRM_EMPLOYE, ECART
HAVING MAX(NUM) = P1.NUM), 0) AS PART_PRIME_EXACTE
FROM   V_PRIMES P1 ;

PRM_EMPLOYE      NUM         PART_PRIME_EXACTE
---------------- ----------- -------------------------------
JAK              1           1000.00
POL              1           333.33
POL              2           333.33
POL              3           333.34
LUC              1           83.33
LUC              2           83.33
LUC              3           83.33
LUC              4           83.33
LUC              5           83.33
LUC              6           83.33
LUC              7           83.33
LUC              8           83.33
LUC              9           83.33
LUC              10          83.33
LUC              11          83.33
LUC              12          83.37

Sans l'apport de la vue, la requête s'avère un peu indigeste :

SELECT PRM_EMPLOYE, NUM, PART_PRIME +
COALESCE((SELECT ECART
FROM   (SELECT PRM_EMPLOYE, CAST(PRM_MONTANT / PRN_NB_ECHEANCES AS DECIMAL (16, 2)) AS PART_PRIME, NUM,
PRM_MONTANT - (SELECT MAX(NUM) * ( CAST(PRM_MONTANT / PRN_NB_ECHEANCES AS DECIMAL (16, 2)) )
FROM   T_PRIME_PRM
INNER JOIN T_NUM
ON NUM BETWEEN 1 AND PRN_NB_ECHEANCES
WHERE  P.PRM_EMPLOYE = PRM_EMPLOYE
GROUP  BY PRM_MONTANT, PRN_NB_ECHEANCES) as ECART
FROM   T_PRIME_PRM P
INNER JOIN T_NUM
ON NUM BETWEEN 1 AND PRN_NB_ECHEANCES) AS T
WHERE  PRM_EMPLOYE = P1.PRM_EMPLOYE
AND PART_PRIME = P1.PART_PRIME
GROUP  BY PART_PRIME, PRM_EMPLOYE, ECART
HAVING MAX(NUM) = P1.NUM), 0) AS PART_PRIME_EXACTE
FROM   (SELECT PRM_EMPLOYE, CAST(PRM_MONTANT / PRN_NB_ECHEANCES AS DECIMAL (16, 2)) AS PART_PRIME, NUM,
PRM_MONTANT - (SELECT MAX(NUM) * ( CAST(PRM_MONTANT / PRN_NB_ECHEANCES AS DECIMAL (16, 2)) )
FROM   T_PRIME_PRM
INNER JOIN T_NUM
ON NUM BETWEEN 1 AND PRN_NB_ECHEANCES
WHERE  P.PRM_EMPLOYE = PRM_EMPLOYE
GROUP  BY PRM_MONTANT, PRN_NB_ECHEANCE id=""S) as ECART
FROM   T_PRIME_PRM P
INNER JOIN T_NUM
ON NUM BETWEEN 1 AND PRN_NB_ECH id=""EANCES) P1 ;

IV-D - Solution au problème n° 33 - affectations comptables

Une première idée est de réaliser l'affectation en réalisant  un rapprochement entre compte et "masque" de compte à l'aide de l'opérateur LIKE. C'est d'ailleurs pour cela que j'ai remplacé les ? par des _ ...

SELECT CPT_COMPTE, CPT_ANA, CPT_MONTANT, AFC_AFFECT, AFC_COMPTE, AFC_ANA
FROM   T_COMPTE_CPT C
INNER JOIN T_AFFECTATION_AFC A
ON     CPT_COMPTE LIKE AFC_COMPTE
AND CPT_ANA    LIKE AFC_ANA
ORDER BY 1, 2 ;

CPT_COMPTE CPT_ANA CPT_MONTANT        AFC_AFFECT AFC_COMPTE AFC_ANA
---------- ------- ------------------ ---------- ---------- -------
612000     12345   130.40             T124       6_____     _____
612000     12345   130.40             T125       612___     _____   -->(Regle 612??? ?????)

612000     26045   260.50             T125       612___     _____   -->(Règle 612??? ?????)
612000     26045   260.50             T124       6_____     _____
612000     26045   260.50             T033       6_____     26___

612101     33556   330.50             T028       6_____     __5__
612101     33556   330.50             T124       6_____     _____
612101     33556   330.50             T125       612___     _____
612101     33556   330.50             T126       612___     __5__   -->(Règle 612??? ??5??)

654000     26346   33.55              T124       6_____     _____
654000     26346   33.55              T033       6_____     26___   -->(Règle 6????? 26???)

654102     28333   180.10             T124       6_____     _____   -->(Règle 6????? ?????)

Comme nous le constatons, cette méthode nous donne différentes possibilités d'affectation des lignes comptables aux comptes. Il faut donc trouver une astuce pour faire en sorte que l'affectation soit la plus complète possible.

Demandons nous quelle est la règle sous-jacente au terme "meilleur" ?

Visiblement il s'agit de considérer que les chiffres les plus à gauche sont plus importants que les chiffres les plus à droite.

Dans ce cas, il faut trouver un moyen de valuer la complétude du masque de compte.

C'est généralement par le calcul d'un "poids" que l'on s'en tire. Un poids, c'est un facteur, un coefficient qui indique que telle information est plus sûre que telle autre.

Dans notre cas nous pouvons donner un poids à l'aide de puissances de 2. Si le caractère est présent alors la puissance du rang du caractère est prise en compte. Sinon, c'est un zéro. Finalement on fait la somme de toutes ces puissances afin de donner le poids globale du masque de compte.

Ainsi pour : 612_ _ _     _ _5_ _, le poids sera le suivant :

rang   2^rang-1   caractère   prise en compte    total
------ ---------- ----------- ------------------ ----------
11     1024       6           oui                1024
10     512        1           oui                512
9      256        2           oui                256
8      128        _           non                0
7      64         _           non                0
6      32         _           non                0
5      16         _           non                0
4      8          _           non                0
3      4          5           oui                4
2      2          _           non                0
1      1          _           non                0
===========
TOTAL                                            1796

On peut donc calculer chaque poids de chaque masque et ajouter cela à la table T_AFFECTATION_AFC en lui adjoignant une nouvelle colonne :

ALTER TABLE T_AFFECTATION_AFC
ADD AFC_PRIORITE SMALLINT;

Il faut ensuite mettre à jour ligne à ligne la table des affectations, comme ceci :

UPDATE T_AFFECTATION_AFC SET AFC_PRIORITE = 1024 WHERE AFC_AFFECT = 'T124';
UPDATE T_AFFECTATION_AFC SET AFC_PRIORITE = 1028 WHERE AFC_AFFECT = 'T028';
UPDATE T_AFFECTATION_AFC SET AFC_PRIORITE = 1048 WHERE AFC_AFFECT = 'T033';
UPDATE T_AFFECTATION_AFC SET AFC_PRIORITE = 1792 WHERE AFC_AFFECT = 'T125';
UPDATE T_AFFECTATION_AFC SET AFC_PRIORITE = 1796 WHERE AFC_AFFECT = 'T126';

Bien entendu, certaines personnes vont dire que je triche puisque j'ajoute à la table d'affectation une colonne... Mais tous d'abord disons que si nous violons la règle, nous n'en violons pas l'esprit qui consiste à préserver l'intégrité de la base de données. Souvenez vous de la règle de Codd n°9, intitulée "Indépendance logique des données" : les applications et les programmes terminaux sont logiquement inaffectés, quand des changements de tous ordres, préservant les informations et qui ne leur portent théoriquement aucune atteinte, sont apportés aux tables de base (restructuration).  En particulier l'ajout d'une table, d'une vue ou d'une colonne à une table existante ne modifie en rien le comportement des applications qui utilisent cette base de données (ou tout du moins, cela devrait être vrai !).

Cependant et pour les puriste nous verrons en final comment respecter notre serment et montrerons une solution sans restructuration de l'existant ni ajout d'une fonction utilisateur.

Parce que tant qu'à violer nos propres règles, allons-y franchement en utilisant une UDF afin de faciliter le calculs des poids. Je vous la livre écrite pour MS SQL Server :

CREATE FUNCTION F_POIDS_COMPTE (@DATA VARCHAR(32))
RETURNS INTEGER
AS
BEGIN
IF @DATA IS NULL RETURN NULL;
DECLARE @OUT INT, @I INT;
SELECT @OUT = 0, @I = LEN(@DATA);
WHILE @I >= 1
BEGIN
IF SUBSTRING(@DATA, @I, 1) <> '_'
SET @OUT = @OUT + POWER(2, LEN(@DATA) - @I);
SET @I = @I - 1;
END;
RETURN @OUT;
END;

Dès lors et afin de départager nos différentes possibilité d'affectation, il ne s'agit plus que de retenir chaque ligne ayant le poids le plus fort, ce qui peut se traduire par :

SELECT CPT_COMPTE, CPT_ANA, CPT_MONTANT, AFC_AFFECT
FROM   T_COMPTE_CPT C
INNER JOIN T_AFFECTATION_AFC A
ON     CPT_COMPTE LIKE AFC_COMPTE
AND CPT_ANA    LIKE AFC_ANA
WHERE  AFC_PRIORITE = (SELECT MAX(AFC_PRIORITE)
FROM   T_COMPTE_CPT C2
INNER JOIN T_AFFECTATION_AFC A2
ON     CPT_COMPTE LIKE AFC_COMPTE
AND CPT_ANA    LIKE AFC_ANA
WHERE  C2.CPT_COMPTE = C.CPT_COMPTE
AND  C2.CPT_ANA = C.CPT_ANA
AND  C2.CPT_MONTANT = C.CPT_MONTANT);

Et qui donne le bon résultat :

CPT_COMPTE CPT_ANA CPT_MONTANT        AFC_AFFECT
---------- ------- ------------------ ----------
654102     28333   180.10             T124
654000     26346   33.55              T033
612101     33556   330.50             T126
612000     26045   260.50             T125
612000     12345   130.40             T125

Comment nous débarrasser de la colonne en sus dans la table des affectations ?

Simple, il suffit de rajouter une table avec toutes les données nécessaires... Cela ne nous est pas interdit !

CREATE TABLE T_AFFECTATION2_AFC
(AFC_COMPTE          CHAR(6),
AFC_ANA             CHAR(5),
AFC_AFFECT          CHAR(4),
AFC_PRIORITE        SMALLINT);

INSERT INTO T_AFFECTATION2_AFC VALUES ('6_____', '_____', 'T124', 1024);
INSERT INTO T_AFFECTATION2_AFC VALUES ('6_____', '__5__', 'T028', 1028);
INSERT INTO T_AFFECTATION2_AFC VALUES ('6_____', '26___', 'T033', 1048);
INSERT INTO T_AFFECTATION2_AFC VALUES ('612___', '_____', 'T125', 1792);
INSERT INTO T_AFFECTATION2_AFC VALUES ('612___', '__5__', 'T126', 1796);

Et au fait... comment calculer les poids sans utiliser une UDF ?

Avec une requête... tout simplement !

SELECT AFC_COMPTE, AFC_ANA, SUM(POID) AS POIDS
FROM   (SELECT AFC_COMPTE, AFC_ANA,
CASE
WHEN SUBSTRING(AFC_COMPTE+AFC_ANA, NUM, 1) = '_' THEN 0
ELSE POWER(2, LEN(AFC_COMPTE+AFC_ANA)- NUM)
END AS POID
FROM   T_AFFECTATION_AFC
INNER JOIN T_NUM
ON NUM BETWEEN 1 AND LEN(AFC_COMPTE+AFC_ANA)) T
GROUP BY AFC_COMPTE, AFC_ANA

Pour la mystérieuse table T_NUM qui apparait bizarrement ici, souvenez vous qu'elle apparait dans de nombreux exercices et de trouve définit comme suit :

CREATE TABLE T_NUM (NUM INT);

INSERT INTO T_NUM VALUES (1);
INSERT INTO T_NUM VALUES (2);
INSERT INTO T_NUM VALUES (3);
INSERT INTO T_NUM VALUES (4);
INSERT INTO T_NUM VALUES (5);
INSERT INTO T_NUM VALUES (6);
INSERT INTO T_NUM VALUES (7);
INSERT INTO T id=""_NUM VALUES (8);
INSERT INTO T_NUM VALUES (9);
INSERT INTO T_NUM VALUES (10);
INSERT INTO T_NUM VALUES (11);
INSERT INTO T_NUM id="" VALUES (12);
...

IV-E - Solution au problème n° 33 - précédents (ou suivants)

Commençons par numéroter les lignes de cette table en utilisant une inéqui auto jointure avec comptage :

SELECT T1.PSV, COUNT(*) AS N
FROM   T_PRECSUIV_PSV T1
INNER JOIN T_PRECSUIV_PSV T2
ON T1.PSV >= T2.PSV
GROUP  BY  T1.PSV

PSV  N
---- -----------
AAA  1
BBB  2
CCC  3
DDD  4

Dès lors nous savons que la précédente est celle ayant un comptage de -1 par rapport à N, la solution devient triviale :

SELECT T1.PSV, COUNT(*) AS N,  COUNT(*) - 1 AS PRECEDENT
FROM   T_PRECSUIV_PSV T1
INNER JOIN T_PRECSUIV_PSV T2
ON T1.PSV >= T2.PSV
GROUP  BY  T1.PSV

PSV  N           PRECEDENT
---- ----------- -----------
AAA  1           0
BBB  2           1
CCC  3           2
DDD  4           3

Sauf que la ligne n° 0 n'existant pas, il nous faut faire :

SELECT T1.PSV, COUNT(*) AS N,  NULLIF(COUNT(*) - 1, 0) AS PRECEDENT
FROM   T_PRECSUIV_PSV T1
INNER JOIN T_PRECSUIV_PSV T2
ON T1.PSV >= T2.PSV
GROUP  BY  T1.PSV

PSV  N           PRECEDENT
---- ----------- -----------
AAA  1           NULL
BBB  2           1
CCC  3           2
DDD  4           3

Ce qui est une bonne solution.

En définitive, la solution globale est fournie par la requête suivante :

SELECT PSV1 AS VALEUR_ACTUELLE, PSV2 AS VALEUR PRECEDENTE
FROM   (SELECT T1.PSV AS PSV1, COUNT(T1.PSV) AS N1
FROM   T_PRECSUIV_PSV T1
LEFT OUTER  JOIN T_PRECSUIV_PSV T2
ON T1.PSV >= T2.PSV
GROUP  BY  T1.PSV) AS T1
LEFT OUTER JOIN (SELECT T1.PSV AS PSV2, COUNT(T1.PSV) + 1 AS N2
FROM   T_PRECSUIV_PSV T1
LEFT OUTER  JOIN T_PRECSUIV_PSV T2
ON T1.PSV >= T2.PSV
GROUP  BY  T1.PSV) T2
ON T1. N1 = T2.N2

VALEUR_ACTUELLE VALEUR PRECEDENTE
--------------- -----------------
AAA             NULL
BBB             AAA
CCC             BBB
DDD             CCC

Mais cette solution est-elle suffisante ?

Par exemple, rajoutons à notre table les données suivantes :

INSERT INTO T_PRECSUIV_PSV VALUES ('BBB');
INSERT INTO T_PRECSUIV_PSV VALUES ('CCC');
INSERT INTO T_PRECSUIV_PSV VALUES ('CCC');
INSERT INTO T_PRECSUIV_PSV VALUES ('CCC');

Notre requête précédente donne :

VALEUR_ACTUELLE VALEUR_PRECEDENT
--------------- ----------------
AAA             NULL
BBB             NULL
CCC             NULL
DDD             NULL

Tentons une autre approche et essayons de chercher pour chaque ligne la valeur immédiatement inférieure.

Cela peut se traduire par :

SELECT DISTINCT PSV AS VALEUR_ACTUELLE,
(SELECT MAX(PSV)
FROM   T_PRECSUIV_PSV
WHERE  PSV < T.PSV) AS VALEUR_PRECEDENTE
FROM   T_PRECSUIV_PSV AS T ;

VALEUR_ACTUELLE VALEUR_PRECEDENTE
--------------- ---------------
AAA             NULL
BBB             AAA
CCC             BBB
DDD             CCC

Cette solution, apparemment simpliste gagne en force et en souplesse !

Bien entendu le chaînage avant/arrière (précédent/suivant) est facile à réaliser :

SELECT DISTINCT PSV AS VALEUR_ACTUELLE,
(SELECT MAX(PSV)
FROM   T_PRECSUIV_PSV
WHERE  PSV < T.PSV) AS VALEUR_PRECEDENTE,
(SELECT MIN(PSV)
FROM   T_PRECSUIV_PSV
WHERE  PSV > T.PSV) AS VALEUR_SUIVANTE
FROM   T_PRECSUIV_PSV AS T ;

VALEUR_ACTUELLE VALEUR_PRECEDENTE VALEUR_SUIVANTE
--------------- ----------------- ---------------
A id=""AA             NULL              BBB
BBB             AAA               CCC
CCC             BBB               DDD
DDD            id=""  CCC               NULL

IV-F - Solution au problème n° 35 - matchs et victoires

Obtenir le nombre de matches des joueurs relève d'une simple astuce de jointure pour le comptage. Une jointure conditionnelle avec un OR dans la condition de jointure. C'est ce que l'on appelle une non équi jointure :

SELECT JOR_NOM, COUNT(*) AS NB_MATCHS
FROM   T_MATCH_MCH M
INNER JOIN T_JOUEUR_JOR J
ON J.JOR_ID = M.JOR_ID1 OR J.JOR_ID = M.JOR_ID2
GROUP  BY JOR_NOM ;

JOR_NOM                          NB_MATCHS
-------------------------------- -----------
Camus                            2
Dupont                           3
Mercier                          3

Obtenir le nombre de victoire des joueurs relève d'une autre astuce de jointure presque aussi simple :

SELECT JOR_NOM, COUNT(*) AS NB_VICTOIRE
FROM   T_MATCH_MCH M
INNER JOIN T_JOUEUR_JOR J
ON J.JOR_ID = CASE
WHEN MCH_SCORE_JOUEUR1 > MCH_SCORE_JOUEUR2 THEN M.JOR_ID1
WHEN MCH_SCORE_JOUEUR2 > MCH_SCORE_JOUEUR1 THEN M.JOR_ID2
END
GROUP  BY JOR_NOM ;

JOR_NOM                          NB_VICTOIRE
-------------------------------- -----------
Camus                            2
Dupont                           1
Mercier                          1

En fait la touche finale est bien plus simple qu'il n'y parait, car il suffit de mettre ces deux précédentes requêtes en tant que table dérivées dans la clause FROM d'une super requête et de les joindre sur le nom du joueur.

Mais comme il peut se trouver des homonymes, nous allons ajouter à nos deux requête l'identifiant du joueur et réaliser la jointure dessus, comme ceci :

SELECT T1.JOR_ID, T1.JOR_NOM, NB_MATCHS, NB_VICTOIRE
FROM   (SELECT JOR_ID, JOR_NOM, COUNT(*) AS NB_MATCHS
FROM   T_MATCH_MCH M
INNER JOIN T_JOUEUR_JOR J
ON J.JOR_ID = M.JOR_ID1 OR J.JOR_ID = M.JOR_ID2
GROUP  BY JOR_ID, JOR_NOM) AS T1
LEFT OUTER JOIN (SELECT JOR_ID, JOR_NOM, COUNT(*) AS NB_VICTOIRE
FROM   T_MATCH_MCH M
INNER JOIN T_JOUEUR_JOR J
ON J.JOR_ID = CASE
WHEN MCH_SCORE_JOUEUR1 > MCH_SCORE_JOUEUR2 THEN M.JOR_ID1
WHEN MCH_SCORE_JOUEUR2 > MCH_SCORE_JOUEUR1 THEN M.JOR_ID2
END
GROUP  BY JOR_ID, JOR_NOM) AS T2
ON T1.JOR_ID = T2.JOR_ID

JOR_ID      JOR_NOM                          NB_MATCHS   NB_VICTOIRE
----------- -------------------------------- ----------- -----------
2           Camus                            2           2
1           Dupont                           3           1
3           Mercier                          3           1

id="" Cependant, je vous laisse deviner pourquoi j'ai préféré une jointure externe gauche à une jointure id="" interne !

IV-G - Solution au problème n° 36 - tri bâtard

Une idée serait de faire un transtypage en entier et de forcer la clause de tri ORDER BY à opérer sur cette transformation, comme ceci :

SELECT *
FROM   T_PARCELLE_PCL
ORDER  BY CAST(PCL_NUM AS INTEGER) ;

Cependant, cette requête part en erreur du fait de la présence de caractères non numériques...

En fait, l'astuce consiste à effectuer un double tri :

- premièrement sur la longueur de la donnée ;

- deuxièmement sur la donnée elle même.

SELECT *
FROM   T_PARCELLE_PCL
ORDER  BY CHARACTER_LENGTH(PCL_NUM), PCL_NUM ;

PCL_NUM
id=""----------------
1
2
3
11
21
22
111
221
28A
28B

Et le id="" tout est joué !

IV-H - Solution au problème n° 37 - vote contraint

Un savant calcul combinant la multiplication et la dénullification est, semble-t-il, la chose la plus courte possible.

ALTER TABLE T_SCHNORKELZIG_SKZ ADD CONSTRAINT CK
CHECK((COALESCE(SKZ_MARC+1,1)*COALESCE(SKZ_PAUL+1,1)*COALESCE(SKZ_JEAN+1,1)*COALESCE(SKZ_LEON+1,1))IN(2,6,24,120))

Remarquez que dans le IN figurent des nombres issus de la fonction factorielle... Pour y parvenir et comme la multiplication par 1 ne change pas les données de départ, on ajoute 1 à la valeur du vote et on met 1 en cas de NULL. Ainsi, les combinaisons possibles sont :

2  * 3 * 4 * 5 = 120
2  * 3 * 4 = 24
2  * 3 =  6
2 = 2

Notez que la longueur de la clause à partir du mot CHECK fait 114 caractères... id="" Avez-vous fait moins ?

IV-I - Solution au problème n° 38 - propriété bien gardée

La solution à ce problème fait partie des requêtes les plus complexes à élaborer. En général les requêtes de manipulation du temps et en particulier celle portant sur des intervalles (durées); sont très difficile à mettre au point.

Voici cependant la décomposition de cette requête. Nous allons d'abord voire une solution globale sous forme de CTE puis nous allons la décortiquer petit à petit pour en comprendre son mécanisme.

IV-I-1 - Étape préliminaire

Tout d'abord il nous faut rajouter quelque information à notre ensemble de données : en effet nous devons borner l'intervalle de traitement de nos données entre le 1er septembre 2008 à 0h et le 7 septembre 2008 à 23h59'59". Pour cela nous devons soit couper les périodes chevauchant ces limites, soit si aucune période ne les chevauche, les créer de toute pièce avec une durée nulle.

Rajouter à l'ensemble de départ deux périodes fictives permettant de borner l'intervalle souhaité :

CREATE TABLE T_BORNE_BRN (DEBUT DATETIME, FIN DATETIME)
INSERT INTO  T_BORNE_BRN VALUES ('20080901 00:00:00', '20080901 00:00:00')
INSERT INTO  T_BORNE_BRN VALUES ('20080907 23:59:59', '20080907 23:59:59')

La solution sous forme de CTE :

WITH
V_GDN
-- Les périodes nouvelles de gardiennage avec les périodes bornes recoupées sur la durée de l'intervalle à scruter
AS
(
SELECT CASE
WHEN GDN_DATEHEURE_DEBUT < (SELECT MIN(DEBUT) FROM T_BORNE_BRN) THEN (SELECT MIN(DEBUT) FROM T_BORNE_BRN)
ELSE GDN_DATEHEURE_DEBUT
END AS GDN_DATEHEURE_DEBUT,
CASE
WHEN GDN_DATEHEURE_FIN > (SELECT MAX(DEBUT) FROM T_BORNE_BRN) THEN (SELECT MAX(DEBUT) FROM T_BORNE_BRN)
ELSE GDN_DATEHEURE_FIN
END AS GDN_DATEHEURE_FIN
FROM   dbo.T_GARDIENNAGE_GDN
UNION  ALL
SELECT DEBUT, FIN
FROM   T_BORNE_BRN
),
-- calcul des périodes agrégées de gardiennage, c'est à dire quelles sont les périodes pendant lesquelles il y a continuellement quelqu'un ?
GDT
AS
-- on combine toutes les heures de début avec toutes les heures de fin qui sont supérieures à celle de début
-- on réalise en fait une matrice "triangulaire"
(
SELECT DISTINCT GD1.GDN_DATEHEURE_DEBUT, GD2.GDN_DATEHEURE_FIN
FROM   V_GDN GD1
INNER JOIN V_GDN GD2
ON GD1.GDN_DATEHEURE_DEBUT <= GD2.GDN_DATEHEURE_FIN
-- élimination des trous entre GD1.GDN_DATEHEURE_DEBUT et GD2.GDN_DATEHEURE_FIN
AND NOT EXISTS
(SELECT *
FROM   V_GDN GD3
WHERE  GD3.GDN_DATEHEURE_DEBUT  > GD1.GDN_DATEHEURE_FIN
AND  GD3.GDN_DATEHEURE_DEBUT  <= GD2.GDN_DATEHEURE_DEBUT
AND  NOT EXISTS
(SELECT *
FROM   V_GDN GD4
WHERE  GD4.GDN_DATEHEURE_DEBUT  <  GD3.GDN_DATEHEURE_DEBUT
AND  GD4.GDN_DATEHEURE_FIN    >= GD3.GDN_DATEHEURE_DEBUT
)
)
-- élimination des sous périodes incluse dans les périodes de base
AND NOT EXISTS
(SELECT *
FROM   V_GDN GD5
WHERE (    GD5.GDN_DATEHEURE_DEBUT <  GD1.GDN_DATEHEURE_DEBUT
AND GD5.GDN_DATEHEURE_FIN   >= GD1.GDN_DATEHEURE_DEBUT )
OR (    GD5.GDN_DATEHEURE_DEBUT <= GD2.GDN_DATEHEURE_FIN
AND GD5.GDN_DATEHEURE_FIN   >  GD2.GDN_DATEHEURE_FIN   )
)
)
-- il faut maintenant inverser le problème en trouvant les périodes ou il n'y a personne !
SELECT T1.GDN_DATEHEURE_FIN AS GDN_DATEHEURE_DEBUT,
MIN(T2.GDN_DATEHEURE_DEBUT) AS GDN_DATEHEURE_FIN
FROM   GDT T1
INNER JOIN GDT T2
ON  T2.GDN_DATEHEURE_DEBUT > T1.GDN_DATEHEURE_FIN
GROUP BY T1.GDN_DATEHEURE_FIN

Comment cette requête fonctionne-t-elle ? Nous allons la décomposer étape par étape et comparer à chaque étape les lignes originelles avec celles calculées...

IV-I-2 - Étape 1 : bornage des tranches sur la période à scruter

CREATE VIEW V_GDN
AS
SELECT CASE
WHEN GDN_DATEHEURE_DEBUT < (SELECT MIN(DEBUT) FROM T_BORNE_BRN) THEN (SELECT MIN(DEBUT) FROM T_BORNE_BRN)
ELSE GDN_DATEHEURE_DEBUT
END AS GDN_DATEHEURE_DEBUT,
CASE
WHEN GDN_DATEHEURE_FIN > (SELECT MAX(DEBUT) FROM T_BORNE_BRN) THEN (SELECT MAX(DEBUT) FROM T_BORNE_BRN)
ELSE GDN_DATEHEURE_FIN
END AS GDN_DATEHEURE_FIN
FROM   dbo.T_GARDIENNAGE_GDN
UNION  ALL
SELECT DEBUT, FIN
FROM   T_BORNE_BRN

-- requête de comparaison des tranches existantes avec les nouvelles tranches "recadrées" suite au bornage sur période à scruter
SELECT *
FROM
(
SELECT DISTINCT
T1.GDN_DATEHEURE_DEBUT AS DEBUT_GARDIENNAGE,
T1.GDN_DATEHEURE_FIN AS FIN_GARDIENNAGE,
T2.GDN_DATEHEURE_DEBUT AS DEBUT_NOUVEAU,
T2.GDN_DATEHEURE_FIN AS FIN_NOUVEAU
FROM   T_GARDIENNAGE_GDN T1
FULL OUTER JOIN V_GDN T2
ON T1.GDN_DATEHEURE_DEBUT = T2.GDN_DATEHEURE_DEBUT
AND T1.GDN_DATEHEURE_FIN = T2.GDN_DATEHEURE_FIN
) AS T
ORDER  BY COALESCE(DEBUT_GARDIENNAGE,  DEBUT_NOUVEAU),
COALESCE(FIN_GARDIENNAGE, FIN_NOUVEAU)

DEBUT_GARDIENNAGE FIN_GARDIENNAGE  DEBUT_NOUVEAU    FIN_NOUVEAU
----------------- ---------------- ---------------- ----------------
NULL              NULL             2008-09-01 00:00 2008-09-01 00:00  --> tranche ajoutée en début
2008-09-01 07:30  2008-09-01 14:30 2008-09-01 07:30 2008-09-01 14:30
2008-09-01 12:30  2008-09-02 00:00 2008-09-01 12:30 2008-09-02 00:00
2008-09-01 22:30  2008-09-02 08:00 2008-09-01 22:30 2008-09-02 08:00
2008-09-01 23:30  2008-09-02 11:00 2008-09-01 23:30 2008-09-02 11:00
2008-09-02 06:00  2008-09-02 17:30 2008-09-02 06:00 2008-09-02 17:30
2008-09-02 08:30  2008-09-02 16:00 2008-09-02 08:30 2008-09-02 16:00
2008-09-03 06:00  2008-09-03 17:30 2008-09-03 06:00 2008-09-03 17:30
2008-09-03 08:30  2008-09-03 16:00 2008-09-03 08:30 2008-09-03 16:00
2008-09-03 18:00  2008-09-04 04:30 2008-09-03 18:00 2008-09-04 04:30
2008-09-03 22:30  2008-09-04 10:00 2008-09-03 22:30 2008-09-04 10:00
2008-09-04 06:00  2008-09-04 17:30 2008-09-04 06:00 2008-09-04 17:30
2008-09-04 14:30  2008-09-05 08:30 2008-09-04 14:30 2008-09-05 08:30
2008-09-05 08:30  2008-09-05 21:30 2008-09-05 08:30 2008-09-05 21:30
2008-09-05 09:30  2008-09-05 12:00 2008-09-05 09:30 2008-09-05 12:00
2008-09-06 08:30  2008-09-06 13:30 2008-09-06 08:30 2008-09-06 13:30
2008-09-06 10:30  2008-09-06 21:00 2008-09-06 10:30 2008-09-06 21:00
2008-09-06 12:00  2008-09-06 22:30 2008-09-06 12:00 2008-09-06 22:30
2008-09-06 16:30  2008-09-07 10:30 2008-09-06 16:30 2008-09-07 10:30
2008-09-06 21:00  2008-09-07 10:30 2008-09-06 21:00 2008-09-07 10:30
2008-09-07 06:00  2008-09-07 12:30 2008-09-07 06:00 2008-09-07 12:30
2008-09-07 18:30  2008-09-08 06:30 2008-09-07 18:30 2008-09-07 23:59  --> tranche modifiée pour sa borne fin
NULL              NULL             2008-09-07 23:59 2008-09-07 23:59  --> tranche ajoutée en fin

IV-I-3 - Étape 2 : calcul de toutes les périodes possibles partant des périodes existantes

On met en relation toutes les fins avec tous les début à condition de respecter l'ordre chronologie que le début soit avant (ou égal : tranche de durée nulle) à la fin

CREATE VIEW GDT1
AS
-- on combine toutes les heures de début avec toutes les heures de fin qui sont supérieures à celle de début
-- on réalise en fait une matrice "triangulaire" (éclatement de périodes...)
SELECT DISTINCT GD1.GDN_DATEHEURE_DEBUT, GD2.GDN_DATEHEURE_FIN
FROM   V_GDN GD1
INNER JOIN V_GDN GD2
ON GD1.GDN_DATEHEURE_DEBUT <= GD2.GDN_DATEHEURE_FIN

Comparaison des tranches existantes avec les nouvelles tranches éclatées

SELECT *
FROM
(
SELECT DISTINCT
T1.GDN_DATEHEURE_DEBUT AS DEBUT_GARDIENNAGE,
T1.GDN_DATEHEURE_FIN AS FIN_GARDIENNAGE,
T2.GDN_DATEHEURE_DEBUT AS DEBUT_NOUVEAU,
T2.GDN_DATEHEURE_FIN AS FIN_NOUVEAU
FROM   T_GARDIENNAGE_GDN T1
FULL OUTER JOIN GDT1 T2
ON T1.GDN_DATEHEURE_DEBUT = T2.GDN_DATEHEURE_DEBUT
AND T1.GDN_DATEHEURE_FIN = T2.GDN_DATEHEURE_FIN
) AS T
ORDER  BY COALESCE(DEBUT_GARDIENNAGE,  DEBUT_NOUVEAU),
COALESCE(FIN_GARDIENNAGE, FIN_NOUVEAU) ;

DEBUT_GARDIENNAGE FIN_GARDIENNAGE  DEBUT_NOUVEAU    FIN_NOUVEAU
----------------- ---------------- ---------------- ----------------
NULL              NULL             2008-09-01 00:00 2008-09-01 00:00
NULL              NULL             2008-09-01 00:00 2008-09-01 14:30
NULL              NULL             2008-09-01 00:00 2008-09-02 00:00
NULL              NULL             2008-09-01 00:00 2008-09-02 08:00
NULL              NULL             2008-09-01 00:00 2008-09-02 11:00
NULL              NULL             2008-09-01 00:00 2008-09-02 16:00
NULL              NULL             2008-09-01 00:00 2008-09-02 17:30
NULL              NULL             2008-09-01 00:00 2008-09-03 16:00
NULL              NULL             2008-09-01 00:00 2008-09-03 17:30
NULL              NULL             2008-09-01 00:00 2008-09-04 04:30
NULL              NULL             2008-09-01 00:00 2008-09-04 10:00
NULL              NULL             2008-09-01 00:00 2008-09-04 17:30
NULL              NULL             2008-09-01 00:00 2008-09-05 08:30
NULL              NULL             2008-09-01 00:00 2008-09-05 12:00
NULL              NULL             2008-09-01 00:00 2008-09-05 21:30
NULL              NULL             2008-09-01 00:00 2008-09-06 13:30
NULL              NULL             2008-09-01 00:00 2008-09-06 21:00
NULL              NULL             2008-09-01 00:00 2008-09-06 22:30
NULL              NULL             2008-09-01 00:00 2008-09-07 10:30
NULL              NULL             2008-09-01 00:00 2008-09-07 12:30
NULL              NULL             2008-09-01 00:00 2008-09-07 23:59
2008-09-01 07:30  2008-09-01 14:30 2008-09-01 07:30 2008-09-01 14:30
NULL              NULL             2008-09-01 07:30 2008-09-02 00:00
NULL              NULL             2008-09-01 07:30 2008-09-02 08:00
NULL              NULL             2008-09-01 07:30 2008-09-02 11:00
NULL              NULL             2008-09-01 07:30 2008-09-02 16:00
NULL              NULL             2008-09-01 07:30 2008-09-02 17:30
NULL              NULL             2008-09-01 07:30 2008-09-03 16:00
NULL              NULL             2008-09-01 07:30 2008-09-03 17:30
NULL              NULL             2008-09-01 07:30 2008-09-04 04:30
NULL              NULL             2008-09-01 07:30 2008-09-04 10:00
NULL              NULL             2008-09-01 07:30 2008-09-04 17:30
NULL              NULL             2008-09-01 07:30 2008-09-05 08:30
NULL              NULL             2008-09-01 07:30 2008-09-05 12:00
NULL              NULL             2008-09-01 07:30 2008-09-05 21:30
NULL              NULL             2008-09-01 07:30 2008-09-06 13:30
NULL              NULL             2008-09-01 07:30 2008-09-06 21:00
NULL              NULL             2008-09-01 07:30 2008-09-06 22:30
NULL              NULL             2008-09-01 07:30 2008-09-07 10:30
NULL              NULL             2008-09-01 07:30 2008-09-07 12:30
NULL              NULL             2008-09-01 07:30 2008-09-07 23:59
NULL              NULL             2008-09-01 12:30 2008-09-01 14:30
2008-09-01 12:30  2008-09-02 00:00 2008-09-01 12:30 2008-09-02 00:00
NULL              NULL             2008-09-01 12:30 2008-09-02 08:00
NULL              NULL             2008-09-01 12:30 2008-09-02 11:00
NULL              NULL             2008-09-01 12:30 2008-09-02 16:00
NULL              NULL             2008-09-01 12:30 2008-09-02 17:30
NULL              NULL             2008-09-01 12:30 2008-09-03 16:00
NULL              NULL             2008-09-01 12:30 2008-09-03 17:30
NULL              NULL             2008-09-01 12:30 2008-09-04 04:30
NULL              NULL             2008-09-01 12:30 2008-09-04 10:00
NULL              NULL             2008-09-01 12:30 2008-09-04 17:30
NULL              NULL             2008-09-01 12:30 2008-09-05 08:30
NULL              NULL             2008-09-01 12:30 2008-09-05 12:00
NULL              NULL             2008-09-01 12:30 2008-09-05 21:30
NULL              NULL             2008-09-01 12:30 2008-09-06 13:30
NULL              NULL             2008-09-01 12:30 2008-09-06 21:00
NULL              NULL             2008-09-01 12:30 2008-09-06 22:30
NULL              NULL             2008-09-01 12:30 2008-09-07 10:30
NULL              NULL             2008-09-01 12:30 2008-09-07 12:30
NULL              NULL             2008-09-01 12:30 2008-09-07 23:59
NULL              NULL             2008-09-01 22:30 2008-09-02 00:00
2008-09-01 22:30  2008-09-02 08:00 2008-09-01 22:30 2008-09-02 08:00
NULL              NULL             2008-09-01 22:30 2008-09-02 11:00
NULL              NULL             2008-09-01 22:30 2008-09-02 16:00
NULL              NULL             2008-09-01 22:30 2008-09-02 17:30
NULL              NULL             2008-09-01 22:30 2008-09-03 16:00
NULL              NULL             2008-09-01 22:30 2008-09-03 17:30
NULL              NULL             2008-09-01 22:30 2008-09-04 04:30
NULL              NULL             2008-09-01 22:30 2008-09-04 10:00
NULL              NULL             2008-09-01 22:30 2008-09-04 17:30
NULL              NULL             2008-09-01 22:30 2008-09-05 08:30
NULL              NULL             2008-09-01 22:30 2008-09-05 12:00
NULL              NULL             2008-09-01 22:30 2008-09-05 21:30
NULL              NULL             2008-09-01 22:30 2008-09-06 13:30
NULL              NULL             2008-09-01 22:30 2008-09-06 21:00
NULL              NULL             2008-09-01 22:30 2008-09-06 22:30
NULL              NULL             2008-09-01 22:30 2008-09-07 10:30
NULL              NULL             2008-09-01 22:30 2008-09-07 12:30
NULL              NULL             2008-09-01 22:30 2008-09-07 23:59
NULL              NULL             2008-09-01 23:30 2008-09-02 00:00
NULL              NULL             2008-09-01 23:30 2008-09-02 08:00
2008-09-01 23:30  2008-09-02 11:00 2008-09-01 23:30 2008-09-02 11:00
NULL              NULL             2008-09-01 23:30 2008-09-02 16:00
NULL              NULL             2008-09-01 23:30 2008-09-02 17:30
NULL              NULL             2008-09-01 23:30 2008-09-03 16:00
NULL              NULL             2008-09-01 23:30 2008-09-03 17:30
NULL              NULL             2008-09-01 23:30 2008-09-04 04:30
NULL              NULL             2008-09-01 23:30 2008-09-04 10:00
NULL              NULL             2008-09-01 23:30 2008-09-04 17:30
NULL              NULL             2008-09-01 23:30 2008-09-05 08:30
NULL              NULL             2008-09-01 23:30 2008-09-05 12:00
NULL              NULL             2008-09-01 23:30 2008-09-05 21:30
NULL              NULL             2008-09-01 23:30 2008-09-06 13:30
NULL              NULL             2008-09-01 23:30 2008-09-06 21:00
NULL              NULL             2008-09-01 23:30 2008-09-06 22:30
NULL              NULL             2008-09-01 23:30 2008-09-07 10:30
NULL              NULL             2008-09-01 23:30 2008-09-07 12:30
NULL              NULL             2008-09-01 23:30 2008-09-07 23:59
NULL              NULL             2008-09-02 06:00 2008-09-02 08:00
NULL              NULL             2008-09-02 06:00 2008-09-02 11:00
NULL              NULL             2008-09-02 06:00 2008-09-02 16:00
2008-09-02 06:00  2008-09-02 17:30 2008-09-02 06:00 2008-09-02 17:30
NULL              NULL             2008-09-02 06:00 2008-09-03 16:00
NULL              NULL             2008-09-02 06:00 2008-09-03 17:30
NULL              NULL             2008-09-02 06:00 2008-09-04 04:30
NULL              NULL             2008-09-02 06:00 2008-09-04 10:00
NULL              NULL             2008-09-02 06:00 2008-09-04 17:30
NULL              NULL             2008-09-02 06:00 2008-09-05 08:30
NULL              NULL             2008-09-02 06:00 2008-09-05 12:00
NULL              NULL             2008-09-02 06:00 2008-09-05 21:30
NULL              NULL             2008-09-02 06:00 2008-09-06 13:30
NULL              NULL             2008-09-02 06:00 2008-09-06 21:00
NULL              NULL             2008-09-02 06:00 2008-09-06 22:30
NULL              NULL             2008-09-02 06:00 2008-09-07 10:30
NULL              NULL             2008-09-02 06:00 2008-09-07 12:30
NULL              NULL             2008-09-02 06:00 2008-09-07 23:59
NULL              NULL             2008-09-02 08:30 2008-09-02 11:00
2008-09-02 08:30  2008-09-02 16:00 2008-09-02 08:30 2008-09-02 16:00
NULL              NULL             2008-09-02 08:30 2008-09-02 17:30
NULL              NULL             2008-09-02 08:30 2008-09-03 16:00
NULL              NULL             2008-09-02 08:30 2008-09-03 17:30
NULL              NULL             2008-09-02 08:30 2008-09-04 04:30
NULL              NULL             2008-09-02 08:30 2008-09-04 10:00
NULL              NULL             2008-09-02 08:30 2008-09-04 17:30
NULL              NULL             2008-09-02 08:30 2008-09-05 08:30
NULL              NULL             2008-09-02 08:30 2008-09-05 12:00
NULL              NULL             2008-09-02 08:30 2008-09-05 21:30
NULL              NULL             2008-09-02 08:30 2008-09-06 13:30
NULL              NULL             2008-09-02 08:30 2008-09-06 21:00
NULL              NULL             2008-09-02 08:30 2008-09-06 22:30
NULL              NULL             2008-09-02 08:30 2008-09-07 10:30
NULL              NULL             2008-09-02 08:30 2008-09-07 12:30
NULL              NULL             2008-09-02 08:30 2008-09-07 23:59
NULL              NULL             2008-09-03 06:00 2008-09-03 16:00
2008-09-03 06:00  2008-09-03 17:30 2008-09-03 06:00 2008-09-03 17:30
NULL              NULL             2008-09-03 06:00 2008-09-04 04:30
NULL              NULL             2008-09-03 06:00 2008-09-04 10:00
NULL              NULL             2008-09-03 06:00 2008-09-04 17:30
NULL              NULL             2008-09-03 06:00 2008-09-05 08:30
NULL              NULL             2008-09-03 06:00 2008-09-05 12:00
NULL              NULL             2008-09-03 06:00 2008-09-05 21:30
NULL              NULL             2008-09-03 06:00 2008-09-06 13:30
NULL              NULL             2008-09-03 06:00 2008-09-06 21:00
NULL              NULL             2008-09-03 06:00 2008-09-06 22:30
NULL              NULL             2008-09-03 06:00 2008-09-07 10:30
NULL              NULL             2008-09-03 06:00 2008-09-07 12:30
NULL              NULL             2008-09-03 06:00 2008-09-07 23:59
2008-09-03 08:30  2008-09-03 16:00 2008-09-03 08:30 2008-09-03 16:00
NULL              NULL             2008-09-03 08:30 2008-09-03 17:30
NULL              NULL             2008-09-03 08:30 2008-09-04 04:30
NULL              NULL             2008-09-03 08:30 2008-09-04 10:00
NULL              NULL             2008-09-03 08:30 2008-09-04 17:30
NULL              NULL             2008-09-03 08:30 2008-09-05 08:30
NULL              NULL             2008-09-03 08:30 2008-09-05 12:00
NULL              NULL             2008-09-03 08:30 2008-09-05 21:30
NULL              NULL             2008-09-03 08:30 2008-09-06 13:30
NULL              NULL             2008-09-03 08:30 2008-09-06 21:00
NULL              NULL             2008-09-03 08:30 2008-09-06 22:30
NULL              NULL             2008-09-03 08:30 2008-09-07 10:30
NULL              NULL             2008-09-03 08:30 2008-09-07 12:30
NULL              NULL             2008-09-03 08:30 2008-09-07 23:59
2008-09-03 18:00  2008-09-04 04:30 2008-09-03 18:00 2008-09-04 04:30
NULL              NULL             2008-09-03 18:00 2008-09-04 10:00
NULL              NULL             2008-09-03 18:00 2008-09-04 17:30
NULL              NULL             2008-09-03 18:00 2008-09-05 08:30
NULL              NULL             2008-09-03 18:00 2008-09-05 12:00
NULL              NULL             2008-09-03 18:00 2008-09-05 21:30
NULL              NULL             2008-09-03 18:00 2008-09-06 13:30
NULL              NULL             2008-09-03 18:00 2008-09-06 21:00
NULL              NULL             2008-09-03 18:00 2008-09-06 22:30
NULL              NULL             2008-09-03 18:00 2008-09-07 10:30
NULL              NULL             2008-09-03 18:00 2008-09-07 12:30
NULL              NULL             2008-09-03 18:00 2008-09-07 23:59
NULL              NULL             2008-09-03 22:30 2008-09-04 04:30
2008-09-03 22:30  2008-09-04 10:00 2008-09-03 22:30 2008-09-04 10:00
NULL              NULL             2008-09-03 22:30 2008-09-04 17:30
NULL              NULL             2008-09-03 22:30 2008-09-05 08:30
NULL              NULL             2008-09-03 22:30 2008-09-05 12:00
NULL              NULL             2008-09-03 22:30 2008-09-05 21:30
NULL              NULL             2008-09-03 22:30 2008-09-06 13:30
NULL              NULL             2008-09-03 22:30 2008-09-06 21:00
NULL              NULL             2008-09-03 22:30 2008-09-06 22:30
NULL              NULL             2008-09-03 22:30 2008-09-07 10:30
NULL              NULL             2008-09-03 22:30 2008-09-07 12:30
NULL              NULL             2008-09-03 22:30 2008-09-07 23:59
NULL              NULL             2008-09-04 06:00 2008-09-04 10:00
2008-09-04 06:00  2008-09-04 17:30 2008-09-04 06:00 2008-09-04 17:30
NULL              NULL             2008-09-04 06:00 2008-09-05 08:30
NULL              NULL             2008-09-04 06:00 2008-09-05 12:00
NULL              NULL             2008-09-04 06:00 2008-09-05 21:30
NULL              NULL             2008-09-04 06:00 2008-09-06 13:30
NULL              NULL             2008-09-04 06:00 2008-09-06 21:00
NULL              NULL             2008-09-04 06:00 2008-09-06 22:30
NULL              NULL             2008-09-04 06:00 2008-09-07 10:30
NULL              NULL             2008-09-04 06:00 2008-09-07 12:30
NULL              NULL             2008-09-04 06:00 2008-09-07 23:59
NULL              NULL             2008-09-04 14:30 2008-09-04 17:30
2008-09-04 14:30  2008-09-05 08:30 2008-09-04 14:30 2008-09-05 08:30
NULL              NULL             2008-09-04 14:30 2008-09-05 12:00
NULL              NULL             2008-09-04 14:30 2008-09-05 21:30
NULL              NULL             2008-09-04 14:30 2008-09-06 13:30
NULL              NULL             2008-09-04 14:30 2008-09-06 21:00
NULL              NULL             2008-09-04 14:30 2008-09-06 22:30
NULL              NULL             2008-09-04 14:30 2008-09-07 10:30
NULL              NULL             2008-09-04 14:30 2008-09-07 12:30
NULL              NULL             2008-09-04 14:30 2008-09-07 23:59
NULL              NULL             2008-09-05 08:30 2008-09-05 08:30
NULL              NULL             2008-09-05 08:30 2008-09-05 12:00
2008-09-05 08:30  2008-09-05 21:30 2008-09-05 08:30 2008-09-05 21:30
NULL              NULL             2008-09-05 08:30 2008-09-06 13:30
NULL              NULL             2008-09-05 08:30 2008-09-06 21:00
NULL              NULL             2008-09-05 08:30 2008-09-06 22:30
NULL              NULL             2008-09-05 08:30 2008-09-07 10:30
NULL              NULL             2008-09-05 08:30 2008-09-07 12:30
NULL              NULL             2008-09-05 08:30 2008-09-07 23:59
2008-09-05 09:30  2008-09-05 12:00 2008-09-05 09:30 2008-09-05 12:00
NULL              NULL             2008-09-05 09:30 2008-09-05 21:30
NULL              NULL             2008-09-05 09:30 2008-09-06 13:30
NULL              NULL             2008-09-05 09:30 2008-09-06 21:00
NULL              NULL             2008-09-05 09:30 2008-09-06 22:30
NULL              NULL             2008-09-05 09:30 2008-09-07 10:30
NULL              NULL             2008-09-05 09:30 2008-09-07 12:30
NULL              NULL             2008-09-05 09:30 2008-09-07 23:59
2008-09-06 08:30  2008-09-06 13:30 2008-09-06 08:30 2008-09-06 13:30
NULL              NULL             2008-09-06 08:30 2008-09-06 21:00
NULL              NULL             2008-09-06 08:30 2008-09-06 22:30
NULL              NULL             2008-09-06 08:30 2008-09-07 10:30
NULL              NULL             2008-09-06 08:30 2008-09-07 12:30
NULL              NULL             2008-09-06 08:30 2008-09-07 23:59
NULL              NULL             2008-09-06 10:30 2008-09-06 13:30
2008-09-06 10:30  2008-09-06 21:00 2008-09-06 10:30 2008-09-06 21:00
NULL              NULL             2008-09-06 10:30 2008-09-06 22:30
NULL              NULL             2008-09-06 10:30 2008-09-07 10:30
NULL              NULL             2008-09-06 10:30 2008-09-07 12:30
NULL              NULL             2008-09-06 10:30 2008-09-07 23:59
NULL              NULL             2008-09-06 12:00 2008-09-06 13:30
NULL              NULL             2008-09-06 12:00 2008-09-06 21:00
2008-09-06 12:00  2008-09-06 22:30 2008-09-06 12:00 2008-09-06 22:30
NULL              NULL             2008-09-06 12:00 2008-09-07 10:30
NULL              NULL             2008-09-06 12:00 2008-09-07 12:30
NULL              NULL             2008-09-06 12:00 2008-09-07 23:59
NULL              NULL             2008-09-06 16:30 2008-09-06 21:00
NULL              NULL             2008-09-06 16:30 2008-09-06 22:30
2008-09-06 16:30  2008-09-07 10:30 2008-09-06 16:30 2008-09-07 10:30
NULL              NULL             2008-09-06 16:30 2008-09-07 12:30
NULL              NULL             2008-09-06 16:30 2008-09-07 23:59
NULL              NULL             2008-09-06 21:00 2008-09-06 21:00
NULL              NULL             2008-09-06 21:00 2008-09-06 22:30
2008-09-06 21:00  2008-09-07 10:30 2008-09-06 21:00 2008-09-07 10:30
NULL              NULL             2008-09-06 21:00 2008-09-07 12:30
NULL              NULL             2008-09-06 21:00 2008-09-07 23:59
NULL              NULL             2008-09-07 06:00 2008-09-07 10:30
2008-09-07 06:00  2008-09-07 12:30 2008-09-07 06:00 2008-09-07 12:30
NULL              NULL             2008-09-07 06:00 2008-09-07 23:59
NULL              NULL             2008-09-07 18:30 2008-09-07 23:59
2008-09-07 18:30  2008-09-08 06:30 NULL             NULL
NULL              NULL             2008-09-07 23:59 2008-09-07 23:59

38-D - Étape 3 : on supprime maintenant les périodes incluses dans les autres afin de ne laisser que des périodes non "redondantes"

CREATE VIEW GDT2
AS
-- on combine toutes les heures de début avec toutes les heures de fin qui sont supérieures à celle de début
-- on réalise en fait une matrice "triangulaire"
SELECT DISTINCT GD1.GDN_DATEHEURE_DEBUT, GD2.GDN_DATEHEURE_FIN
FROM   V_GDN GD1
INNER JOIN V_GDN GD2
ON GD1.GDN_DATEHEURE_DEBUT <= GD2.GDN_DATEHEURE_FIN

-- élimination des sous périodes incluse dans les périodes de base
AND NOT EXISTS
(SELECT *
FROM   V_GDN GD5
WHERE (    GD5.GDN_DATEHEURE_DEBUT <  GD1.GDN_DATEHEURE_DEBUT
AND GD5.GDN_DATEHEURE_FIN   >= GD1.GDN_DATEHEURE_DEBUT )
OR (    GD5.GDN_DATEHEURE_DEBUT <= GD2.GDN_DATEHEURE_FIN
AND GD5.GDN_DATEHEURE_FIN   >  GD2.GDN_DATEHEURE_FIN   )
);

Comparaison des tranches existantes avec les nouvelles tranches éclatées débarrassées de la redondance

SELECT *
FROM
(
SELECT DISTINCT
T1.GDN_DATEHEURE_DEBUT AS DEBUT_GARDIENNAGE,
T1.GDN_DATEHEURE_FIN AS FIN_GARDIENNAGE,
T2.GDN_DATEHEURE_DEBUT AS DEBUT_NOUVEAU,
T2.GDN_DATEHEURE_FIN AS FIN_NOUVEAU
FROM   T_GARDIENNAGE_GDN T1
FULL OUTER JOIN GDT2 T2
ON T1.GDN_DATEHEURE_DEBUT = T2.GDN_DATEHEURE_DEBUT
AND T1.GDN_DATEHEURE_FIN = T2.GDN_DATEHEURE_FIN
) AS T
ORDER  BY COALESCE(DEBUT_GARDIENNAGE,  DEBUT_NOUVEAU),
COALESCE(FIN_GARDIENNAGE, FIN_NOUVEAU)

DEBUT_GARDIENNAGE FIN_GARDIENNAGE  DEBUT_NOUVEAU    FIN_NOUVEAU
----------------- ---------------- ---------------- ----------------
NULL              NULL             2008-09-01 00:00 2008-09-01 00:00
NULL              NULL             2008-09-01 00:00 2008-09-02 17:30
NULL              NULL             2008-09-01 00:00 2008-09-03 17:30
NULL              NULL             2008-09-01 00:00 2008-09-05 21:30
NULL              NULL             2008-09-01 00:00 2008-09-07 12:30
NULL              NULL             2008-09-01 00:00 2008-09-07 23:59
2008-09-01 07:30  2008-09-01 14:30 NULL             NULL
NULL              NULL             2008-09-01 07:30 2008-09-02 17:30
NULL              NULL             2008-09-01 07:30 2008-09-03 17:30
NULL              NULL             2008-09-01 07:30 2008-09-05 21:30
NULL              NULL             2008-09-01 07:30 2008-09-07 12:30
NULL              NULL             2008-09-01 07:30 2008-09-07 23:59
2008-09-01 12:30  2008-09-02 00:00 NULL             NULL
2008-09-01 22:30  2008-09-02 08:00 NULL             NULL
2008-09-01 23:30  2008-09-02 11:00 NULL             NULL
2008-09-02 06:00  2008-09-02 17:30 NULL             NULL
2008-09-02 08:30  2008-09-02 16:00 NULL             NULL
2008-09-03 06:00  2008-09-03 17:30 2008-09-03 06:00 2008-09-03 17:30
NULL              NULL             2008-09-03 06:00 2008-09-05 21:30
NULL              NULL             2008-09-03 06:00 2008-09-07 12:30
NULL              NULL             2008-09-03 06:00 2008-09-07 23:59
2008-09-03 08:30  2008-09-03 16:00 NULL             NULL
2008-09-03 18:00  2008-09-04 04:30 NULL             NULL
NULL              NULL             2008-09-03 18:00 2008-09-05 21:30
NULL              NULL             2008-09-03 18:00 2008-09-07 12:30
NULL              NULL             2008-09-03 18:00 2008-09-07 23:59
2008-09-03 22:30  2008-09-04 10:00 NULL             NULL
2008-09-04 06:00  2008-09-04 17:30 NULL             NULL
2008-09-04 14:30  2008-09-05 08:30 NULL             NULL
2008-09-05 08:30  2008-09-05 21:30 NULL             NULL
2008-09-05 09:30  2008-09-05 12:00 NULL             NULL
2008-09-06 08:30  2008-09-06 13:30 NULL             NULL
NULL              NULL             2008-09-06 08:30 2008-09-07 12:30
NULL              NULL             2008-09-06 08:30 2008-09-07 23:59
2008-09-06 10:30  2008-09-06 21:00 NULL             NULL
2008-09-06 12:00  2008-09-06 22:30 NULL             NULL
2008-09-06 16:30  2008-09-07 10:30 NULL             NULL
2008-09-06 21:00  2008-09-07 10:30 NULL             NULL
2008-09-07 06:00  2008-09-07 12:30 NULL             NULL
NULL              NULL             2008-09-07 18:30 2008-09-07 23:59
2008-09-07 18:30  2008-09-08 06:30 NULL             NULL

38-E - Étape 4 : en sus de de débarrasser de la redondance, on se débarrase des tranches faisant des "trous" au sein d'une même période

CREATE VIEW GDT3
AS
-- on combine toutes les heures de début avec toutes les heures de fin qui sont supérieures à celle de début
-- on réalise en fait une matrice "triangulaire"
SELECT DISTINCT GD1.GDN_DATEHEURE_DEBUT, GD2.GDN_DATEHEURE_FIN
FROM   V_GDN GD1
INNER JOIN V_GDN GD2
ON GD1.GDN_DATEHEURE_DEBUT <= GD2.GDN_DATEHEURE_FIN

-- élimination des sous périodes incluse dans les périodes de base
AND NOT EXISTS
(SELECT *
FROM   V_GDN GD5
WHERE (    GD5.GDN_DATEHEURE_DEBUT <  GD1.GDN_DATEHEURE_DEBUT
AND GD5.GDN_DATEHEURE_FIN   >= GD1.GDN_DATEHEURE_DEBUT )
OR (    GD5.GDN_DATEHEURE_DEBUT <= GD2.GDN_DATEHEURE_FIN
AND GD5.GDN_DATEHEURE_FIN   >  GD2.GDN_DATEHEURE_FIN   )
)
-- élimination des trous entre GD1.GDN_DATEHEURE_DEBUT et GD2.GDN_DATEHEURE_FIN
AND NOT EXISTS
(SELECT *
FROM   V_GDN GD3
WHERE  GD3.GDN_DATEHEURE_DEBUT  > GD1.GDN_DATEHEURE_FIN
AND  GD3.GDN_DATEHEURE_DEBUT  <= GD2.GDN_DATEHEURE_DEBUT
AND  NOT EXISTS
(SELECT *
FROM   V_GDN GD4
WHERE  GD4.GDN_DATEHEURE_DEBUT  <  GD3.GDN_DATEHEURE_DEBUT
AND  GD4.GDN_DATEHEURE_FIN    >= GD3.GDN_DATEHEURE_DEBUT
)
)

Comparaison des tranches existantes avec les nouvelles tranches débarrassées de toutes les scories

SELECT *
FROM
(
SELECT DISTINCT
T1.GDN_DATEHEURE_DEBUT AS DEBUT_GARDIENNAGE,
T1.GDN_DATEHEURE_FIN AS FIN_GARDIENNAGE,
T2.GDN_DATEHEURE_DEBUT AS DEBUT_NOUVEAU,
T2.GDN_DATEHEURE_FIN AS FIN_NOUVEAU
FROM   T_GARDIENNAGE_GDN T1
FULL OUTER JOIN GDT3 T2
ON T1.GDN_DATEHEURE_DEBUT = T2.GDN_DATEHEURE_DEBUT
AND T1.GDN_DATEHEURE_FIN = T2.GDN_DATEHEURE_FIN
) AS T
ORDER  BY COALESCE(DEBUT_GARDIENNAGE,  DEBUT_NOUVEAU),
COALESCE(FIN_GARDIENNAGE, FIN_NOUVEAU)

DEBUT_GARDIENNAGE FIN_GARDIENNAGE  DEBUT_NOUVEAU    FIN_NOUVEAU
----------------- ---------------- ---------------- ----------------
NULL              NULL             2008-09-01 00:00 2008-09-01 00:00
2008-09-01 07:30  2008-09-01 14:30 NULL             NULL
NULL              NULL             2008-09-01 07:30 2008-09-02 17:30
2008-09-01 12:30  2008-09-02 00:00 NULL             NULL
2008-09-01 22:30  2008-09-02 08:00 NULL             NULL
2008-09-01 23:30  2008-09-02 11:00 NULL             NULL
2008-09-02 06:00  2008-09-02 17:30 NULL             NULL
2008-09-02 08:30  2008-09-02 16:00 NULL             NULL
2008-09-03 06:00  2008-09-03 17:30 2008-09-03 06:00 2008-09-03 17:30
2008-09-03 08:30  2008-09-03 16:00 NULL             NULL
2008-09-03 18:00  2008-09-04 04:30 NULL             NULL
NULL              NULL             2008-09-03 18:00 2008-09-05 21:30
2008-09-03 22:30  2008-09-04 10:00 NULL             NULL
2008-09-04 06:00  2008-09-04 17:30 NULL             NULL
2008-09-04 14:30  2008-09-05 08:30 NULL             NULL
2008-09-05 08:30  2008-09-05 21:30 NULL             NULL
2008-09-05 09:30  2008-09-05 12:00 NULL             NULL
2008-09-06 08:30  2008-09-06 13:30 NULL             NULL
NULL              NULL             2008-09-06 08:30 2008-09-07 12:30
2008-09-06 10:30  2008-09-06 21:00 NULL             NULL
2008-09-06 12:00  2008-09-06 22:30 NULL             NULL
2008-09-06 16:30  2008-09-07 10:30 NULL             NULL
2008-09-06 21:00  2008-09-07 10:30 NULL             NULL
2008-09-07 06:00  2008-09-07 12:30 NULL             NULL
NULL              NULL             2008-09-07 18:30 2008-09-07 23:59
2008-09-07 18:30  2008-09-08 06:30 NULL             NULL

Notez que les étapes 3 et 4 sont réalisées dans la même CTE.

38-F - Étape 5 (finale): recherche des intervalles inverses

Il faut maintenant inverser le problème en trouvant les périodes où il n'y a personne !

CREATE VIEW GDT4
AS
SELECT T1.GDN_DATEHEURE_FIN AS GDN_DATEHEURE_DEBUT,
MIN(T2.GDN_DATEHEURE_DEBUT) AS GDN_DATEHEURE_FIN
FROM   GDT3 T1
INNER JOIN GDT3 T2
ON  T2.GDN_DATEHEURE_DEBUT > T1.GDN_DATEHEURE_FIN
GROUP BY T1.GDN_DATEHEURE_FIN;

Comparaison des tranches existantes avec les tranches vides enfin calculées :

SELECT *
FROM
(
SELECT DISTINCT
T1.GDN_DATEHEURE_DEBUT AS DEBUT_GARDIENNAGE,
T1.GDN_DATEHEURE_FIN AS FIN_GARDIENNAGE,
T2.GDN_DATEHEURE_DEBUT AS DEBUT_NOUVEAU,
T2.GDN_DATEHEURE_FIN AS FIN_NOUVEAU
FROM   T_GARDIENNAGE_GDN T1
FULL OUTER JOIN GDT4 T2
ON T1.GDN_DATEHEURE_DEBUT = T2.GDN_DATEHEURE_DEBUT
AND T1.GDN_DATEHEURE_FIN = T2.GDN_DATEHEURE_FIN
) AS T
ORDER  BY COALESCE(DEBUT_GARDIENNAGE,  DEBUT_NOUVEAU),
COALESCE(FIN_GARDIENNAGE, FIN_NOUVEAU);

DEBUT_GARDIENNAGE FIN_GARDIENNAGE  DEBUT_NOUVEAU    FIN_NOUVEAU
----------------- ---------------- ---------------- ----------------
NULL              NULL             2008-09-01 00:00 2008-09-01 07:30
2008-09-01 07:30  2008-09-01 14:30 NULL             NULL
2008-09-01 12:30  2008-09-02 00:00 NULL             NULL
2008-09-01 22:30  2008-09-02 08:00 NULL             NULL
2008-09-01 23:30  2008-09-02 11:00 NULL             NULL
2008-09-02 06:00  2008-09-02 17:30 NULL             NULL
2008-09-02 08:30  2008-09-02 16:00 NULL             NULL
NULL              NULL             2008-09-02 17:30 2008-09-03 06:00
2008-09-03 06:00  2008-09-03 17:30 NULL             NULL
2008-09-03 08:30  2008-09-03 16:00 NULL             NULL
NULL              NULL             2008-09-03 17:30 2008-09-03 18:00
2008-09-03 18:00  2008-09-04 04:30 NULL             NULL
2008-09-03 22:30  2008-09-04 10:00 NULL             NULL
2008-09-04 06:00  2008-09-04 17:30 NULL             NULL
2008-09-04 14:30  2008-09-05 08:30 NULL             NULL
2008-09-05 08:30  2008-09-05 21:30 NULL             NULL
2008-09-05 09:30  2008-09-05 12:00 NULL             NULL
NULL              NULL             2008-09-05 21:30 2008-09-06 08:30
2008-09-06 08:30  2008-09-06 13:30 NULL             NULL
2008-09-06 10:30  2008-09-06 21:00 NULL             NULL
2008-09-06 12:00  2008-09-06 22:30 NULL             NULL
2008-09-06 16:30  2008-09-07 10:30 NULL             NULL
2008-09-06 21:00  2008-09-07 10:30 NULL             NULL
2008-09-07 06:00  2008-09-07 12:30 NULL             NULL
NULL              NULL             2008-09-07 12:30 2008-09-07 18:30
2008-09-07 18:30  2008-09-08 06:30 NULL             NULL

39 - Solution au problème n° 39 - abstract et mots clefs

On comprend bien que pour réaliser le comptage il faut que l'on arrive à une table telle que chaque mot ait été découpé et mis dans une colonne avec la référence du titre du papier.

La difficulté de ce découpage réside en deux problèmes :

39-A - Le découpage n'est pas itératif

En effet, si les données étaient telles que l'on aurait autant de caractère dièse (#) que de mot, la découpe serait simplifiée par le fait que trouver le début ou la fin du mot clef, consisterait à trouver la position de chaque caractères #. Or ce n'est pas le cas. Il convient donc d'agir en conséquence et faire deux traitements distincts.

Par exemple, trouver le premier mot clef de l'abstract revient à faire la requête suivante :

SELECT *, CASE
WHEN POSITION('#', PPR_ABSTRACT) > 0
THEN SUBSTRING(PPR_ABSTRACT FROM 1 FOR COALESCE(POSITION('#', PPR_ABSTRACT) - 1, 0))
ELSE PPR_ABSTRACT
END AS MOT_CLEF
FROM   T_PAPIER_PPR T ;

PPR_TITRE                            PPR_ABSTRACT                         MOT_CLEF1
------------------------------------ ------------------------------------ -------------
Les derniers roi de France           Histoire#Politique                   Histoire
De Gaulle, un héro de l'histoire     Histoire#Politique#Guerre            Histoire
Les deux guerres mondiales           Histoire#Guerre                      Histoire
Les années 50 en Europe              Histoire#Union européenne            Histoire
Les présidents de la république      Histoire#Politique                   Histoire
Histoire de France                   Histoire                             Histoire

39-B - Le découpage n'est pas "fini"

En effet, le nombre de mots clef dans l'abstract n'est pas limité. Ici dans notre exemple nous avons fait en sorte de ne pas avoir une colonne PPR_ABSTRACT dont la longueur dépasse 120 caractères, mais dans la réalité, les choses ne sont pas bornées. Il faut donc prévoir un découpage de l'abstract sur un nombre indéterminé de mots clefs.

La solution à ce problème consiste à passer par une table de nombre et de faire un produit cartésien pour assurer le découpage des mots clefs...

Créons comme à notre habitude une table des nombres pour ce faire :

CREATE TABLE T_NUM (NUM INTEGER);

INSERT INTO T_NUM VALUES (0);
INSERT INTO T_NUM VALUES (1);
INSERT INTO T_NUM VALUES (2);
INSERT INTO T_NUM VALUES (3);
INSERT INTO T_NUM VALUES (4);
INSERT INTO T_NUM VALUES (5);
INSERT INTO T_NUM VALUES (6);
INSERT INTO T_NUM VALUES (7);
INSERT INTO T_NUM VALUES (8);
INSERT INTO T_NUM VALUES (9);

INSERT INTO T_NUM
SELECT T1.NUM + 10 * T2.NUM + 100 * T3.NUM
FROM   T_NUM AS T1
CROSS JOIN T_NUM AS T2
CROSS JOIN T_NUM AS T3
WHERE  T1.NUM + 10 * T2.NUM + 100 * T3.NUM > 9 ;

Une voie pour parvenir à ce résultat est la requête suivante :

SELECT PPR_TITRE, PPR_ABSTRACT,
SUBSTRING(PPR_ABSTRACT, N.NUM + 1, LEN(PPR_ABSTRACT) - N.NUM + 1) AS DECOUPE
FROM   T_PAPIER_PPR T
CROSS JOIN T_NUM N
WHERE  SUBSTRING(PPR_ABSTRACT, N.NUM, 1) = '#' ;

PPR_TITRE                            PPR_ABSTRACT                 DECOUPE
------------------------------------ ---------------------------- --------------------------------------------
Les derniers roi de France           Histoire#Politique           Politique
De Gaulle, un héro de l'histoire     Histoire#Politique#Guerre    Politique#Guerre
De Gaulle, un héro de l'histoire     Histoire#Politique#Guerre    Guerre
Les deux guerres mondiales           Histoire#Guerre              Guerre
Les années 50 en Europe              Histoire#Union européenne    Union européenne
Les présidents de la république      Histoire#Politique           Politique

Il ne suffit plus que de nettoyer cette table en ôtant tout ce qui est situé après le caractères # dans la colonne DECOUPE de faire une union avec la précédente requête et enfin de compter les occurrences.

Voici donc la requête finale :

SELECT CAST(MOT_CLEF AS VARCHAR(16)) AS MOT_CLEF, COUNT(*) AS NBR_REPONSES
FROM   (SELECT PPR_TITRE, PPR_ABSTRACT, SUBSTRING(MOT_CLEF, 1,
COALESCE(NULLIF(CHARINDEX('#', MOT_CLEF), 0) - 1, LEN(MOT_CLEF))) AS MOT_CLEF
FROM   (SELECT *, CASE
WHEN CHARINDEX('#', PPR_ABSTRACT) > 0
THEN SUBSTRING(PPR_ABSTRACT, 1, COALESCE(CHARINDEX('#', PPR_ABSTRACT) - 1, 0))
ELSE PPR_ABSTRACT
END AS MOT_CLEF
FROM   T_PAPIER_PPR T
UNION
SELECT PPR_TITRE, PPR_ABSTRACT,
SUBSTRING(PPR_ABSTRACT, N.NUM + 1, LEN(PPR_ABSTRACT) - N.NUM + 1) AS DECOUPE
FROM   T_PAPIER_PPR T
CROSS JOIN T_NUM N
WHERE  SUBSTRING(PPR_ABSTRACT, N.NUM, 1) = '#') T) TT
GROUP BY MOT_CLEF ;

Une autre façon de faire est de rajouter un # en tête de l'abstract dès le départ :

Ajout du caractère # en tête :

SELECT PPR_TITRE, '#' + PPR_ABSTRACT AS PPR_ABSTRACT
FROM   T_PAPIER_PPR

PPR_TITRE                            PPR_ABSTRACT
------------------------------------ -------------------------------
Les derniers roi de France           #Histoire#Politique
De Gaulle, un héro de l'histoire     #Histoire#Politique#Guerre
Les deux guerres mondiales           #Histoire#Guerre
Les années 50 en Europe              #Histoire#Union européenne
Les présidents de la république      #Histoire#Politique
Histoire de France                   #Histoire

Découpe sur n caractères # :

SELECT PPR_TITRE, PPR_ABSTRACT,
SUBSTRING(PPR_ABSTRACT, N.NUM + 1, LEN(PPR_ABSTRACT) - N.NUM + 1) AS DECOUPE
FROM   (SELECT PPR_TITRE, '#' + PPR_ABSTRACT AS PPR_ABSTRACT
FROM   T_PAPIER_PPR) T
CROSS JOIN T_NUM N
WHERE  SUBSTRING(PPR_ABSTRACT, N.NUM, 1) = '#' ;

PPR_TITRE                            PPR_ABSTRACT                     DECOUPE
------------------------------------ ------------------------------- -----------------------------
Les derniers roi de France           #Histoire#Politique             Histoire#Politique
Les derniers roi de France           #Histoire#Politique             Politique
De Gaulle, un héro de l'histoire     #Histoire#Politique#Guerre      Histoire#Politique#Guerre
De Gaulle, un héro de l'histoire     #Histoire#Politique#Guerre      Politique#Guerre
De Gaulle, un héro de l'histoire     #Histoire#Politique#Guerre      Guerre
Les deux guerres mondiales           #Histoire#Guerre                Histoire#Guerre
Les deux guerres mondiales           #Histoire#Guerre                Guerre
Les années 50 en Europe              #Histoire#Union européenne      Histoire#Union européenne
Les années 50 en Europe              #Histoire#Union européenne      Union européenne
Les présidents de la république      #Histoire#Politique             Histoire#Politique
Les présidents de la république      #Histoire#Politique             Politique
Histoire de France                   #Histoire                       Histoire

Suppression de tout ce qui est après et avec le caractère # :

SELECT PPR_TITRE, PPR_ABSTRACT,
CASE
WHEN CHARINDEX('#', DECOUPE) > 0
THEN SUBSTRING(DECOUPE, 1, CHARINDEX('#', DECOUPE) - 1)
ELSE DECOUPE
END AS MOT_CLEF
FROM   (SELECT PPR_TITRE, PPR_ABSTRACT,
SUBSTRING(PPR_ABSTRACT, N.NUM + 1, LEN(PPR_ABSTRACT) - N.NUM + 1) AS DECOUPE
FROM   (SELECT PPR_TITRE, '#' + PPR_ABSTRACT AS PPR_ABSTRACT
FROM   T_PAPIER_PPR) T
CROSS JOIN T_NUM N
WHERE  SUBSTRING(PPR_ABSTRACT, N.NUM, 1) = '#') TT

PPR_TITRE                            PPR_ABSTRACT                   MOT_CLEF
------------------------------------ ------------------------------ --------------------
Les derniers roi de France           #Histoire#Politique            Histoire
Les derniers roi de France           #Histoire#Politique            Politique
De Gaulle, un héro de l'histoire     #Histoire#Politique#Guerre     Histoire
De Gaulle, un héro de l'histoire     #Histoire#Politique#Guerre     Politique
De Gaulle, un héro de l'histoire     #Histoire#Politique#Guerre     Guerre
Les deux guerres mondiales           #Histoire#Guerre               Histoire
Les deux guerres mondiales           #Histoire#Guerre               Guerre
Les années 50 en Europe              #Histoire#Union européenne     Histoire
Les années 50 en Europe              #Histoire#Union européenne     Union européenne
Les présidents de la république      #Histoire#Politique            Histoire
Les présidents de la république      #Histoire#Politique            Politique
Histoire de France                   #Histoire                      Histoire

Comptage final :

SELECT MOT_CLEF, COUNT(*)
FROM   (SELECT PPR_TITRE, PPR_ABSTRACT,
CASE
WHEN CHARINDEX('#', DECOUPE) > 0
THEN SUBSTRING(DECOUPE, 1, CHARINDEX('#', DECOUPE) - 1)
ELSE DECOUPE
END AS MOT_CLEF
FROM   (SELECT PPR_TITRE, PPR_ABSTRACT,
SUBSTRING(PPR_ABSTRACT, N.NUM + 1, LEN(PPR_ABSTRACT) - N.NUM + 1) AS DECOUPE
FROM   (SELECT PPR_TITRE, '#' + PPR_ABSTRACT AS PPR_ABSTRACT
FROM   T_PAPIER_PPR) T
CROSS JOIN T_NUM N
WHERE  SUBSTRING(PPR_ABSTRACT, N.NUM, 1) = '#') TT ) TTT
GROUP  BY MOT_CLEF

MOT_CLEF                   NOMBRE
-------------------------- -----------
Guerre                     2
Histoire                   6
Politique                  3
Union européenne           1