Problème requête SQL (HAVING COUNT / MAX)

Version imprimable

27/11/2014, 23h35
AuroreMu

Problème requête SQL (HAVING COUNT / MAX)
Bonsoir,

J'ai un petit problème concernant une requête, je n'arrive pas à la finir. Voici le problème : Quel(s) membre(s) cumule(nt) le plus de changement d’abonnement (nom, nombre de
changement) ?

Mon modèle relationnel :
ABONNER (CodeAbo*,NumM*, DateDebut, DateFin)
MEMBRE (NumM, NomM, PrenomM, AdresseM, VilleM, DateDeNaissanceM, NumS*)

Et voilà ce que j'ai essayé de faire pour la requête :
Code:

1 2 3 4 SELECT Count(ABONNER.NumM) AS nbchangement, NomM FROM ABONNER, MEMBRE WHERE ABONNER.NumM=MEMBRE.NumM GROUP BY ABONNER.NumM, NomM
le soucis c'est que ça affiche la liste de tous les membres avec le nombre de changement pour chacun. Pour afficher le membre qui a le plus de changement, il me semble qu'il faudrait faire un having count = select max (qqchose) mais je n'arrive pas à l'écrire.

Merci d'avance.

Sans connaître le SGBD utilisé ni sa version, je répondrai en utilisant les fonctions de regroupement fenêtrées qui permettent de résoudre cela très facilement :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
WITH    cnt
        AS  (   SELECT  abo.numm
                    ,   COUNT(*)    AS nbabo
                FROM    abonner abo
                GROUP BY abo.numm
            )
    ,   sel
        AS  (   SELECT  LAST_VALUE(cnt.numm) OVER (ORDER BY cnt.nbabo ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
                FROM    cnt
            )
SELECT  mbr.nomm
FROM    membre  mbr
    INNER JOIN
        sel
        ON  cnt.numm  = mbr.numm
;

Avec un SGBD qui ne connait pas ces fonctions ni l'usage des CTE, cela sera un peu plus complexe... mais à peine.

Bonsoir,

Pour compléter le propos d’al1_24.

Si le SGBD ne connaît ni les CTE (Common Table Expression) ni les fonctions sophistiquées, on peut procéder ainsi (méthode bourrin), en respectant la norme SQL:1992 (je détaille les composants de la requête finale (R4)) :

(R1) Comptage des changements d’abonnement par membre :

Code:

1
2
3
SELECT NumM, COUNT(*) AS Kount
FROM   ABONNER
GROUP BY NumM ;

Code:

1
2
3
4
5
6
7
NumM   Kount
----   -----
m01        3
m02        4
m03        2
m04        1
m05        4

Manifestement les membres m02 et m05 répondent à la question posée.

(R2) Recherche du plus grand nombre de changements d’abonnement :

Code:

1
2
3
4
5
6
SELECT MAX(Kount)
FROM   (
        SELECT COUNT(*) AS Kount
        FROM   ABONNER
        GROUP BY NumM
       ) AS x ;

Code:

1
2
3
MAX(Kount)
----------
         4

(R3) Recherche des membres pour lesquels le nombre de changements d’abonnement est égal à MAX(Kount). Pour cela on tricote les deux requêtes précédentes :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT NumM, Kount
FROM   (
        SELECT NumM, COUNT(*) AS Kount
        FROM   ABONNER
        GROUP BY NumM
       ) AS x
WHERE  Kount = (SELECT  MAX(Kount)
                FROM   (
                        SELECT NumM, COUNT(*) AS Kount
                        FROM   ABONNER
                        GROUP BY NumM
                       ) AS truc
               )
GROUP BY NumM ;

Code:

1
2
3
4
NumM    Kount
----    -----
m02         4
m05         4

(R4) Pour la requête finale (jointure avec la table MEMBRE) :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT NomM, Kount
FROM   (
        SELECT NumM, COUNT(*) AS Kount
        FROM   ABONNER
        GROUP BY NumM
       ) AS x
JOIN MEMBRE AS y ON x.NumM = y.NumM  
WHERE  Kount = (SELECT  MAX(Kount)
                FROM   (
                        SELECT NumM, COUNT(*) AS Kount
                        FROM   ABONNER
                        GROUP BY NumM
                       ) AS truc
               )
GROUP BY x.NumM
;

Code:

1
2
3
4
NomM     Kount
----     -----
Bernard      4 
Emile        4