3
novembre
2009
Colle SQL n°2
novembre
2009
Un article de SQLpro
Comment numéroter avec rupture, sans utiliser les fonctions de fenêtrage comme ROW_NUMBER() ?
Voici une table et son contenu :
CREATE TABLE T (Numero_Compte INT, Groupe CHAR(1), Montant DECIMAL(16,4));
INSERT INTO T VALUES (23441, 'A', 755.95655999999997)
INSERT INTO T VALUES (28972, 'A', 1293.1123)
INSERT INTO T VALUES (28973, 'A', 388.51556899999997)
INSERT INTO T VALUES (32091, 'A', 2875.866)
INSERT INTO T VALUES (34193, 'A', 4661.9679999999998)
INSERT INTO T VALUES (37237, 'S', 312.12299999999999)
INSERT INTO T VALUES (39156, 'S', 309.65827000000002)
INSERT INTO T VALUES (45230, 'S', 615.71899999999994)
INSERT INTO T VALUES (45841, 'S', 365.96730000000002)
INSERT INTO T VALUES (46531, 'S', 576.68309999999997)
INSERT INTO T VALUES (47181, 'S', 1450.2131999999999)
Comment faire en sorte d’obtenir une numérotation dans l’ordre des colonnes Numero_Compte et Montant avec une rupture sur Groupe ?
Et ainsi obtenir le résultat suivants :
Numero_Compte Groupe Montant N
------------- ------ ---------------- ------
34193 A 4661.9680 1
32091 A 2875.8660 2
28973 A 388.5156 3
28972 A 1293.1123 4
23441 A 755.9566 5
47181 S 1450.2132 1
46531 S 576.6831 2
45841 S 365.9673 3
45230 S 615.7190 4
39156 S 309.6583 5
37237 S 312.1230 6
???
—
Frédéric BROUARD, Spécialiste modélisation, bases de données, optimisation, langage SQL.
Le site sur le langage SQL et les S.G.B.D. relationnels : http://sqlpro.developpez.com/
Expert SQL Server http://www.sqlspot.com : audit, optimisation, tuning, formation
* * * * * Enseignant au CNAM PACA et à l’ISEN à Toulon * * * * *
Dernier post !
SELECT t1.*, COUNT(*) AS N <br />
FROM t t1 <br />
JOIN t t2 <br />
ON t1.Numero_Compte <= t2.Numero_Compte <br />
WHERE t1.Groupe = t2.Groupe <br />
GROUP BY t1.Numero_Compte, t1.Groupe, t1.Montant <br />
ORDER BY t1.Groupe ASC, t1.Numero_Compte DESC; <br />
Il manquait des colonnes dans le GROUP BY
SELECT t1.*, COUNT(*) AS N <br />
FROM t t1 <br />
JOIN t t2 <br />
ON t1.Numero_Compte
Plus propre
SELECT t1.Numero_Compte, t1.Groupe, t1.Montant, COUNT(*) AS N <br />
FROM t t1 <br />
<dd>JOIN t t2</dd> <br />
<dd><dd>ON t1.Numero_Compte <= t2.Numero_Compte</dd></dd> <br />
<dd><dd>AND t1.Groupe = t2.Groupe</dd></dd> <br />
GROUP BY t1.Numero_Compte <br />
ORDER BY t1.Groupe ASC, t1.Numero_Compte DESC; <br />
SELECT t1.Numero_Compte, t1.Groupe, t1.Montant, COUNT(*) AS N
FROM t t1 JOIN t t2 ON t1.Numero_Compte <= t2.Numero_Compte
WHERE t1.Groupe = t2.Groupe
GROUP BY t1.Numero_Compte
ORDER BY t1.Groupe ASC, t1.Numero_Compte DESC;
SELECT t1.Numero_Compte, t1.Groupe, t1.Montant, COUNT(*) AS N<br />
FROM t t1<br />
JOIN t t2<br />
ON t1.Numero_Compte
SELECT t1.Numero_Compte, t1.Groupe, t1.Montant, COUNT(*) AS N
FROM t t1
JOIN t t2
ON t1.Numero_Compte
select t1.*,(select COUNT(*) from t where t.groupe=t1.groupe and t1.montant
un rien archaique le mecanisme de post
select t1.*,(select COUNT(*) from t where t.groupe=t1.groupe and t1.montant
select t1.*,
(select COUNT(*) from t where t.groupe=t1.groupe and t1.montant
select t1.*,(select COUNT(*) from t where t.groupe=t1.groupe and t1.montant
FROM T t1 <br />
JOIN (SELECT * FROM T) t2 <br />
ON t1.Groupe = t2.Groupe <br />
AND ((t1.Numero_Compte < t2.Numero_Compte) <br />
OR (t1.Numero_Compte = t2.Numero_Compte AND t1.Montant
Il suffit de complexifier un tout petit peu la condition de jointure, pour lever le cas d’égalité des numéros de compte :
SELECT t1.*, COUNT(*) AS N </br>FROM T t1
JOIN (SELECT * FROM T) t2 ON t1.Groupe = t2.Groupe
AND ((t1.Numero_Compte < t2.Numero_Compte)
OR (t1.Numero_Compte = t2.Numero_Compte AND t1.Montant
Oui, mais c’est incomplet. Par exemple si je rajoute :
Alors cela ne marche plus….
J’attend votre correction ! (elle est basée sur le vectorisation des informations).
A +
Je viens de comprendre …
Pardon, je n’avais pas lu le « sans fonction de fenêtrage » …
Il suffit d’utiliser un RANK() OVER avec un PARTITION BY et un ORDER BY :
SELECT Numero_Compte, Groupe, Montant,
RANK() OVER (PARTITION BY Groupe ORDER BY Numero_Compte DESC, Montant)
FROM T