Число строк в группе в mysql

Я хочу создать базу результатов запроса по этому сценарию, которая может создать номер строки в соответствии с командой crew_id и введите.

id   crew_id   amount    type
 1      4       1000      AUB
 2      4       1500      AUB
 3      5       8000      CA
 4      4       1000      CA
 5      5       1000      AUB
 6      6       3000      AUB
 7      4       2000      CA
 8      6       3500      AUB
 9      4       5000      AUB
 10     5       9000      CA
 11     5       1000      CA

OUTPUT должен быть ff:

id    crew_id   amount   type    row_number
 1      4       1000      AUB        1    
 2      4       1500      AUB        2 
 9      4       5000      AUB        3
 4      4       1000      CA         1
 7      4       2000      CA         2
 5      5       1000      AUB        1
 3      5       8000      CA         1
 10     5       9000      CA         2
 11     5       1000      CA         3
 6      6       3000      AUB        1
 6      6       3000      AUB        2

Мне нужен только один оператор select только в этом выпуске

Ответ 1

Вопрос довольно старый. Но я хотел бы опубликовать его, если у кого-то будет такая же проблема.

Прежде всего, описанные ответы не работают правильно. Например, для

id   crew_id   amount    type
1      4       1000      AUB
2      4       1500      AUB
5      5       1000      AUB
6      6       3000      AUB
8      6       3500      AUB
9      4       5000      AUB

(Я просто удалил строки с типом "CA" ), таблица результатов будет

id   crew_id   amount    rank   type
1      4       1000      1      AUB
2      4       1500      2      AUB
9      4       5000      3      AUB
5      5       1000      4      AUB
6      6       3000      5      AUB
8      6       3500      6      AUB

Таким образом, на самом деле он не использует как crew_id, так и тип, он просто использует тип.

Вот как я решил эту проблему (возможно, есть более элегантный способ сделать это, чем использовать два вложенных CASE, но вы получите эту идею):

SELECT id,
    amount,
    CASE crew_id 
        WHEN @curCrewId THEN
            CASE type 
                WHEN @curType THEN @curRow := @curRow + 1 
                ELSE @curRow := 1
            END
        ELSE @curRow :=1
    END AS rank,
    @curCrewId := crew_id AS crew_id,
    @curType := type AS type
FROM Table1 p
JOIN (SELECT @curRow := 0, @curCrewId := 0, @curType := '') r
ORDER BY crew_id, type

Основная идея остается. Я просто добавил переменную @curCrewId. Если кому-то нужно использовать 3 переменные для группировки, просто используйте 3 переменные и 3 вложенных "CASE".:)

Ответ 2

Прошу пройти через мою скрипку

Этот последний пробовал

    SELECT    id,
              crew_id,
              amount,
              type,
             ( 
                CASE type 
                WHEN @curType 
                THEN @curRow := @curRow + 1 
                ELSE @curRow := 1 AND @curType := type END
              ) + 1 AS rank
    FROM      Table1 p,
              (SELECT @curRow := 0, @curType := '') r
   ORDER BY  crew_id,type asc;

Ответ 3

@Ответ Janty не работает, когда тип начинается с числа (ранг начинается с 2 вместо 1).

Используйте вместо этого следующее:

SELECT id,
     crew_id,
     amount,
     CASE type 
         WHEN @curType THEN @curRow := @curRow + 1 
         ELSE @curRow := 1
     END AS rank,
     @curType := type AS type
FROM Table1 p
JOIN (SELECT @curRow := 0, @curType := '') r
ORDER BY crew_id, type

Ответ 4

В дополнение к ответу @Janty это решение, если вы хотите UDATE свою таблицу с номером журнала:

UPDATE myTable mt,(SELECT @curRow := 0, @curType := '') r SET type=
    ( 
        CASE type 
            WHEN @curType
        THEN @curRow := @curRow + 1 
        ELSE @curRow := 1 AND @curType := type END
      )
;

В качестве janty тоже crewId не входит. Используйте второй "случай" для этого, как указано в других ответах.

Ответ 5

SELECT id, crew_id, amount, type,
       ( 
        CASE type 
          WHEN @curType 
          THEN @curRow := @curRow + 1 
          ELSE @curRow := 1 AND @curType := type  END
      ) + 1 AS rank
 FROM      Table1 p,
      (SELECT @curRow := 0, @curType := '') r
   ORDER BY  crew_id, type asc;

Ответ 6

Вот мой ответ, используя только один случай, который создает номер строки в соответствии с обоими столбцами:

SELECT id, crew_id, amount, type,
   (CASE CONCAT(crew_id, type) 
        WHEN @cur_crew_type
        THEN @curRow := @curRow + 1
        ELSE @curRow := 0 END) + 1 AS cnt,
    @cur_crew_type := CONCAT(crew_id, type) AS cur_crew_type
FROM TABLE t,
     (SELECT @curRow := 0, @cur_crew_type := '') counter
      ORDER BY crew_id, type;