MSSQL 행 번호 매기기

MSSQL에서 행(Row) 번호 매기는 방법입니다.

 

문법

SELECT ROW_NUMBER() OVER(PARTITION BY columnName ORDER BY columnName) AS NO
  FROM tablename

 

행번호 쿼리

WITH EX AS
(
	SELECT '한국' NAME, '아시아' REGION
	UNION
	SELECT '미국', '북아메리카'
	UNION
	SELECT '캐나다', '북아메리카'
	UNION
	SELECT '영국', '유럽'
	UNION
	SELECT '독일', '유럽'
	UNION
	SELECT '중국', '아시아'
	UNION
	SELECT '필리핀', '아시아'
)
SELECT ROW_NUMBER() OVER(ORDER BY NAME) AS NO,
       NAME, REGION
  FROM EX

 

파티션별 행번호 쿼리

WITH EX AS
(
	SELECT '한국' NAME, '아시아' REGION
	UNION
	SELECT '미국', '북아메리카'
	UNION
	SELECT '캐나다', '북아메리카'
	UNION
	SELECT '영국', '유럽'
	UNION
	SELECT '독일', '유럽'
	UNION
	SELECT '중국', '아시아'
	UNION
	SELECT '필리핀', '아시아'
)
SELECT ROW_NUMBER() OVER(PARTITION BY REGION ORDER BY NAME) AS NO,
       REGION, NAME
  FROM EX