MS-SQL 함수 중에 순위 / 번호 / 범위를 구하는 함수

 

if object_id('tempdb..#Employee') is not null drop table #Employee

 

 

create table #Employee

(

       employeeID varchar(10)

       , employeeName varchar(50)

       , gender varchar(2)

       , salary int

)

 

 

insert into #Employee (employeeID, employeeName, gender, salary)

values ('S0001', '홍길동', '남', 6500)

, ('S0002', '김삼순', '여', 5000)

, ('S0003', '강우동', '남', 4500)

, ('S0004', '정주고', '남', 4000)

, ('S0005', '최사모', '여', 3800)

, ('S0006', '정효리', '여', 3000)

, ('S0007', '이최고', '남', 3000)

, ('S0008', '오감자', '남', 2700)

, ('S0009', '김한국', '남', 2500)

, ('S0010', '정주리', '여', 3000)

, ('S0011', '정우성', '남', 2700)

, ('S0012', '한효주', '여', 2500)

 

select * from #Employee

 

----1.전체순위 : RANK() OVER(...) - 급여내림차순순위 - OVER(...) 안에 순위를 구할 기준이 되는 열에 대한 정렬 정보를 입력하면 된다.

select employeeID, employeeName, gender, salary, RANK() OVER(order by salary desc) as rnk from #Employee

 

----2.남/녀별순위 : RANK() OVER(PARTITION BY ...) - 남/여 별 급여내림차순순위 - PARTITION BY에 영역을 구분할 열을 지정하면 된다.

select employeeID, employeeName, gender, salary, RANK() OVER(PARTITION BY gender order by salary desc) as rnk from #Employee

 

----3.전체순위(중복되더라도 순위를 점프하지 않고 이어지는 값으로 표시) : DENSE_RANK() OVER(...) - 급여내림차순순위 - OVER(...) 안에 순위를 구할 기준이 되는 열에 대한 정렬 정보를 입력하면 된다.

select employeeID, employeeName, gender, salary, DENSE_RANK() OVER(order by salary desc) as rnk from #Employee

 

----4.남/녀별순위(중복되더라도 순위를 점프하지 않고 이어지는 값으로 표시) : DENSE_RANK() OVER(PARTITION BY ...) - 남/여 별 급여내림차순순위 - PARTITION BY에 영역을 구분할 열을 지정하면 된다.

select employeeID, employeeName, gender, salary, DENSE_RANK() OVER(PARTITION BY gender order by salary desc) as rnk from #Employee

 

----5.번호표시 : ROW_NUMBER() OVER(...) - 이름을내림차순정렬 - OVER(...) 안에 번호를 구할 기준이 되는 열에 대한 정렬 정보를 입력하면 된다.

select ROW_NUMBER() OVER(order by employeeName desc) as num, employeeID, employeeName, gender, salary from #Employee

 

----6.남/녀별번호표시 : ROW_NUMBER() OVER(PARTITION BY ...) - 남/여 별 이름을내림차순순위 - PARTITION BY에 영역을 구분할 열을 지정하면 된다.

select ROW_NUMBER() OVER(PARTITION BY gender order by salary desc) as num, employeeID, employeeName, gender, salary from #Employee

 

----7.범위표시 : NTILE(범위) OVER(...) - 급여내림차순범위 - OVER(...) 안에 범위를 구할 기준이 되는 열에 대한 정렬 정보를 입력하면 된다.

select employeeID, employeeName, gender, salary, NTILE(3) OVER(order by salary desc) as band from #Employee

 

----8.남/녀별범위표시 : NTILE(범위) OVER(PARTITION BY ...) - 남/여 별 급여내림차순범위 - OVER(...) 안에 범위를 구할 기준이 되는 열에 대한 정렬 정보를 입력하면 된다.

select employeeID, employeeName, gender, salary, NTILE(3) OVER(PARTITION BY gender order by salary desc) as band from #Employee

 

 

 

 

if object_id('tempdb..#Employee') is not null drop table #Employee