상관쿼리 - 순위 구하기

라디오스님

/*
작성 : 김진와
일자 : 2003 .07 .11
내용 : 순위구하기 및 최고,최소값 출력 예제
*/

USE TEST
GO

-- 테이블 생성
CREATE TABLE ST_LEVEL
(
S_NO smallint IDENTITY (1, 1) NOT NULL ,
S_NAME varchar(10) NOT NULL ,
S_POINT float NULL ,
S_RANK smallint NULL
) ON [PRIMARY]
GO

-- 기본키 지정
ALTER TABLE ST_LEVEL WITH NOCHECK ADD
CONSTRAINT PK_ST_LEVEL PRIMARY KEY CLUSTERED
(
S_NO
) ON [PRIMARY]
GO


-- 데이타 입력
INSERT INTO ST_LEVEL (S_NAME, S_POINT) VALUES ('J', 84)
GO
INSERT INTO ST_LEVEL (S_NAME, S_POINT) VALUES ('I', 73)
GO
INSERT INTO ST_LEVEL (S_NAME, S_POINT) VALUES ('H', 88)
GO
INSERT INTO ST_LEVEL (S_NAME, S_POINT) VALUES ('G', 74)
GO
INSERT INTO ST_LEVEL (S_NAME, S_POINT) VALUES ('F', 68)
GO
INSERT INTO ST_LEVEL (S_NAME, S_POINT) VALUES ('E', 77)
GO
INSERT INTO ST_LEVEL (S_NAME, S_POINT) VALUES ('D', 80)
GO
INSERT INTO ST_LEVEL (S_NAME, S_POINT) VALUES ('C', 79)
GO
INSERT INTO ST_LEVEL (S_NAME, S_POINT) VALUES ('B', 87)
GO
INSERT INTO ST_LEVEL (S_NAME, S_POINT) VALUES ('A', 90)
GO


-- S_RANK 컬럼이 없이 순위 구하기
SELECT S_NO, S_NAME, S_POINT,
RANKING = (SELECT COUNT(DISTINCT S_POINT) FROM ST_LEVEL WHERE S_POINT >= A.S_POINT)
FROM ST_LEVEL A
ORDER BY S_NO DESC
GO

-- S_RANK 컬럼에 전체 순위 일괄 업데이트
UPDATE ST_LEVEL SET S_RANK=X.RANK
FROM (
SELECT S_NAME,
RANK = (SELECT COUNT(DISTINCT S_POINT) FROM ST_LEVEL WHERE S_POINT >= A.S_POINT)
FROM ST_LEVEL A
) X
WHERE ST_LEVEL.S_NAME = X.S_NAME
GO


-- 데이타 확인
SELECT * FROM ST_LEVEL ORDER BY S_NO DESC
GO


-- 순위 업데이트
UPDATE ST_LEVEL SET S_POINT=81 WHERE S_NAME='E'
GO


-- 전체 순위 일괄 업데이트문으로 순위 재조정 (위의 쿼리 적용)


-- 데이타 확인(위의 쿼리 적용)


/*
Min함수를 이용하여 자신의 점수 이상의 점수중에 Min(point)에 해당되는 이의 Level을 구하기
*/
-- 특정값 확인
SELECT * FROM ST_LEVEL WHERE S_NAME='D'
GO

/*
S_NAME=D 의 값중 S_RANK=5 이다.

또한 아래의 부분은 변수(DECALRE, SET 문)로 대치해도 상관없음
*/
-- 특정 순위 이상인 점수중 MIN(POINT) 에 해당되는 값중 가장 큰 수
SELECT *
FROM ST_LEVEL
WHERE S_NAME = (SELECT MAX(S_NAME) FROM ST_LEVEL WHERE S_RANK < 5)
GO

-- 특정 순위 이상인 점수중 MIN(POINT) 에 해당되는 값 : 예)01
SELECT S_NO, MIN(S_NAME) AS 'S_NAME', S_POINT, S_RANK
FROM ST_LEVEL
GROUP BY S_NO, S_POINT, S_RANK
HAVING S_RANK < 5
--ORDER BY S_NO DESC
GO

-- 특정 순위 이상인 점수중 MIN(POINT) 에 해당되는 값 : 예)02
-- 이경우는 구간별 값도 구할수 있음
SELECT *
FROM
(
SELECT S_NO, S_NAME, S_POINT,
RANKING=(SELECT COUNT(DISTINCT S_POINT) FROM ST_LEVEL WHERE S_POINT >= A.S_POINT)
FROM ST_LEVEL A
) X
WHERE (RANKING < 5)
GO 

[출처] 상관쿼리 - 순위 구하기|작성자 진카자마