SELECT Transact-SQL 문에서 동적으로 행 번호를 매기는 방법
SQL Server
2007. 3. 16. 01:41
출처 : http://support.microsoft.com/kb/186133/ko
SELECT Transact-SQL 문에서 동적으로 행 번호를 매기는 방법
| 기술 자료 ID | : | 186133 |
| 마지막 검토 | : | 2006년 5월 22일 월요일 |
| 수정 | : | 5.1 |
이 문서는 이전에 다음 ID로 출판되었음: KR186133
요약
이 문서에서는 유연성 있는 방법을 사용하여 SELECT 문을 수행할 때 동적으로 행의 순위를 매기는 방법을 설명합니다. 이 방법은 유일한 해결책일 수 있으며 프로시저 해결책보다 빠릅니다. 행 번호 매기기나 순위 매기기는 전형적인 프로시저 문제입니다. 이 해결책은 대개 루프와 임시 테이블을 기반으로 하므로 SQL Server 루프와 커서를 사용합니다. 이 기술은 자동 조인에 기반을 두고 있습니다. 선택되는 관계는 대개 "보다 큼"입니다. 특정 데이터 집합을 자신과 비교할 때 해당 집합의 각 요소가 "보다 큼" 관계를 충족하는 횟수를 계산합니다.
참고 다음 예제는 pubs 데이터베이스를 기반으로 합니다. 기본적으로 Northwind 예제 데이터베이스와 pubs 예제 데이터베이스는 SQL Server 2005에 설치되지 않습니다. 이러한 데이터베이스는 Microsoft 다운로드 센터에서 다운로드할 수 있습니다. 자세한 내용을 보려면 다음 Microsoft 웹 사이트를 방문하십시오.
쿼리:
쿼리:
SQL Server 2005에서는 다음 코드를 사용합니다.
쿼리:
SQL Server 2005에서는 다음 코드를 사용합니다.
예제:
예제 1:
예제 2:
Microsoft 제품 관련 기술 전문가들과 온라인으로 정보를 교환하시려면 Microsoft 뉴스 그룹 (http://support.microsoft.com/newsgroups/default.aspx)에 참여하시기 바랍니다.
참고 다음 예제는 pubs 데이터베이스를 기반으로 합니다. 기본적으로 Northwind 예제 데이터베이스와 pubs 예제 데이터베이스는 SQL Server 2005에 설치되지 않습니다. 이러한 데이터베이스는 Microsoft 다운로드 센터에서 다운로드할 수 있습니다. 자세한 내용을 보려면 다음 Microsoft 웹 사이트를 방문하십시오.
http://go.microsoft.com/fwlink/?linkid=30196 (http://go.microsoft.com/fwlink/?linkid=30196)(영문)
SQL2000SampleDb.msi를 다운로드한 후 SQL2000SampleDb.msi를 두 번 눌러 예제 데이터베이스 스크립트를 추출합니다. 기본적으로 SQL2000SampleDb.msi는 데이터베이스 스크립트와 추가 정보 파일을 다음 폴더에 추출합니다.
C:\SQL Server 2000 Sample Databases
추가 정보 파일의 지침에 따라 설치 스크립트를 실행합니다.
위로 가기SQL Server 2005를 사용하는 경우
SQL Server 2005에서 새 기능으로 제공되는 순위 함수를 사용하는 것이 좋습니다. 순위 함수에 대한 자세한 내용은 다음 MSDN(Microsoft Developer Network) 웹 사이트를 참조하십시오.http://msdn2.microsoft.com/ko-kr/library/ms189798.aspx (http://msdn2.microsoft.com/ko-kr/library/ms189798.aspx)
예제 1
이 예제의 경우| • | 집합 1은 저자입니다. |
| • | 집합 2는 저자입니다. |
| • | 관계는 "성과 이름이 보다 큼(last and first names are greater than)"입니다. |
| • | 이름 + 성을 다른 이름 + 성과 비교하여 중복 문제를 방지할 수 있습니다. |
| • | count(*)를 통해 해당 관계가 충족된 횟수를 계산합니다. |
select rank=count(*), a1.au_lname, a1.au_fname
from authors a1, authors a2
where a1.au_lname + a1.au_fname >= a2.au_lname + a2.au_fname
group by a1.au_lname, a1.au_fname
order by rank
select rank() OVER (ORDER BY a.au_lname, a.au_fname) as rank, a.au_lname, a.au_fname
from authors a
order by rank
Rank Au_Lname Au_Fname
---- -------------- -----------
1 Bennet Abraham
2 Blotchet-Halls Reginald
3 Carson Cheryl
4 DeFrance Michel
5 del Castillo Innes
6 Dull Ann
7 Greene Morningstar
8 Green Marjorie
9 Gringlesby Burt
10 Hunter Sheryl
11 Karsen Livia
12 Locksley Charlene
13 MacFeather Stearns
14 McBadden Heather
15 O'Leary Michael
16 Panteley Sylvia
17 Ringer Albert
18 Ringer Anne
19 Smith Meander
20 Straight Dean
21 Stringer Dirk
22 White Johnson
23 Yokomoto Akiko
(23 row(s) affected)
예제 2
이 예제의 경우| • | 판매 부수를 기준으로 서점의 순위를 매깁니다. |
| • | 집합 1은 서점에서 판매한 책 부수입니다(select stor_id, qty=sum(qty) from sales group by stor_id). |
| • | 집합 2는 서점에서 판매한 책 부수입니다(select stor_id, qty=sum(qty) from sales group by stor_id). |
| • | 관계는 "책 부수가 보다 큼(the number of books is greater than)"입니다. |
| • | 중복을 피하기 위해 qty 대신 price*qty를 비교할 수도 있습니다. |
select rank=count(*), s1.stor_id, qty=sum(s1.qty)
from (select stor_id, qty=sum(qty) from sales group by stor_id) s1,
(select stor_id, qty=sum(qty) from sales group by stor_id) s2
where s1.qty >= s2.qty
group by s1.stor_id
order by rank
Rank Stor_Id Qty ---- ------- --- 1 6380 8 2 7896 120 3 8042 240 4 7067 360 5 7066 625 6 7131 780 (6 row(s) affected)참고 Qty 열의 값이 올바르지 않지만 판매 부수를 기준으로 하는 서점 순위는 올바릅니다. 이것이 이 방법의 결함입니다. 결과에 부수가 잘못 나타나는 것에 신경쓰지 않는다면 이 방법을 사용하여 서점의 순위를 반환할 수 있습니다.
SQL Server 2005에서는 다음 코드를 사용합니다.
select row_number() over (order by qty desc) as rank,s1.stor_id,s1.qty
from (select stor_id, qty=sum(qty) from sales group by stor_id) as s1
rank stor_id qty ------- ------- ------ 1 7131 130 2 7066 125 3 7067 90 4 8042 80 5 7896 60 6 6380 8 (6 row(s) affected)참고 SQL Server 2005에서 순위 함수를 사용하면 순위와 부수에 대한 올바른 결과를 얻을 수 있습니다.
예제 3
이 예제의 경우| • | 수익을 기준으로 출판사의 순위를 매깁니다. |
| • | 집합 1은 출판사별 총 판매액입니다. |
| • | 집합 2는 출판사별 총 판매액입니다. |
| • | 관계는 "보다 많은 돈을 벌었음(earns more money than)"입니다. |
select rank=count(*), s1.pub_id, sales=sum(s1.sales)
from (select t.pub_id, sales=sum(s.qty*t.price)
from sales s, titles t
where s.title_id=t.title_id
and t.price is not null
group by t.pub_id) s1,
(select t.pub_id, sales=sum(s.qty*t.price)
from sales s, titles t
where s.title_id=t.title_id
and t.price is not null
group by t.pub_id) s2
where s1.sales>= s2.sales
group by s1.pub_id
order by rank
Rank Pub_Id Sales ---- ------ -------- 1 0736 1,961.85 2 0877 4,256.20 3 1389 7,760.85 (3 row(s) affected)참고 Sales 열의 값이 올바르지 않지만 수익을 기준으로 하는 출판사의 순위는 올바릅니다.
SQL Server 2005에서는 다음 코드를 사용합니다.
select rank() over (order by sales desc) as rank,s1.pub_id,s1.sales
from (select t.pub_id, sales=sum(s.qty*t.price)
from sales s inner join titles t
on s.title_id=t.title_id
where t.price is not null
group by t.pub_id) as s1rank pub_id sales ------- ------ --------- 1 1389 2586.95 2 0877 2128.10 3 0736 1961.85 (3 row(s) affected)참고 SQL Server 2005에서 순위 함수를 사용하면 순위와 수익에 대한 올바른 결과를 얻을 수 있습니다.
단점
| • | Cross Join 때문에 많은 수의 행에는 이 방법을 사용할 수 없습니다. 이 방법은 행이 수십 또는 수백 개인 경우에 적합합니다. 대형 테이블에서는 대규모 검색을 피하기 위해 인덱스를 사용해야 합니다. |
| • | 이 방법은 중복 값을 제대로 처리하지 못합니다. 중복 값을 비교하면 행 번호가 불연속적으로 매겨집니다. 행 번호가 불연속적으로 매겨지는 것을 원하지 않으면 스프레드시트에 결과를 삽입할 때 순위 열을 숨기고 대신 스프레드시트 번호 매기기를 사용하면 됩니다. 참고 SQL Server 2005를 사용하는 경우 row_number() 함수를 사용하여 중복 행에 관계없이 순차적인 행의 번호를 반환할 수 있습니다. |
select rank=count(*), s1.title_id, qty=sum(s1.qty)
from (select title_id, qty=sum(qty) from sales group by title_id) s1,
(select title_id, qty=sum(qty) from sales group by title_id) s2
where s1.qty >= s2.qty
group by s1.title_id
order by rankRank Title_Id Qty ---- -------- ---- 1 MC2222 10 4 BU1032 60 4 BU7832 60 4 PS3333 60 7 PS1372 140 7 TC4203 140 7 TC7777 140 10 BU1111 250 10 PS2106 250 10 PS7777 250 11 PC1035 330 12 BU2075 420 14 MC3021 560 14 TC3218 560 15 PC8888 750 16 PS2091 1728 (16 row(s) affected)
장점
| • | 뷰와 결과 서식에도 이러한 쿼리를 사용할 수 있습니다. |
| • | 순위가 낮은 데이터일수록 더 오른쪽으로 들여 쓸 수 있습니다. |
CREATE VIEW v_pub_rank
AS
select rank=count(*), s1.title_id, qty=sum(s1.qty)
from (select title_id, qty=sum(qty) from sales group by title_id) s1,
(select title_id, qty=sum(qty) from sales group by title_id) s2
where s1.qty >= s2.qty
group by s1.title_id
select publisher=convert(varchar(20),replicate (' ', power(2,rank)) +
pub_id +
replicate(' ', 15-power(2,rank))+': '),
earnings=qty
from v_pub_rank
Publisher Earnings
------------- --------
0736 : 1,961.85
0877 : 4,256.20
1389 : 7,760.85
SQL Server 2005에서는 다음 코드를 사용합니다.CREATE VIEW v_pub_rank
AS
select rank() over (order by sales) as rank,s1.pub_id,s1.sales
from (select t.pub_id, sales=sum(s.qty*t.price)
from sales s, titles t
where s.title_id=t.title_id
and t.price is not null
group by t.pub_id) as s1
GO
select publisher=convert(varchar(20),replicate (' ', power(2,rank)) +
pub_id + replicate(' ', 15-power(2,rank))+': '),
earnings=sales
from v_pub_rank order by rank
GOpublisher earnings
-------------------- ---------------------
0736 : 1961.85
0877 : 2128.10
1389 : 2586.95
(3 row(s) affected)예제 2:
CREATE VIEW v_title_rank
AS
select rank=count(*), s1.title_id, qty=sum(s1.qty)
from (select title_id, qty=sum(qty) from sales group by title_id) s1,
(select title_id, qty=sum(qty) from sales group by title_id) s2
where s1.qty >= s2.qty
group by s1.title_id
select Book=convert(varchar(45),replicate (' ', 2*rank) +
title_id +
replicate(' ', 35-2*rank)+': '),
qty
from v_title_rank
order by rank
Book Qty
------------------------------------------- ----
MC2222 : 10
BU1032 : 60
BU7832 : 60
PS3333 : 60
PS1372 : 140
TC4203 : 140
TC7777 : 140
BU1111 : 250
PS2106 : 250
PS7777 : 250
PC1035 : 330
BU2075 : 420
MC3021 : 560
TC3218 : 560
PC8888 : 750
PS2091 : 1728
(16 row(s) affected)
SQL Server 2005에서는 다음 코드를 사용합니다.CREATE VIEW v_title_rank
AS
select rank() over (order by qty) as rank, s1.title_id,s1.qty
from (select title_id, qty=sum(qty) from sales group by title_id) as s1
GO
select Book=convert(varchar(45),replicate (' ', 2*rank) +
title_id + replicate(' ', 35-2*rank)+': '), qty
from v_title_rank
order by rank
GOBook qty
--------------------------------------------- -----------
MC2222 : 10
BU1032 : 15
BU7832 : 15
PS3333 : 15
TC4203 : 20
TC7777 : 20
PS1372 : 20
BU1111 : 25
PS7777 : 25
PS2106 : 25
PC1035 : 30
BU2075 : 35
MC3021 : 40
TC3218 : 40
PC8888 : 50
PS2091 : 108
(16 row(s) affected)
Microsoft 제품 관련 기술 전문가들과 온라인으로 정보를 교환하시려면 Microsoft 뉴스 그룹 (http://support.microsoft.com/newsgroups/default.aspx)에 참여하시기 바랍니다.
본 문서의 정보는 다음의 제품에 적용됩니다.
| • | Microsoft SQL Server 2000 Standard Edition |
| • | Microsoft SQL Server 4.21a Standard Edition |
| • | Microsoft SQL Server 6.0 Standard Edition |
| • | Microsoft SQL Server 6.5 Standard Edition |
| • | Microsoft SQL Server 7.0 Standard Edition |
| • | Microsoft SQL Server 2005 Standard Edition |
| • | Microsoft SQL Server 2005 Developer Edition |
| • | Microsoft SQL 2005 Server Enterprise |
| • | Microsoft SQL Server 2005 Express Edition |
| • | Microsoft SQL 2005 Server Workgroup |
키워드: |
kbhowtomaster KB186133 |
