[SQL] 테이블의 각 그룹에 대한 상위 N 개의 행을 선택한

테이블의 각 그룹에 대한 상위 N 개의 행을 선택한

나는 "테이블의 각 그룹에 대해 상위 N 행을 선택"에 대한 매우 일반적인 문제에 직면하고있다.

ID, 이름, hair_colour, 점수 열이있는 테이블을 고려하십시오.

나는 각각의 머리 색깔을 위해, 내가 3 득점 이름을 맨 얻을 수있는 결과 집합은합니다.

이 문제를 해결하기 위해 나는 릭 오스본의 블로그 게시물에서 "SQL-점점 정상-N-행를 위해 A-그룹화 쿼리를"필요 정확히 있어요

내 점수가 동일한 경우 예상대로 그 해결책은 작동하지 않습니다.

다음과 같은 예에서 전술 한 결과.

 id  name  hair  score  ranknum
---------------------------------
 12  Kit    Blonde  10  1
  9  Becca  Blonde  9  2
  8  Katie  Blonde  8  3
  3  Sarah  Brunette 10  1    
  4  Deborah Brunette 9  2 - ------- - - > if
  1  Kim  Brunette 8  3

이것은 또한 사라와 같은 동일한 점수 (10)가있는 경우 행 4 데보라 갈색 머리 9 2. 고려, 다음 ranknum은 머리의 "갈색 머리"유형 2,2,3 될 것입니다.

이에 대한 해결책은 무엇입니까?

해결법

1. ==============================

   1.당신은 SQL 서버 2005 이상을 사용하는 경우, 당신은 순위 기능을 사용할 수 있으며, CTE는이를 달성하기 위해 :

   당신은 SQL 서버 2005 이상을 사용하는 경우, 당신은 순위 기능을 사용할 수 있으며, CTE는이를 달성하기 위해 :

   ;WITH HairColors AS
   (SELECT id, name, hair, score, 
           ROW_NUMBER() OVER(PARTITION BY hair ORDER BY score DESC) as 'RowNum'
   )
   SELECT id, name, hair, score
   FROM HairColors
   WHERE RowNum <= 3
   

   이 CTE 것 "파티션"머리 컬럼의 값을 기준으로 데이터, 각 파티션 점수 (내림차순)에 의한 위해 다음과 행 번호를 가져옵니다; 각 파티션에 대한 가장 높은 점수 등 후 1, 2

   그래서 당신은 각 그룹의 TOP 3 원하는 경우, 3 이하 (1, 2, 3)의 ROWNUM을 가지고있는 CTE에서 만 행을 선택 -> 당신은이 간다!

2. ==============================

   2.알고리즘이 순위와 함께 제공되는 방법은 순위를 생성하기 위해,에 또는 문제의 소녀보다 더 큰 동일 점수로 제품 간 행의 수를 계산하는 것입니다. 따라서 당신에 대해 얘기하고 문제가있는 경우에, 사라의 그리드는 같을 것이다

   알고리즘이 순위와 함께 제공되는 방법은 순위를 생성하기 위해,에 또는 문제의 소녀보다 더 큰 동일 점수로 제품 간 행의 수를 계산하는 것입니다. 따라서 당신에 대해 얘기하고 문제가있는 경우에, 사라의 그리드는 같을 것이다

   a.name | a.score | b.name  | b.score
   -------+---------+---------+--------
   Sarah  | 9       | Sarah   | 9
   Sarah  | 9       | Deborah | 9
   

   두 여자가 여기 2의 순위를 얻을 이유와 유사하게 데보라을 위해하는 것이다.

   문제는 넥타이있을 때, 모든 여자 때문에 당신이 그들을 대신 가장 높은 값을 할 것이 수,에 묶여 범위에서 가장 낮은 값을 가지고 있다는 것입니다. 나는 간단한 변화가이 문제를 해결할 수 있다고 생각합니다 :

   대신에보다 큰 또는 동등한 비교, 엄격하게 더 나은 여자의 수를 계산하는 비교보다 더 큰-엄격한을 사용합니다. 그런 다음, 그 하나를 추가하면 (적절한 관계를 다루는 것) 당신의 순위가 있습니다. 그래서에게 내부 선택은 다음과 같습니다

   SELECT a.id, COUNT(*) + 1 AS ranknum
   FROM girl AS a
     INNER JOIN girl AS b ON (a.hair = b.hair) AND (a.score < b.score)
   GROUP BY a.id
   HAVING COUNT(*) <= 3
   

   사람이 내 통지를 탈출이 접근 방식에 문제를 볼 수 있을까요?

3. ==============================

   3.제대로 된 핸들 영업 이익의 문제를 선택이 화합물을 사용하여

   제대로 된 핸들 영업 이익의 문제를 선택이 화합물을 사용하여

   SELECT g.* FROM girls as g
   WHERE g.score > IFNULL( (SELECT g2.score FROM girls as g2
                   WHERE g.hair=g2.hair ORDER BY g2.score DESC LIMIT 3,1), 0)
   

   참고 테이블 여자는 머리의 어떤 종류의 적은 행이 때 우리는 SQL 답에 표시 할 핸들 경우에 여기 IFNULL를 사용하는 데 필요한 (OP의 경우는 3 개 항목)입니다.

from https://stackoverflow.com/questions/3998529/selecting-top-n-rows-for-each-group-in-a-table by cc-by-sa and MIT license