Numer wiersza () w MySQL

W MySQL nie ma funkcjonalności rankingowej. Najbliżej jest użyć zmiennej:

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

Więc jak to działa w moim przypadku? Potrzebuję dwóch zmiennych, po jednej dla każdego z col1 i col2? Col2 wymagałby resetowania, gdy zmieniłby się col1..?

Zawsze podążam za tym wzorcem. Pod tą tabelą:

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

Możesz otrzymać taki wynik:

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

Uruchamiając to zapytanie, które nie wymaga zdefiniowania żadnej zmiennej:

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

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

Sprawdź ten artykuł, pokazuje, jak naśladować SQL ROW_NUMBER () z partycją przez w MySQL. Natknąłem się na ten sam scenariusz w implementacji WordPressa. Potrzebowałem ROW_NUMBER() i nie było go tam.

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

Przykład w artykule to użycie pojedynczej partycji przez pole. Aby podzielić na dodatkowe pola, możesz zrobić coś takiego:

  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 

Używając concat_ws uchwyty null ' s. przetestowałem to przeciw 3 Polom używającym int, date i varchar. Mam nadzieję, że to pomoże. Sprawdź artykuł, ponieważ rozbija to zapytanie i wyjaśnia je.

Ja też zagłosowałbym na rozwiązanie Mostacho z drobną modyfikacją jego kodu zapytania:

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

Co da ten sam wynik:

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

Dla tabeli:

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

Z tą tylko różnicą, że zapytanie nie używa JOIN i GROUP BY, opierając się na zagnieżdżonym select.

Od MySQL 8.0.0 i powyżej możesz natywnie używać funkcji okienkowych.

1.4 Co nowego w MySQL 8.0:

Funkcje okna.

MySQL obsługuje teraz funkcje okien, które dla każdego wiersza zapytania wykonują obliczenia przy użyciu wierszy powiązanych z tym wierszem. Należą do nich funkcje takie jak RANK (), LAG () i NTILE (). Ponadto kilka istniejących funkcji agregujących może być teraz używanych jako funkcje okna; na przykład sum () i AVG ().

ROW_NUMBER () over_clause :

Zwraca numer bieżącego wiersza wewnątrz jego partycji. Liczba wierszy wynosi od 1 do liczby wierszy partycji.

ORDER BY wpływa na kolejność numerowania wierszy. Bez ORDER BY, Numeracja wierszy jest nieokreślona.

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

Zdefiniowałbym funkcję:

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

Wtedy mógłbym zrobić:

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

Teraz nie masz subquery, której nie możesz mieć w widokach.

Nie ma takiej zabawy jak rownum, row_num() w MySQL ale sposób jest jak poniżej:

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

Zapytanie o numer wiersza w mysql

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

Najlepszym rozwiązaniem okazało się użycie subquery takiej jak Ta:

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

Partycja według kolumn jest po prostu porównywana z ' = ' i oddzielana przez AND. Kolejność według kolumn byłaby porównywana z " " i rozdzielana przez OR.

Nie można naśladować funkcji numeru wiersza. Możesz uzyskać wyniki, których oczekujesz, ale najprawdopodobniej na pewnym etapie będziesz rozczarowany. Oto co mówi dokumentacja mysql:

Dla innych poleceń, takich jak SELECT, możesz uzyskać oczekiwane wyniki, ale nie jest to gwarantowane. W poniższym oświadczeniu możesz pomyśleć, że MySQL oceni @a pierwszy, a następnie zrobi przypisanie drugi: Wybierz @a, @a:=@a+1, ...; Jednak kolejność oceny wyrażeń użycie zmiennych użytkownika jest niezdefiniowane.

Pozdrawiam, Georgi.

MariaDB 10.2 implementuje "funkcje okien", w tym RANK (), ROW_NUMBER () i kilka innych rzeczy:

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

Bazując na rozmowie w Percona Live w tym miesiącu, są one dość dobrze zoptymalizowane.

Składnia jest identyczna z kodem w pytaniu.

Trochę za późno, ale może też pomóc komuś, kto szuka odpowiedzi...

Between rows / row_number example-zapytanie rekurencyjne, które może być użyte w dowolnym 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

Pozwala to na uzyskanie tej samej funkcjonalności, co ROW_NUMBER() i PARTITION BY w 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

Również trochę późno, ale dzisiaj miałem taką samą potrzebę, więc szukałem w Google i wreszcie proste ogólne podejście znaleźć tutaj w artykule Pinal Dave http://blog.sqlauthority.com/2014/03/09/mysql-reset-row-number-for-each-group-partition-by-row-number/

Chciałem skupić się na oryginalnym pytaniu Paula (to był mój problem, jak również) więc podsumować moje rozwiązanie jako przykład pracy.

Ponieważ chcemy podzielić na dwie kolumny utworzyłbym zmienną SET podczas iteracja w celu określenia, czy nowa grupa została uruchomiona.

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

3 oznacza w pierwszym parametrze MAKE_SET, że chcę obie wartości w zestawie (3=1/2). Oczywiście, jeśli nie mamy dwóch lub więcej kolumn budujących grupy, możemy wyeliminować operację MAKE_SET. Konstrukcja jest dokładnie taka sama. To działa dla mnie zgodnie z wymaganiami. Wielkie podziękowania dla Pinal Dave za jego wyraźnej demonstracji.

Nie widzę żadnej prostej odpowiedzi obejmującej część "PARTITION BY" więc oto moja :

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

• klauzula ORDER BY musi odzwierciedlać Twój numer wiersza. Tak więc istnieje już wyraźne ograniczenie: nie możesz mieć kilku wierszy "emulacji" tej formy w tym samym czasie.
• kolejność "kolumny obliczeniowej" ma znaczenie . Jeśli mysql oblicza te kolumny w innej kolejności, może to nie działać.

• W tym prostym przykładzie umieściłem tylko jeden, ale można mieć kilka" partycji przez " części

      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
  

To może być również rozwiązanie:

SET @row_number = 0;

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

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;

To działa idealnie dla mnie, aby utworzyć numer wiersza, gdy mamy więcej niż jedną kolumnę. W tym przypadku dwie kolumny.

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

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