MySQL GROUP_CONCAT으로 그룹 내 번호 매기기 구현하기

MySQL GROUP_CONCAT에서 번호 매기기 구현하기

MySQL에서 GROUP_CONCAT() 함수를 사용하면 그룹별로 여러 값을 하나의 문자열로 연결할 수 있습니다. 여기에 사용자 정의 변수를 활용하면 각 그룹 내 항목에 순번(1., 2., 3.)을 붙여 출력할 수도 있습니다. 이 글에서는 실제 예제를 통해 그 방법을 단계별로 살펴보겠습니다.

1. 테이블 생성

먼저 예제에 사용할 테이블을 생성합니다.

mysql> create table DemoTable1627
    -> (
    -> FirstName varchar(20),
    -> LastName varchar(20)
    -> );
Query OK, 0 rows affected (0.59 sec)

2. 샘플 데이터 삽입

INSERT 명령을 사용하여 테이블에 레코드를 추가합니다.

mysql> insert into DemoTable1627 values('John','Smith');
Query OK, 1 row affected (0.11 sec)
mysql> insert into DemoTable1627 values('John','Doe');
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable1627 values('Adam','Smith');
Query OK, 1 row affected (0.18 sec)
mysql> insert into DemoTable1627 values('Carol','Taylor');
Query OK, 1 row affected (0.08 sec)

3. 전체 데이터 확인

SELECT 문으로 테이블의 모든 레코드를 조회해 보겠습니다.

mysql> select * from DemoTable1627;

실행 결과는 다음과 같습니다.

+-----------+----------+
| FirstName | LastName |
+-----------+----------+
| John      | Smith    |
| John      | Doe      |
| Adam      | Smith    |
| Carol     | Taylor   |
+-----------+----------+
4 rows in set (0.00 sec)

4. GROUP_CONCAT으로 그룹 내 번호 매기기 쿼리

이제 핵심인 group_concat() 번호 매기기 쿼리입니다. 사용자 변수 @p(이전 행의 LastName 값)와 @j(현재 그룹 내 순번)를 이용해, 같은 성(LastName)을 가진 사람들에게 순서대로 번호를 부여합니다.

mysql> select  LastName,
    -> group_concat(
    -> concat(@j := if (@p = LastName, @j + 1, if(@p := LastName,1,1)), '.', FirstName)
    -> separator ', ') FirstName
    -> from  DemoTable1627
    -> group by LastName;

실행 결과는 다음과 같습니다.

+----------+----------------+
| LastName | FirstName      |
+----------+----------------+
| Doe      | 1.John         |
| Smith    | 1.John, 2.Adam |
| Taylor   | 1.Carol        |
+----------+----------------+
3 rows in set (0.09 sec)

동작 원리 살펴보기

• @p = LastName 비교: 현재 행의 LastName이 이전 행과 같으면 기존 순번(@j)에 1을 더합니다.
• 새로운 그룹 시작 시: LastName이 바뀌면 @p := LastName으로 변수를 갱신하고 순번을 1로 초기화합니다.
• concat()과 separator: '번호.이름' 형태로 문자열을 만든 뒤, 쉼표(,)로 연결하여 그룹별 결과를 한 줄로 출력합니다.

이처럼 사용자 정의 변수와 GROUP_CONCAT을 조합하면 별도의 서브쿼리나 애플리케이션 로직 없이도 SQL만으로 그룹 내 번호 매기기를 손쉽게 구현할 수 있습니다. 참고로 MySQL 8.0 이상 환경이라면 윈도우 함수인 ROW_NUMBER() OVER (PARTITION BY ...)를 사용하는 것이 더 권장되는 방식입니다.