Row_Number () in MySQL

Is er een leuke manier in MySQL om de SQL Server-functie te repliceren ROW_NUMBER()?

Bijvoorbeeld:

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

Dan kan ik bijvoorbeeld een voorwaarde toevoegen om intRownaar 1 om een ​​enkele rij te krijgen met de hoogste col3voor elke (col1, col2)paar.


Antwoord 1, Autoriteit 100%

Ik wil de rij met het hoogste Col3 voor elk (Col1, Col2) -paar.

Dat is een GroupWise Maximum , een van De meest gestelde SQL-vragen (omdat het lijkt alsof het gemakkelijk zou moeten zijn, maar eigenlijk is het niet).

Ik mollig vaak voor een null-self-join:

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;

“Haal de rijen in de tabel waarvoor geen andere rij met bijpassende col1, Col2 een hoger Col3 heeft.” (U zult opmerken dat deze en de meeste andere GroupWise-maximale oplossingen meerdere rijen retourneren als meer dan één rij dezelfde COL1, COL2, COL3 heeft. Als dat een probleem is, heeft u mogelijk wat nabewerking nodig.)


Antwoord 2, Autoriteit 192%

Er is geen rangorde-functionaliteit in MySQL. De dichtstbijzijnde die u kunt krijgen, is om een ​​variabele te gebruiken:

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

Dus hoe zou dat in mijn geval werken? Ik heb twee variabelen nodig, één voor elk van Col1 en Col2? Col2 zou op de een of andere manier moeten resetten wanneer Col1 veranderde ..?

Ja. Als het Oracle was, kunt u de hoofdfunctie gebruiken om op de volgende waarde te pieken. Gelukkig bedekt Quassnoi de logica voor wat u moet implementeren in MySQL .


Antwoord 3, Autoriteit 77%

Ik eindig altijd op dit patroon. Gezien deze tabel:

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

U kunt dit resultaat krijgen:

+------+------+------------+
|    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 |
+------+------+------------+

Door deze query te gebruiken, die geen variabele gedefinieerd heeft:

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

Ik hoop dat dat helpt!


Antwoord 4, Autoriteit 56%

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

Antwoord 5, Autoriteit 31%

Van MySQL 8.0.0en boven je native kon gebruiken windowed functies.

1.4 Wat is nieuw in MySQL 8.0 :

Window functies.

MySQL ondersteunt nu vensterfuncties dat voor elke rij van een query uitvoeren van een berekening met rijen betrekking tot die rij. Fungeert onder andere als RANK (), LAG () en NTILE (). Daarnaast een aantal bestaande totaalfuncties nu kan worden gebruikt als vensterfuncties; bijvoorbeeld SUM () en AVG ().

ROW_NUMBER () over_clause

Geeft het nummer van de huidige rij in zijn partitie. Rijen getallen variëren van 1 tot het aantal partitie rijen.

ORDER BY invloed op de volgorde waarin de rijen zijn genummerd. Zonder ORDER BY, rijnummering is onbepaald.

Demo:

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 Demo


Antwoord 6, Autoriteit 24%

Bekijk dit artikel, het laat zien hoe u SQL Row_Number () met een partitie door in MySQL mimicalt. Ik liep naar ditzelfde scenario in een implementatie van WordPress. Ik had Row_Number () nodig en het was er niet.

http://www.explodybits.com/2011/11/mysql -Row-nummer /

Het voorbeeld in het artikel gebruikt een enkele partitie per veld. Partitioneren door extra velden zou je zoiets kunnen doen:

 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 

Het gebruik van concat_ws handgrepen null’s. Ik heb dit getest tegen 3 velden met behulp van een int, datum en varchar. Ik hoop dat dit helpt. Bekijk het artikel omdat deze deze query breekt en legt het uit.


Antwoord 7, Autoriteit 16%

Ik zou ook stemmen op de oplossing van MAKEY MOSTACHO met kleine aanpassing aan zijn querycode:

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

die hetzelfde resultaat zal geven:

+------+------+------------+
|    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 |
+------+------+------------+

Voor de tabel:

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

Met het enige verschil dat de query geen lid en groep gebruikt, moet u in plaats daarvan op genesteld selecteren.


Antwoord 8, Autoriteit 10%

Ik zou een functie definiëren:

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

Dan kan ik doen:

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

Nu heb je geen subquery, die je niet in het meningen kunt hebben.


Antwoord 9, Autoriteit 9%

Query voor Row_Number in MySQL

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

Antwoord 10, Autoriteit 8%

Er is geen fondion zoals rownum, row_num()in MySQL, maar de manier waarop rond is:

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

Antwoord 11, Autoriteit 3%

De oplossing die ik vond het beste te werken, gebruikte een subquery als volgt:

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

De partitie per kolommen worden gewoon vergeleken met ‘=’ en gescheiden door en. De bestelling door kolommen zou worden vergeleken met ‘& lt;’ of ‘& GT;’, en gescheiden door or.

Ik heb dit gevonden om zeer flexibel te zijn, zelfs als het een beetje kostbaar is.


Antwoord 12, Autoriteit 3%

De Rowngoms-functionaliteit kan niet worden nagemakkelijkt. Misschien krijg je de resultaten die je verwacht, maar je zult waarschijnlijk in een fase teleurgesteld worden.
Dit is wat MySQL-documentatie zegt:

Voor andere verklaringen, zoals SELECT, kunt u de resultaten krijgen die u verwacht, maar dit is niet gegarandeerd. In de volgende verklaring zou u kunnen denken dat MySQL @A eerst zal evalueren en dan een opdracht afleggen:
Selecteer @A, @a: = @ A + 1, …;
De volgorde van evaluatie voor uitdrukkingen met behulp van gebruikersvariabelen is ongedefinieerd.

groeten,
Georgi.


Antwoord 13, Autoriteit 3%

MARIADB 10.2 implementeert “vensterfuncties”, inclusief rang (), rij_number () en verschillende andere dingen:

https://mariadb.com/kb/en/mariaDB/Window-functions /

Gebaseerd op een gesprek bij Percona live deze maand, zijn ze redelijk goed geoptimaliseerd.

De syntaxis is identiek aan de code in de vraag.


Antwoord 14, Autoriteit 3%

BELANGRIJK: Houd rekening met het upgraden naar MySQL 8+ en gebruik de gedefinieerde en gedocumenteerde Row_Number () -functie en Sloot Old-hacks vastgebonden aan een functie Beperkte oude versie van MySQL

Nu is hier een van die hacks:

De antwoorden hier die in-query-variabelen meestal / allemaal gebruiken, lijken het feit te negeren dat de documentatie (parafras) zegt:

Vertrouw niet op items in de selectielijst die wordt geëvalueerd in volgorde van boven naar beneden. Wijs geen variabelen toe in één Select item en gebruik ze in een andere

Als zodanig is er een risico dat ze het verkeerde antwoord zullen uitkijken, omdat ze meestal een

doen

select
  (row number variable that uses partition variable),
  (assign partition variable)

Als deze ooit worden geëvalueerd, stopt het rijnummer op (geen partities)

We moeten dus iets gebruiken met een gegarandeerde volgorde van uitvoering. Voer geval in wanneer:

SELECT
  t.*, 
  @r := CASE 
    WHEN col = @prevcol THEN @r + 1 
    WHEN (@prevcol := col) = null THEN null
    ELSE 1 END AS rn
FROM
  t, 
  (SELECT @r := 0, @prevcol := null) x
ORDER BY col

Zoals overzicht LD, is de volgorde van de prev: belangrijk – prevol moet worden vergeleken met de waarde van de huidige rij voordat we het een waarde van de huidige rij toewijzen (anders zou het de huidige rijen col zijn, niet de vorige rij COL-waarde).

Hier is hoe dit bij elkaar past:

  • De eerste wanneer wordt geëvalueerd. Als de col van deze rij hetzelfde is als de col van de vorige rij dan wordt opgehoogd en teruggebracht en van de behuizing geretourneerd. Deze retour-LED-waarden worden opgeslagen in @R. Het is een kenmerk van MySQL die toewijzing retourneert de nieuwe waarde van wat er in @R is toegewezen in de REAL-rijen.

  • Voor de eerste rij van de resultatenset is @prevcol null (het is geïnitialiseerd als null in de subquery), dus dit predikaat is onwaar. Dit eerste predikaat retourneert ook false telkens wanneer col verandert (de huidige rij is anders dan de vorige rij). Dit zorgt ervoor dat de tweede WANNEER wordt geëvalueerd.

  • Het tweede WHEN-predikaat is altijd onwaar en bestaat puur om een nieuwe waarde toe te kennen aan @prevcol. Omdat de col van deze rij anders is dan de col van de vorige rij (we weten dit omdat als het hetzelfde was, de eerste WANNEER zou zijn gebruikt), moeten we de nieuwe waarde toewijzen om het de volgende keer te bewaren voor testen. Omdat de toewijzing wordt gemaakt en vervolgens het resultaat van de toewijzing wordt vergeleken met null, en alles wat met null wordt gelijkgesteld onwaar is, is dit predikaat altijd onwaar. Maar in ieder geval heeft het zijn werk gedaan om de waarde van col uit deze rij te behouden, zodat het kan worden geëvalueerd tegen de col-waarde van de volgende rij

  • Omdat de tweede WHEN false is, betekent dit dat in situaties waarin de kolom die we partitioneren door (col) is veranderd, het de ELSE is die een nieuwe waarde geeft voor @r, waarbij de nummering opnieuw wordt gestart vanaf 1

We komen in een situatie terecht waarin dit:

SELECT
  t.*, 
  ROW_NUMBER() OVER(PARTITION BY pcol1, pcol2, ... pcolX ORDER BY ocol1, ocol2, ... ocolX) rn
FROM
  t

Heeft de algemene vorm:

SELECT
  t.*, 
  @r := CASE 
    WHEN col1 = @pcol1 AND col2 = @pcol2 AND ... AND colX = @pcolX THEN @r + 1 
    WHEN (@pcol1 := pcol1) = null OR (@pcol2 := col2) = null OR ... OR (@pcolX := colX) = null THEN null
    ELSE 1 
  END AS rn
FROM
  t, 
  (SELECT @r := 0, @pcol1 := null, @pcol2 := null, ..., @pcolX := null) x
ORDER BY pcol1, pcol2, ..., pcolX, ocol1, ocol2, ..., ocolX

Voetnoten:

  • De p in pcol betekent “partitie”, de o in ocol betekent “volgorde” – in de algemene vorm heb ik de “prev” van de variabelenaam laten vallen om visuele rommel te verminderen

  • De haakjes rond (@pcolX := colX) = nullzijn belangrijk. Zonder hen wijs je null toe aan @pcolX en werkt het niet meer

  • Het is een compromis dat de resultatenset ook op de partitiekolommen moet worden geordend, anders werkt de vergelijking van de vorige kolom. U kunt uw rijnummer dus niet volgens de ene kolom bestellen, maar uw resultatenset in een andere. U kunt dit misschien oplossen met subquery’s, maar ik geloof dat de documenten ook aangeven dat de volgorde van subquery’s kan worden genegeerd tenzij LIMIT wordt gebruikt en dit kan gevolgen hebben prestatie

  • Ik heb me er niet in verdiept, behalve testen of de methode werkt, maar als er een risico bestaat dat de predikaten in de tweede WANNEER weg zullen worden geoptimaliseerd (alles vergeleken met null is null/false, dus waarom zou je de moeite nemen om de opdracht) en niet uitgevoerd, stopt het ook. Dit lijkt niet te gebeuren in mijn ervaring, maar ik accepteer graag opmerkingen en stel een oplossing voor als dit redelijkerwijs zou kunnen gebeuren

  • Het kan verstandig zijn om de nulls die @pcolX creëren naar de daadwerkelijke typen van uw kolommen te casten, in de subquery die de @pcolX-variabelen maakt, namelijk: select @pcol1 := CAST(null as INT), @pcol2 := CAST(null as DATE)


Antwoord 15, autoriteit 2%

Hierdoor kan dezelfde functionaliteit worden bereikt die ROW_NUMBER() EN PARTITION BY biedt in 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

Antwoord 16, Autoriteit 2%

Ik zie geen enkel eenvoudig antwoord dat de “partitie door” -deel bedekt, dus hier is de mijne:

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
  • De bestelling per clausule moet uw needy-noden weerspiegelen. Er is dus al een duidelijke beperking: je kunt op hetzelfde moment geen enkele row_number “emulatie” van dit formulier hebben.
  • De volgorde van de “Computed Column” Matters . Als u MySQL die kolom in een andere bestelling hebt bereken, werkt het misschien niet.
  • In dit eenvoudige voorbeeld zet ik er maar één, maar je kunt verschillende “partitie door” delen

    hebben

       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
    

Antwoord 17, Autoriteit 2%

Dit kan ook een oplossing zijn:

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

Antwoord 18, Autoriteit 2%

MySQL heeft de Row_Number () sinds versie 8,0 + ondersteund.

Als u MySQL 8.0 of hoger gebruikt, controleert u het Row_Number () -functie.
Anders hebt u de functie van Row_number () emuleren.

De Row_Number () is een rangordefunctie die een sequentiaal nummer van een rij retourneert, vanaf 1 voor de eerste rij.

Voor oudere versie,

SELECT t.*, 
       @rowid := @rowid + 1 AS ROWID
  FROM TABLE t, 
       (SELECT @rowid := 0) dummy;

Antwoord 19

een beetje laat, maar kan ook helpen aan iemand die zoekt naar antwoorden …

Tussen rijen / rij_number voorbeeld – recursieve query die in elk SQL kan worden gebruikt:

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

Antwoord 20

Ook een beetje laat, maar vandaag had ik dezelfde behoefte, dus ik heb gezocht op Google en uiteindelijk een eenvoudige algemene benadering die hier in het artikel http://blog.sqlauthority.com/2014/03/09/MYSQL- reset-rij-nummer-voor-elk-groep-partition-by-rij-nummer /

Ik wilde me concentreren op de oorspronkelijke vraag van Paul (dat was mijn probleem ook) dus ik vat mijn oplossing samen als werkvoorbeeld.

Omdat we over twee kolommen willen verdelen, zou ik tijdens de iteratie een SET-variabele maken om te bepalen of er een nieuwe groep is gestart.

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

De 3 betekent bij de eerste parameter van MAKE_SET dat ik beide waarden in de SET wil (3=1|2).
Als we geen twee of meer kolommen hebben die de groepen vormen, kunnen we natuurlijk de bewerking MAKE_SET elimineren. De constructie is precies hetzelfde. Dit werkt voor mij zoals vereist. Veel dank aan Pinal Dave voor zijn duidelijke demonstratie.


Antwoord 21

Oplossingen met cross join en komma werken niet als uw zoekopdracht een GROUP BY-instructie heeft. Voor dergelijke gevallen kunt u subselect gebruiken:

SELECT (@row_number := @row_number + 1) AS rowNumber, res.*
FROM
(
  SELECT SUM(r.amount) 
  FROM Results r 
  WHERE username = 1 
  GROUP BY r.amount
) res
CROSS JOIN (SELECT @row_number := 0) AS dummy

Antwoord 22

Dit is niet de meest robuuste oplossing – maar als u gewoon op zoek bent naar een gepartitioneerde rangorde op een veld met slechts een paar verschillende waarden, is het misschien niet onhandelbaar om een ​​hoesje te gebruiken wanneer logica met zoveel variabelen als u vereisen.

Zoals dit voor mij heeft gewerkt in het verleden:

SELECT t.*, 
   CASE WHEN <partition_field> = @rownum1 := @rownum1 + 1 
     WHEN <partition_field> = @rownum2 := @rownum2 + 1 
     ...
     END AS rank
FROM YOUR_TABLE t, 
   (SELECT @rownum1 := 0) r1, (SELECT @rownum2 := 0) r2
ORDER BY <rank_order_by_field>
;

Ik hoop dat dat logisch is / helpt!


Antwoord 23

Dit werkt perfect voor mij om RownTumber te creëren wanneer we meer dan één kolom hebben. In dit geval twee kolom.

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

Other episodes