lundi 19 décembre 2011

SUM for the 5 best models in 5 best brands

As we can see in the solution:

Expression are evaluated in the column order in the select clause.

This is the same for the where clause.  An advice is to test first the expression that get more chance to return false at the head of the where conditions :   



  1. SET @BRAND=0;
  2. SET @ct=0;
  3. SELECT
  4.  *
  5. FROM
  6.  (SELECT
  7.    IF(@BRAND<>id_BRAND,@ct:=0 ,@ct:=@ct ) ,
  8.    @ct:=@ct+1 AS ct,   
  9.    @BRAND:=id_BRAND,
  10.    t2.*
  11.   FROM
  12.    (SELECT
  13.      st.ID_BRAND,
  14.      st.ID_MODEL,
  15.      SUM(COMPTE) AS total,
  16.      AVG(totalm)
  17.     FROM
  18.      STAT st JOIN
  19.      (SELECT
  20.        ID_BRAND,
  21.        SUM(COMPTE) AS totalm
  22.       FROM
  23.        STAT
  24.       GROUP BY
  25.        ID_BRAND
  26.       ORDER BY
  27.        totalm DESC
  28.       LIMIT 5
  29.      ) as t USING( ID_BRAND)
  30.     GROUP BY
  31.      st.ID_BRAND,
  32.      st.ID_MODEL
  33.     ORDER BY
  34.      st.ID_BRAND,
  35.      total DESC
  36.    ) AS t2
  37.  ) AS t3
  38. WHERE
  39.  ct <=5

Aucun commentaire:

Enregistrer un commentaire