mysql의 그룹당 행 번호

mysql의 그룹당 행 번호

저는 crew_id와 type에 따라 행 번호를 생성할 수 있는 이 시나리오를 기반으로 쿼리 결과를 생성하고 싶습니다.

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

그 질문은 꽤 오래되었습니다.하지만 누군가 같은 문제가 생길 경우를 대비해서 게시하고 싶습니다.

우선, 설명된 답변이 올바르게 작동하지 않습니다.예를 들어, 다음과 같은 경우

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와 type을 모두 사용하지 않고 type만 사용합니다.

다음은 제가 이 문제를 해결한 방법입니다(아마도 두 개의 중첩된 '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'를 사용하세요. :)

MySQL 8.0에서는 ROW_NUMBER 창 함수를 사용할 수 있습니다.

SELECT *, ROW_NUMBER() OVER(PARTITION BY crew_id) AS row_number
FROM MyTable

@Janty의 대답은 형식이 숫자로 시작할 때(순위가 1이 아닌 2에서 시작할 때) 작동하지 않습니다.

대신 다음을 사용합니다.

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

@Janty의 답변 외에도 행 번호로 테이블을 업데이트하려면 다음과 같은 해결책이 있습니다.

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 too crewId가 안에 없습니다.다른 답변에 언급된 것과 같이 두 번째 "사례"를 사용합니다.

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;

다음은 두 열에 따라 행 번호를 생성하는 하나의 대소문자만 사용한 답변입니다.

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;

언급URL : https://stackoverflow.com/questions/17939198/row-number-per-group-in-mysql