현재 오프라인 상태입니다. 인터넷에 다시 연결하기를 기다리고 있습니다.

SELECT Transact-SQL 문에서 동적으로 행 번호를 매기는 방법

요약
이 문서에서는 유연성 있는 방법을 사용하여 SELECT 문을 수행할 때 동적으로 행의 순위를 매기는 방법을 설명합니다. 이 방법은 유일한 해결책일 수 있으며 프로시저 해결책보다 빠릅니다. 행 번호 매기기나 순위 매기기는 전형적인 프로시저 문제입니다. 이 해결책은 대개 루프와 임시 테이블을 기반으로 하므로 SQL Server 루프와 커서를 사용합니다. 이 기술은 자동 조인에 기반을 두고 있습니다. 선택되는 관계는 대개 "보다 큼"입니다. 특정 데이터 집합을 자신과 비교할 때 해당 집합의 각 요소가 "보다 큼" 관계를 충족하는 횟수를 계산합니다.

참고 다음 예제는 pubs 데이터베이스를 기반으로 합니다. 기본적으로 Northwind 예제 데이터베이스와 pubs 예제 데이터베이스는 SQL Server 2005에 설치되지 않습니다. 이러한 데이터베이스는 Microsoft 다운로드 센터에서 다운로드할 수 있습니다. 자세한 내용은 다음 Microsoft 웹 사이트를 참조하십시오. SQL2000SampleDb.msi를 다운로드한 후 SQL2000SampleDb.msi를 두 번 눌러 예제 데이터베이스 스크립트를 추출합니다. 기본적으로 SQL2000SampleDb.msi는 데이터베이스 스크립트와 추가 정보 파일을 다음 폴더에 추출합니다.
C:\SQL Server 2000 Sample Databases
추가 정보 파일의 지침에 따라 설치 스크립트를 실행합니다.

SQL Server 2005를 사용하는 경우

SQL Server 2005에서 새 기능으로 제공되는 순위 함수를 사용하는 것이 좋습니다. 순위 함수에 대한 자세한 내용은 다음 MSDN(Microsoft Developer Network) 웹 사이트를 참조하십시오. 맨 위로

예제 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				
SQL Server 2005에서는 다음 코드를 사용합니다.
   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.qtyfrom (select stor_id, qty=sum(qty) from sales group by stor_id) as s1
결과:
rank     stor_id  qty-------  -------  ------1        7131     1302        7066     1253        7067     904        8042     805        7896     606        6380     8(6 row(s) affected)
참고 SQL Server 2005에서 순위 함수를 사용하면 순위와 부수에 대한 올바른 결과를 얻을 수 있습니다.

맨 위로

예제 3

이 예제의 경우
  • 수익을 기준으로 출판사의 순위를 매깁니다.
  • 집합 1은 출판사의 총 판매액입니다.
            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					
  • 집합 2는 출판사의 총 판매액입니다.
            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					
  • 관계는 "보다 많은 돈을 벌었음(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 s1
결과:
rank     pub_id  sales-------  ------  ---------1        1389    2586.952        0877    2128.103        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 rank
결과:
   Rank    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)				
맨 위로

장점

  • 뷰와 결과 서식에도 이러한 쿼리를 사용할 수 있습니다.
  • 순위가 낮은 데이터일수록 더 오른쪽으로 들여 쓸 수 있습니다.
예제 1:
   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_rankASselect 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 s1GOselect  publisher=convert(varchar(20),replicate (' ', power(2,rank)) +	pub_id + replicate(' ', 15-power(2,rank))+': '),	earnings=salesfrom v_pub_rank order by rankGO
결과:
publisher            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_rankASselect rank() over (order by qty) as rank, s1.title_id,s1.qtyfrom (select title_id, qty=sum(qty) from sales group by title_id) as s1GOselect Book=convert(varchar(45),replicate (' ', 2*rank) +title_id + replicate(' ', 35-2*rank)+': '), qtyfrom v_title_rankorder by rankGO
결과:
Book                                          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) 
맨 위로
prodsql join rank formatting query
속성

문서 ID: 186133 - 마지막 검토: 01/30/2014 11:15:00 - 수정: 6.0

  • 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 Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • kbsqlsetup kbhowtomaster KB186133
피드백