Solutions pour le petit jeu des requêtes SQL
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
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 ;
|
O
u 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
+ 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
|
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
+ 10 * TED.ENT_N
+ 100 * TEC.ENT_N
+ 1000 * TEM.ENT_N
FROM T_ENTIER_ENT TEU,
T_ENTIER_ENT TED,
T_ENTIER_ENT TEC,
T_ENTIER_ENT TEM
WHERE TEU.ENT_N
+ 10 * TED.ENT_N
+ 100 * TEC.ENT_N
+ 1000 * TEM.ENT_N > 9
|
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
CROSS JOIN T_ENTIER_ENT TED
CROSS JOIN T_ENTIER_ENT TEC
CROSS JOIN T_ENTIER_ENT TEM
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
CROSS JOIN T_ENTIER_ENT TED
CROSS JOIN T_ENTIER_ENT TEC
CROSS JOIN T_ENTIER_ENT TEM
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 ;
|
Copyright © SQLPro.
Aucune reproduction, même partielle, ne peut être faite
de ce site ni 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.
