Numéro de ligne() en MySQL

Question

Numéro de ligne() en MySQL

y a-t-il un bon moyen pour MySQL de répliquer la fonction de serveur SQL ROW_NUMBER() ?

par exemple:

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

alors je pourrais, par exemple, ajouter une condition pour limiter intRow à 1 pour obtenir une ligne simple avec la plus haute col3 pour chaque paire (col1, col2) .

230

greatest-n-per-group mysql rank row-number sql

demandé sur shA.t 2009-12-13 02:58:44

21 réponses

score 91 · Answer 1

je veux la rangée avec la plus haute col3 pour chaque paire (col1, col2).

C'est un groupwise maximum , l'une des questions SQL les plus fréquemment posées (car il semble que ce devrait être facile, mais en fait, ce n'est pas le cas).

j'ai souvent dodu pour un null-auto-jointure:

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;

"Obtenir les lignes dans la table, pour lesquelles aucune autre ligne avec une correspondance col1,col2 a un plus haut col3."(Vous remarquerez ceci et la plupart des autres solutions groupwise-maximum retourneront plusieurs lignes si plus d'une ligne a la même col1,col2,col3. Si c'est un problème, vous aurez peut-être besoin d'un post-traitement.)

score 185 · Answer 2

il n'y a pas de fonctionnalité de classement dans MySQL. Le plus proche que vous pouvez obtenir est d'utiliser une variable:

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

alors comment ça marcherait dans mon cas? J'ai besoin de deux variables, une pour col1 et une pour col2? Col2 aurait besoin d'être réinitialisé d'une façon ou d'une autre lorsque col1 a changé..?

Oui. Si C'était Oracle, vous pourriez utiliser la fonction LEAD pour atteindre la valeur maximale à la valeur suivante. Heureusement, Quassnoi couvre la logique de ce que vous avez besoin pour mettre en œuvre dans MySQL .

score 74 · Answer 3

je finis toujours par suivre ce modèle. Compte tenu de 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 n'a besoin d'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

Espère que ça aide!

score 55 · Answer 4

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

score 25 · Answer 5

regardez cet Article, il montre comment imiter SQL ROW_NUMBER() avec une partition by in MySQL. J'ai rencontré ce même scénario dans une implémentation WordPress. J'avais besoin de ROW_NUMBER() et il n'était pas là.

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

l'exemple dans l'article utilise une seule partition par champ. Pour cloisonner 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

utilisant concat_ws handles null's. J'ai testé ceci contre 3 champs en utilisant un int, une date et un varchar. Espérons que cette aide. Vérifiez l'article car il décompose cette requête et l'explique.

score 14 · Answer 6

je voterais aussi pour la solution de Mosty Mostacho avec des modifications mineures à son code de requête:

SELECT a.i, a.j, (
    SELECT count(*) from test b where a.j >= b.j AND a.i = b.i
) AS row_number FROM test a

Qui donnera le même 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 |
+------+------+------------+

pour la table:

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

avec la seule différence que la requête n'utilise pas JOIN et GROUP BY, en s'appuyant à la place sur select imbriqué.

score 14 · Answer 7

à partir de MySQL 8.0.0 et au-dessus, vous pouvez nativement utiliser des fonctions fenêtrées.

1.4 Quoi de neuf en MySQL 8.0 :

les fonctions de la Fenêtre.

MySQL supporte maintenant des fonctions de fenêtre qui, pour chaque ligne d'une requête, effectuent un calcul en utilisant des lignes liées à cette ligne. Il s'agit de fonctions telles que RANK(), LAG(), et NTILE(). En outre, plusieurs les fonctions agrégées existantes peuvent maintenant être utilisées comme des fonctions de fenêtre; par exemple, SUM() et AVG().

ROW_NUMBER () over_clause :

renvoie le nombre de la ligne courante dans sa partition. Les nombres de lignes vont de 1 au nombre de lignes de cloisons.

ordre par affecte l'ordre dans lequel les lignes sont numérotées. Sans ordre, la numérotation des lignes est indéterminée.

Démo:

CREATE TABLE Table1(
  id INT AUTO_INCREMENT PRIMARY KEY, col1 INT,col2 INT, col3 TEXT);

INSERT INTO Table1(col1, col2, col3)
VALUES (1,1,'a'),(1,1,'b'),(1,1,'c'),
       (2,1,'x'),(2,1,'y'),(2,2,'z');

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

DBFiddle Démo

score 11 · Answer 8

je définirais une fonction:

delimiter $$
DROP FUNCTION IF EXISTS `getFakeId`$$
CREATE FUNCTION `getFakeId`() RETURNS int(11)
    DETERMINISTIC
begin
return if(@fakeId, @fakeId:=@fakeId+1, @fakeId:=1);
end$$

ensuite, j'ai pu faire:

select getFakeId() as id, t.* from table t, (select @fakeId:=0) as t2;

Maintenant vous n'avez pas de sous-jeu, ce que vous ne pouvez pas avoir dans les vues.

score 8 · Answer 9

il n'y a pas de funtion comme rownum , row_num() dans MySQL mais le chemin autour est comme ci-dessous:

select 
      @s:=@s+1 serial_no, 
      tbl.* 
from my_table tbl, (select @s:=0) as s;

score 6 · Answer 10

requête pour row_number dans mysql

set @row_number=0;
select (@row_number := @row_number +1) as num,id,name from sbs

score 4 · Answer 11

la solution que j'ai trouvée pour travailler le mieux était d'utiliser un subquery comme celui-ci:

SELECT 
    col1, col2, 
    (
        SELECT COUNT(*) 
        FROM Table1
        WHERE col1 = t1.col1
        AND col2 = t1.col2
        AND col3 > t1.col3
    ) AS intRow
FROM Table1 t1

la PARTITION par colonnes vient d'être comparée à '=' et séparée par et. L'ordre Par colonnes serait comparé avec ' < 'ou'>', et séparé par OR.

j'ai trouvé cela très souple, même si il est un peu coûteux.

score 4 · Answer 12

la fonctionnalité rownumber ne peut pas être imitée. Vous obtiendrez peut-être les résultats que vous espérez, mais vous serez très probablement déçu à un moment donné. Voici ce que dit la documentation de mysql:

pour D'autres énoncés, tels que SELECT, vous pourriez obtenir les résultats que vous attendez, mais ce n'est pas garanti. Dans la déclaration suivante, vous pourriez penser que MySQL va évaluer @une première et ensuite faire une deuxième tâche: Sélectionnez @a, @a:=@a+1,...; Cependant, l' l'ordre d'évaluation des expressions impliquant des variables utilisateur n'est pas défini.

en ce qui Concerne, Georgi.

score 3 · Answer 13

MariaDB 10.2 implémente "Window Functions", incluant RANK (), ROW_NUMBER () et plusieurs autres choses:

https://mariadb.com/kb/en/mariadb/window-functions /

basé sur un entretien à Percona Live Ce mois-ci, ils sont raisonnablement bien optimisés.

la syntaxe est identique au code de la Question.

score 1 · Answer 14

un peu tard, mais peut aussi aider à quelqu'un qui cherche des réponses...

entre lignes/row_number exemple-requête récursive qui peut être utilisée dans N'importe quel SQL:

WITH data(row_num, some_val) AS 
(
 SELECT 1 row_num, 1 some_val FROM any_table --dual in Oracle
  UNION ALL
 SELECT row_num+1, some_val+row_num FROM data WHERE row_num < 20 -- any number
)
SELECT * FROM data
 WHERE row_num BETWEEN 5 AND 10
/

ROW_NUM    SOME_VAL
-------------------
5           11
6           16
7           22
8           29
9           37
10          46

score 1 · Answer 15

cela permet d'obtenir les mêmes fonctionnalités que ROW_NUMBER() et PARTITION BY dans MySQL

SELECT  @row_num := IF(@prev_value=GENDER,@row_num+1,1) AS RowNumber
       FirstName, 
       Age,
       Gender,
       @prev_value := GENDER
  FROM Person,
      (SELECT @row_num := 1) x,
      (SELECT @prev_value := '') y
  ORDER BY Gender, Age DESC

score 1 · Answer 16

également un peu en retard, mais aujourd'hui j'avais le même besoin, donc j'ai fait une recherche sur Google et enfin une approche générale simple trouvé ici dans L'article de Dave Pinal http://blog.sqlauthority.com/2014/03/09/mysql-reset-row-number-for-each-group-partition-by-row-number /

je voulais me concentrer sur la question originale de Paul (c'était aussi mon problème) donc je résume ma solution comme un exemple de travail.

nous voulons partager sur deux colonnes, je créerais une variable définie pendant l'itération pour identifier si un nouveau groupe a été lancé.

SELECT col1, col2, col3 FROM (
  SELECT col1, col2, col3,
         @n := CASE WHEN @v = MAKE_SET(3, col1, col2)
                    THEN @n + 1 -- if we are in the same group
                    ELSE 1 -- next group starts so we reset the counter
                END AS row_number,
         @v := MAKE_SET(3, col1, col2) -- we store the current value for next iteration
    FROM Table1, (SELECT @n := 0, @v := NULL) r -- helper table for iteration with startup values
   ORDER BY col1, col2, col3 DESC -- because we want the row with maximum value
) x WHERE row_number = 1 -- and here we select exactly the wanted row from each group

le 3 signifie au premier paramètre de MAKE_SET que je veux les deux valeurs dans L'ensemble (3=1/2). Bien sûr, si nous n'avons pas deux colonnes ou plus construisant les groupes, nous pouvons éliminer L'opération MAKE_SET. La construction est exactement la même. Cela fonctionne pour moi selon les besoins. Un grand merci à Pinal Dave pour sa démonstration claire.

score 1 · Answer 17

Je ne vois pas de réponse simple couvrant la "PARTITION par" partie donc voici la mienne:

SELECT
    *
FROM (
    select
        CASE WHEN @partitionBy_1 = l THEN @row_number:=@row_number+1 ELSE @row_number:=1 END AS i
        , @partitionBy_1:=l AS p
        , t.*
    from (
        select @row_number:=0,@partitionBy_1:=null
    ) as x
    cross join (
        select 1 as n, 'a' as l
        union all
        select 1 as n, 'b' as l    
        union all
        select 2 as n, 'b' as l    
        union all
        select 2 as n, 'a' as l
        union all
        select 3 as n, 'a' as l    
        union all    
        select 3 as n, 'b' as l    
    ) as t
    ORDER BY l, n
) AS X
where i > 1

L'ordre Par clause doit refléter votre besoin de ROW_NUMBER. Il y a donc déjà une limite claire: vous ne pouvez pas avoir plusieurs "émulation" de ROW_NUMBER de ce formulaire en même temps.
L'ordre de la "colonne calculée" questions . Si vous avez mysql calculer ces colonne dans un autre ordre, il pourrait ne pas fonctionner.

dans cet exemple simple, je n'en ai mis qu'un, mais vous pouvez avoir plusieurs "partitions par" pièces

    CASE WHEN @partitionBy_1 = part1 AND @partitionBy_2 = part2 [...] THEN @row_number:=@row_number+1 ELSE @row_number:=1 END AS i
    , @partitionBy_1:=part1 AS P1
    , @partitionBy_2:=part2 AS P2
    [...] 
FROM (
    SELECT @row_number:=0,@partitionBy_1:=null,@partitionBy_2:=null[...]
) as x

score 1 · Answer 18

cela pourrait aussi être une solution:

SET @row_number = 0;

SELECT 
    (@row_number:=@row_number + 1) AS num, firstName, lastName
FROM
    employees

score -1 · Answer 19

set @i = 1;  
INSERT INTO ARG_VALUE_LOOKUP(ARG_VALUE_LOOKUP_ID,ARGUMENT_NAME,VALUE,DESCRIPTION,UPDATE_TIMESTAMP,UPDATE_USER,VER_NBR,OBJ_ID) 
select @i:= @i+1 as ARG_VALUE_LOOKUP_ID,ARGUMENT_NAME,VALUE,DESCRIPTION,CURRENT_TIMESTAMP,'admin',1,UUID() 
FROM TEMP_ARG_VALUE_LOOKUP 
order by ARGUMENT_NAME;

score -1 · Answer 20

cela fonctionne parfaitement pour moi de créer RowNumber lorsque nous avons plus d'une colonne. Dans ce cas, en deux colonnes.

SELECT @row_num := IF(@prev_value= concat(`Fk_Business_Unit_Code`,`NetIQ_Job_Code`), @row_num+1, 1) AS RowNumber, 
    `Fk_Business_Unit_Code`,   
    `NetIQ_Job_Code`,  
    `Supervisor_Name`,  
    @prev_value := concat(`Fk_Business_Unit_Code`,`NetIQ_Job_Code`)  
FROM (SELECT DISTINCT `Fk_Business_Unit_Code`,`NetIQ_Job_Code`,`Supervisor_Name`         
      FROM Employee    
      ORDER BY `Fk_Business_Unit_Code`, `NetIQ_Job_Code`, `Supervisor_Name` DESC) z,  
(SELECT @row_num := 1) x,  
(SELECT @prev_value := '') y  
ORDER BY `Fk_Business_Unit_Code`, `NetIQ_Job_Code`,`Supervisor_Name` DESC

score -5 · Answer 21

SELECT 
    col1, col2, 
    count(*) as intRow
FROM Table1
GROUP BY col1,col2
ORDER BY col3 desc

Las etiquetas más populares

Numéro de ligne() en MySQL

21 réponses