개요
상황에 따라서 내가 원하는 입력 순서대로 값을 출력해야될 때가 있다.
예를 들어 보자. 행의 값이 각각 '1', '2', '3'인 행이 있다.
이 중에서 임의로 '2' > '3' > '1' 의 순서로 결과를 조회해야 한다.
하지만 MSSQL에서는 '1' > '2' > '3' 순서대로 결과가 출력된다.
- 입력 쿼리
select
*
from
number_table
where
number in (2, 3, 1)
- 조회 결과
내가 생각한대 순서대로 조회되지 않고 자동적으로 정렬되어서 나온다.
왜 그럴까? 그리고 어떻게 해야 할까?
[ 결과 미리 보기 ]
1부터 9까지의 값이 있는 테이블이 있다.
나는 이 테이블을 2, 4, 6, 8, 9, 7, 5, 3, 1 순으로 조회를 해야 한다.
[ 참고사항 ]
이 포스팅은 SELECT구문에 입력한 값을 차례대로 조회하기 위한 방법을 설명한 포스팅으로써,
포스팅에서 사용된 쿼리에 대한 자세한 내용은 생략합니다.
단, 해당 내용과 관련된 설명은 참고할 수 있도록 도중도중 참고 링크를 넣어두었으니 궁금하면 확인해주세요.
SELECT 조회 결과가 자동적으로 정렬되는 이유
MS-SQL(SQL Server)에서 기본 키(Primary Key, PK)로 지정된 컬럼의 값은 일반적으로 클러스터형 인덱스(Clustered Index)로 저장된다.
클러스터형 인덱스란, 해당 키 값을 기반으로 테이블이나 뷰의 데이터행을 정렬하여 저장하는 방식을 말한다.
간단히 말해서 데이터베이스 엔진이 인덱스를 이용하여 효율적으로 데이터를 찾도록 하기 위함인데,
클러스터형 인덱스, 그리고 인덱스에 대해 조금 더 알고 싶다면 아래의 포스팅에서 참고할 수 있다.
- SQL - 인덱스의 개념, 특징과 종류, 차이점, 데이터 액세스 기법(전체 테이블 스캔과 인덱스 스캔)
- 2.2 SQL Server의 클러스터형 인덱스 부분 참고
요약해보자.
기본키는 일반적으로 클러스터형 인덱스로 저장되기 때문에 자동적으로 조회 결과가 정렬된다.
그렇기 때문에 기본키를 활용하여 쿼리를 조회하면 자동으로 정렬되어 검색 결과가 출력되기 때문에
내가 입력한 IN구문의 순서대로 출력되지 않고 인덱스의 정렬 순서대로 조회되는 것이다.
IN 구문에 입력한 값 순서대로 데이터를 조회하려면?
MySQL에는 따로 구문이 있는 것 같지만,
MS-SQL에서는 IN구문의 입력값 순서대로 출력에 대한 내용은 찾기가 어려웠다.
이 방법은 IN구문에 입력 순서대로 데이터를 조회하기 위한 하나의 방법으로 생각해두면 좋을 것 같다.
사실 포스팅을 쓴 이유 또한 내가 재사용하기 위함의 용도가 가장 크다.
입력 순서대로 데이터 조회 방법
로직은 생각보다 단순하다.
1) 입력한 값을 순서와 함께 테이블의 형태로 저장한다.
2) 조회하려는 테이블에서 입력한 값이 있는 테이블과 INNER JOIN을 한다.
3) 입력한 값이 있는 테이블의 순서대로 값을 조회한다.
이 내용을 조금 더 디테일하게 풀어보자.
1) IN구문의 입력값을 변수에 저장한다.
2) 입력값이 저장된 변수를 XML 형식으로 변환하여 테이블의 형태를 구성한다.
3) 테이블의 형태를 구성할 때 ROW_NUMBER() 함수를 사용하여 순서를 함께 포함한다.
4) WITH구문(공통 테이블식)을 이용하여 조회할 테이블을 JOIN한다.
5) ORDER BY절에 ROW_NUMBER() 함수를 사용한 컬럼을 기재하고 결과를 출력한다.
예시 테이블
- 테이블의 이름은 number_table이다.
- 컬럼의 이름은 number이다.
- number 컬럼은 기본키로 지정되어 있다.
select * from number_table
조회 내용 정의
내가 차례대로 출력해야할 값은 차례대로 2 > 4 > 6 > 8 > 9 > 7 > 5 > 3 > 1 이다.
쿼리로 표현해보자면 다음과 같다.
(물론 아래의 쿼리로는 입력 순서대로 조회되지 않고, 1부터 9까지 차례대로 조회된다.)
select * from number_table
where number in (2,4,6,8,9,7,5,3,1)입력한 값을 행의 형태로 변환 (xmlnamespaces절 사용)
IN구문에 입력되는 값을 문자열 변수에 저장하자.
이 값을 XML 형식으로 저장하여 행으로 만들 것이다.
xmlnamespaces절에 대한 설명은 아래의 포스팅에서 자세히 확인할 수 있다.
declare @inputData nvarchar(max)
set @inputData = '2,4,6,8,9,7,5,3,1';
변수를 XML 형식으로 변환하여 재저장하자.
변환된 값은 XML 형식이기 때문에 XML 타입을 갖는 변수에 저장해야한다.
때문에 @XML이라는 변수를 추가로 선언하여 저장하였다.
declare @xml xml
set @xml = N'<root><item>' + replace(@inputData, ',', '</item><item>') + N'</item></root>';
-- @inputData : 위에서 저장한 입력값
[ 중간 확인 ]
입력된 데이터의 값을 테이블로 확인해보자.
SELECT
x.num.value('.', 'int') AS 'number'
FROM @xml.nodes('/root/item') AS x(num)
정상적으로 xml 형식의 문자열이 테이블로 생성되었다.
차례대로 입력한 값에 순서를 넣어 테이블로 변환
이제 입력값의 순서대로 XML 테이블의 순서를 지정해준다.
순서는 ROW_NUMBER() 함수를 사용하며, 이 포스팅에서 설명하는 내용의 핵심이 되는 함수이다.
ROW_NUMBER() 함수에 대한 설명은 아래의 포스팅에서 자세히 확인할 수 있다.
SELECT
ROW_NUMBER() OVER (ORDER BY x.num) AS col_order, -- 입력된 순서대로 순서를 정의
x.num.value('.', 'int') AS 'number'
FROM @xml.nodes('/root/item') AS x(num)
공통테이블식 이용 (With)
공통 테이블 식을 만들어서 조회할 테이블과 INNER JOIN을 한다.
- 공통테이블식에 사용된 이름 : temp
- 조회할 테이블 이름 : number_table
- 조회할 컬럼 이름 : number
-- WITH 구문을 사용
WITH temp AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY x.num) AS col_order,
x.num.value('.', 'int') AS 'number'
FROM
@xml.nodes('/root/item') AS x(num)
)
이제 결과를 조회할 쿼리에 ROW_NUMBER() 함수로 생성된 컬럼(col_order)을 ORDER BY절에 기재한다.
-- 데이터 조회
SELECT
num.*
FROM number_table as num
INNER JOIN temp on temp.number = num.number
ORDER BY
col_order
- 쿼리 결과
전체 쿼리
- number_table이 있다고 가정함.
- 컬럼의 이름은 number이며, 기본키로 설정되어 있다고 가정함.
- number 컬럼의 값은 1부터 차레대로 9까지 있다고 가정함.
- 그 외 컬럼은 생략함.
-- 입력값 설정
declare @inputData nvarchar(max)
set @inputData = '2,4,6,8,9,7,5,3,1';
-- 문자열을 XML 형식으로 변환
declare @xml xml
set @xml = N'<root><item>' + replace(@inputData, ',', '</item><item>') + N'</item></root>';
-- WITH 구문 사용
WITH temp AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY x.num) AS 'col_order',
x.num.value('.', 'int') AS 'number'
FROM
@xml.nodes('/root/item') AS x(num)
)
-- 데이터 조회
SELECT
num.*
FROM number_table as num
INNER JOIN temp on temp.number = num.number
ORDER BY
'col_order'