[MySQL] SELECT 결과에 행 번호 자동으로 매기기

키워드

넘버링, 번호 자동 매기기, 행 번호 자동 증가

 

목적은 아래처럼 SELECT 쿼리의 검색 결과에 단순히 1씩 증가하는 번호를 붙이고자하는 것이다.

 

UserID	UserName	Duty	SEQ
hgd	홍길동	사장	1
jjh	전지현	상무이사	2
lss	클라라	상무이사	3

 

SELECT *, @SEQ := @SEQ+1 AS SEQ FROM B, (SELECT @SEQ := 0) A;

결과적으로 위 쿼리를 실행해 보면 B 테이블의 조회 결과에 1부터 증가하는 SEQ가 따라붙게 된다.

그런데 아래처럼 쿼리가 조금 복잡해지면 문제가 생긴다.

SELECT userinfo.id AS SIPAddress, userinfo.kname AS UserName,
org_duty.duty_name AS UserTitle, userinfo.depart_code AS ParentDeptCode,
@SEQ := @SEQ+1 AS SEQ
FROM userinfo
JOIN org_duty ON userinfo.duty_no=org_duty.duty_no, (SELECT @SEQ := 0) A
ORDER BY userinfo.depart_code, org_duty.priority_level DESC, userinfo.kname;

실행해 보면 SEQ 값이 올바르게 정렬되지 않았고 뒤섞여 있다(join 때문).

아래처럼 서브쿼리를 사용하면 원하는 순서대로 번호를 매길 수 있다.

SELECT SIPAddress, UserName, UserTitle, @SEQ := @SEQ+1 AS Sequence, ParentDeptCode
FROM
(SELECT userinfo.id AS SIPAddress, userinfo.kname AS UserName,
org_duty.duty_name AS UserTitle, userinfo.depart_code AS ParentDeptCode
FROM userinfo
INNER JOIN org_duty ON userinfo.duty_no=org_duty.duty_no
ORDER BY userinfo.depart_code, org_duty.priority_level DESC, userinfo.kname)
AS tbl, (SELECT @SEQ := 0) A

넘버링 방식 사용 이유

ORACLE이라면 row_number() over()라는 좋은 함수가 있지만 MySQL에는 그런 함수가 없다.

대신 TABLE에 AUTO_INCREMENT 필드를 만들어 INSERT 시점에 자동 증가시키는 기능이 있다.

하지만 MySQL 뷰 테이블을 생성하는 경우 변수와 서브 쿼리를 사용할 수 없으므로 좀 더 난해해진다.