ROW_NUMBER() dans MySQL

Question

Existe-t-il un moyen efficace de répliquer la fonction du serveur SQL dans MySQL ? ROW_NUMBER() ?

Par exemple :

SELECT 
    col1, col2, 
    ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY col3 DESC) AS intRow
FROM Table1

Je pourrais alors, par exemple, ajouter une condition pour limiter intRow à 1 pour obtenir une seule rangée avec la plus haute col3 pour chaque (col1, col2) paire.

Answer 1

Il n'existe pas de fonctionnalité de classement dans MySQL. Le plus proche que vous puissiez obtenir est d'utiliser une variable :

SELECT t.*, 
       @rownum := @rownum + 1 AS rank
  FROM YOUR_TABLE t, 
       (SELECT @rownum := 0) r

---

alors comment cela pourrait-il fonctionner dans mon cas ? J'aurais besoin de deux variables, une pour chaque col1 et col2 ? Col2 devrait être réinitialisée d'une manière ou d'une autre lorsque Col1 change ?

Oui. Si c'était Oracle, vous pourriez utiliser la fonction LEAD pour atteindre la valeur suivante. Heureusement, Quassnoi couvre la logique de ce que vous devez implémenter dans MySQL .

Answer 2

Je veux la ligne avec la col3 la plus élevée pour chaque paire (col1, col2).

C'est un maximum collectif L'une des questions SQL les plus fréquemment posées (car elle semble être facile, mais ne l'est pas vraiment).

J'opte souvent pour l'auto-jointure nulle :

SELECT t0.col3
FROM table AS t0
LEFT JOIN table AS t1 ON t0.col1=t1.col1 AND t0.col2=t1.col2 AND t1.col3>t0.col3
WHERE t1.col1 IS NULL;

"Obtenez les lignes de la table pour lesquelles aucune autre ligne avec col1,col2 correspondant n'a un col3 supérieur". (Vous remarquerez que cette solution et la plupart des autres solutions de maximisation par groupe renverront plusieurs lignes si plusieurs lignes ont les mêmes col1,col2,col3. Si c'est un problème, vous aurez peut-être besoin d'un post-traitement).

Answer 3

Je finis toujours par suivre ce schéma. Étant donné ce tableau :

+------+------+
|    i |    j |
+------+------+
|    1 |   11 |
|    1 |   12 |
|    1 |   13 |
|    2 |   21 |
|    2 |   22 |
|    2 |   23 |
|    3 |   31 |
|    3 |   32 |
|    3 |   33 |
|    4 |   14 |
+------+------+

Vous pouvez obtenir ce résultat :

+------+------+------------+
|    i |    j | row_number |
+------+------+------------+
|    1 |   11 |          1 |
|    1 |   12 |          2 |
|    1 |   13 |          3 |
|    2 |   21 |          1 |
|    2 |   22 |          2 |
|    2 |   23 |          3 |
|    3 |   31 |          1 |
|    3 |   32 |          2 |
|    3 |   33 |          3 |
|    4 |   14 |          1 |
+------+------+------------+

En exécutant cette requête, qui ne nécessite aucune variable définie :

SELECT a.i, a.j, count(*) as row_number FROM test a
JOIN test b ON a.i = b.i AND a.j >= b.j
GROUP BY a.i, a.j

J'espère que cela vous aidera !

Answer 4

SELECT 
    @i:=@i+1 AS iterator, 
    t.*
FROM 
    tablename AS t,
    (SELECT @i:=0) AS foo

Answer 5

Consultez cet article, il montre comment imiter la fonction SQL ROW_NUMBER() avec une partition by dans MySQL. J'ai rencontré ce même scénario dans une mise en œuvre de WordPress. J'avais besoin de ROW_NUMBER() et il n'y en avait pas.

http://www.explodybits.com/2011/11/mysql-row-number/

L'exemple de l'article utilise une seule partition par champ. Pour partitionner par des champs supplémentaires, vous pourriez faire quelque chose comme ceci :

  SELECT  @row_num := IF(@prev_value=concat_ws('',t.col1,t.col2),@row_num+1,1) AS RowNumber
         ,t.col1 
         ,t.col2
         ,t.Col3
         ,t.col4
         ,@prev_value := concat_ws('',t.col1,t.col2)
    FROM table1 t,
         (SELECT @row_num := 1) x,
         (SELECT @prev_value := '') y
   ORDER BY t.col1,t.col2,t.col3,t.col4 

L'utilisation de concat_ws gère les nullités. J'ai testé cela sur 3 champs utilisant un int, une date et un varchar. J'espère que cela vous aidera. Consultez l'article car il décompose et explique cette requête.