[SQL] 사용 액세스 SQL은 그룹화 된 순위를 할 수

사용 액세스 SQL은 그룹화 된 순위를 할 수

어떻게 (관계를 포함하여) 나는 부서로 그룹화 # 고객이 영업 사원을 평가합니까?

예를 들어,이 테이블 주어, 나는 오른쪽의 순위 열을 만들려고합니다. 어떻게 Access에서이 작업을 수행해야합니까? 영업 사원이 부서 #Customers 순위 빌 DeptA 20 일 테드 DeptA 30 2 제인 DeptA 40 세 빌 DeptB 50 일 메리 DeptB (60) 2

나는 이미이 SQL 코드로 순위 간단한 작업을 수행하는 방법을 알고있다. 하지만 그룹화 받아이 재 작업하는 방법을 모르겠어요.

Select Count(*) from [Tbl] Where [#Customers] <  [Tblx]![#Customers] )+1

또한, 거기에이 사용하는 SQL 서버 순위 () 함수에 대한 답변을 많이하지만 난 Access에서이 작업을 수행해야합니다. 제안, 제발?

해결법

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

   1.

   SELECT *, (select count(*) from tbl as tbl2 where
   tbl.customers > tbl2.customers and tbl.dept = tbl2.dept) + 1 as rank from tbl
   

   그냥 하위 쿼리에 부서 필드를 추가 ...

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

   2.하위 쿼리 그레이트 솔루션! 거대한 레코드를 제외하고 하위 쿼리 솔루션은 매우 느리게 가져옵니다. 그것의 더 나은 (빠르게) 자체에서, folowing 솔루션 봐 가입하세요 사용 : 자기가 가입

   하위 쿼리 그레이트 솔루션! 거대한 레코드를 제외하고 하위 쿼리 솔루션은 매우 느리게 가져옵니다. 그것의 더 나은 (빠르게) 자체에서, folowing 솔루션 봐 가입하세요 사용 : 자기가 가입

   SELECT tbl1.SalesPerson , count(*) AS Rank 
   FROM tbl AS tbl1 INNER JOIN tbl AS tbl2 ON tbl1.DEPT = tbl2.DEPT 
       AND tbl1.#Customers < tbl2.#Customers 
   GROUP BY tbl1.SalesPerson 
   

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

   3.나는이 오래된 스레드 알고 있습니다. 나는 매우 비슷한 문제에 많은 시간을 보냈다 크게 여기에 주어진 전 답변으로 도움이 된 이후, 나는 내가 훨씬 더 빠른 방법이 발견 한 내용을 공유하고 싶습니다. (가 더 복잡하다, 조심하십시오.)

   나는이 오래된 스레드 알고 있습니다. 나는 매우 비슷한 문제에 많은 시간을 보냈다 크게 여기에 주어진 전 답변으로 도움이 된 이후, 나는 내가 훨씬 더 빠른 방법이 발견 한 내용을 공유하고 싶습니다. (가 더 복잡하다, 조심하십시오.)

   먼저 "Individualizer"라는 다른 테이블을합니다. 이것은-가장 높은 순위 - 그 - 당신 - 필요 통해 숫자 1의 목록이 포함 된 하나 개의 필드를해야합니다.

   옆에 VBA 모듈을 만들고 그것으로이 붙여 넣습니다 :

   'Global Declarations Section.
   Option Explicit
   Global Cntr
   
   '*************************************************************
   ' Function:  Qcntr()
   '
   ' Purpose: This function will increment and return a dynamic
   ' counter. This function should be called from a query.
   '*************************************************************
   
   Function QCntr(x) As Long
      Cntr = Cntr + 1
      QCntr = Cntr
   End Function
   
   '**************************************************************
   ' Function:  SetToZero()
   '
   ' Purpose: This function will reset the global Cntr to 0. This
   ' function should be called each time before running a query
   ' containing the Qcntr() function.
   '**************************************************************
   
   Function SetToZero()
      Cntr = 0
   End Function
   

   Module1의로 저장합니다.

   다음으로,이 같은 Query1을 만들기 :

   SELECT Table1.Dept, Count(Table1.Salesperson) AS CountOfSalesperson
   FROM Table1
   GROUP BY Table1.Dept;
   

   이 같은 QUERY2라는 MakeTable 쿼리를 만듭니다 :

   SELECT SetToZero() AS Expr1, QCntr([ID]) AS Rank, Query1.Dept, 
   Query1.CountOfSalesperson, Individualizer.ID 
   INTO Qtable1
   FROM Query1 
   INNER JOIN Individualizer 
      ON Query1.CountOfSalesperson >= Individualizer.ID;
   

   이 같은 QUERY3라는 또 다른 MakeTable 쿼리를 만듭니다 :

   SELECT SetToZero() AS Expr1, QCntr([Identifier]) AS Rank, 
   [Salesperson] & [Dept] & [#Customers] AS Identifier, Table1.Salesperson, 
   Table1.Dept, Table1.[#Customers] 
   INTO Qtable2
   FROM Table1;
   

   당신이 유일하게 모든 행을 식별하는 이미 다른 필드가있는 경우는 식별자 필드를 만들 필요가 없습니다 것입니다.

   쿼리 실행 및 쿼리 테이블을 만들 수 있습니다. 이 같은 쿼리라는 이름의 네 번째 쿼리를 만듭니다 :

   SELECT Qtable2.Salesperson, Qtable2.Dept, Qtable2.[#Customers], Qtable1.ID AS Rank
   FROM Qtable1 
   INNER JOIN Qtable2 ON Qtable1.Rank = Qtable2.Rank;
   

   Query4 당신이 찾고있는 결과를 반환합니다.

   실제로, 당신은 편리한 위치에 배치 버튼에서 해당 함수를 호출 한 후 QUERY2 및 QUERY3를 실행하는 VBA 함수를 작성 할 것입니다.

   지금은 말도 안되게 당신이 준 예를 들어 복잡한이 소리를 알고있다. 그러나 현실에서, 나는 당신의 테이블이 더이보다 복잡 확신합니다. 바라건대 내 예는 실제 상황에 적용 할 수 있습니다. (: 262 개 레코드가 하위 쿼리 방법으로 평가하여 12,000 기록을 6 초 이상 1 분 비교에로) 12,000 레코드 내 데이터베이스에서이 방법은 가장 빠른 FAR입니다.

   이 순위 방법은 당신이 즉시 결과를 출력하지 않는 한 테이블에 쓸모가 있기 때문에 나를 위해 진짜 비밀은 MakeTable 질의했다. 그러나, 이것은에 적용 할 수있는 상황을 제한한다.

   추신 내 데이터베이스에 나는 테이블에서 직접 결과를 당기는되지 않았 음을 언급하는 것을 잊었다. 그들이 평가 될 필요가 전에 기록은 이미 쿼리와 여러 계산의 캐릭터를 통과했다. 이것은 아마도 내 상황에서 두 가지 방법 사이의 속도에서 큰 차이에 크게 기여했다. 당신이 테이블에서 직접 기록을 당기는 경우에, 당신은 거의 큰 개선을 통지하지 않을 수 있습니다.

4. ==============================

   4.당신은 몇 가지 계산을 할 필요가있다. 나는 일반적으로 카운터 필드의 조합과 "오프셋"필드에 활용합니다. 이 같은 모양의 테이블을 목표로하고 (#Customers이 필요하지 않습니다,하지만 당신은 제대로하고있는 것을 당신에게 시각을 줄 것이다) :

   당신은 몇 가지 계산을 할 필요가있다. 나는 일반적으로 카운터 필드의 조합과 "오프셋"필드에 활용합니다. 이 같은 모양의 테이블을 목표로하고 (#Customers이 필요하지 않습니다,하지만 당신은 제대로하고있는 것을 당신에게 시각을 줄 것이다) :

   SalesPerson Dept #Customers Ctr Offset
   Bill        DeptA     20    1   1
   Ted         DeptA     30    2   1
   Jane        DeptA     40    3   1
   Bill        DeptB     50    4   4
   Mary        DeptB     60    5   4
   

   그래서, 순위를주고, 당신이 할 것 [센트로] - [오프셋] +1 AS 순위

5. ==============================

   5.이 추가 및 기타 관련 액세스 랭킹이나하는 순위 타이 브레이커 기본적인 방법 당신의 FROM 절에서 테이블하지만 중 하나 크로스 탭 쿼리입니다 쿼리를 포함하지 일어나는 경우 크로스 탭 쿼리를 수행 할 수 없습니다 순위 액세스의 다른 버전, 또는 다른 곳에서 크로스 탭 쿼리를 포함하는 쿼리.

   이 추가 및 기타 관련 액세스 랭킹이나하는 순위 타이 브레이커 기본적인 방법 당신의 FROM 절에서 테이블하지만 중 하나 크로스 탭 쿼리입니다 쿼리를 포함하지 일어나는 경우 크로스 탭 쿼리를 수행 할 수 없습니다 순위 액세스의 다른 버전, 또는 다른 곳에서 크로스 탭 쿼리를 포함하는 쿼리.

   SELECT 문 내의 SELECT 문 (서브 쿼리)이 사용되는 경우 위에서 언급 한 코드,

    "SELECT *, (select count(*) from tbl as tbl2 where tbl.customers > tbl2.customers and tbl.dept = tbl2.dept) + 1 as rank from tbl"
   

   작동하지 않으며 항상 "tbl.customers이> tbl2.customers는"찾을 수없는 코드의 부분에 오류를 표현하는 실패합니다.

   과거의 프로젝트 내 상황에서, 나는 테이블 대신 쿼리를 참조하고 해당 쿼리 내에서 나는 이렇게 실패하고 오류를 생산하는 크로스 탭 쿼리를 참조했다. 내가 먼저 크로스 탭 쿼리에서 테이블을 생성하여이 문제를 해결 할 수 있었다, 나는 FROM 절에서 새로 만든 테이블을 참조 할 때, 나를 위해 일하기 시작했다.

   그래서 마지막으로, 일반적으로 당신이, 당신이 대신 테이블의 쿼리를 참조하는 경우에로 자르 순위를 할 이전에 위의 공유 된 것과 SELECT 문의 FROM 절에 쿼리 나 테이블을 참조 할 수 있지만, 그 쿼리는 안된다 크로스 탭 쿼리를 수 또는 크로스 탭 쿼리 또 다른 쿼리를 참조합니다.

   이 위의 문을 참조하는 일 경우 가능한 이유를 찾고 문제를했을 수 있습니다 다른 사람을 돕고 당신이 당신의 자신의 프로젝트 내에서 절에서의 테이블을 참조하지 않습니다 바랍니다. 또한, Access에서 크로스 탭 쿼리와 별명에 하위 쿼리를 수행하는 것은 아마도 경우 / 가능 하나되도록 멀리 벗어나지 좋은 아이디어 나 가장 좋은 방법은 아니다.

   당신이 액세스 경유 쿼리 편집기에서 스크롤 마우스의 사용을 허용 것이라고이 유용하고, 소원을 발견하면 나에게 같은하시기 바랍니다 제공합니다.

6. ==============================

   6.나는 일반적으로 여기에서 정보와 아이디어를 선택하고 때때로에서 놀라운 일들을 구축 끝!

   나는 일반적으로 여기에서 정보와 아이디어를 선택하고 때때로에서 놀라운 일들을 구축 끝!

   오늘, 나는 일주일에 데려다 복잡한 있도록 뭔가를 무슨 일이 있었는지 예상하지 않았다, 내가 Access에서 내 능력의 최선을 데이터의 순위 땜질 한 (우물이 지난 일주일 동안 가정 해 봅시다) 그것을 알아낼 수 있습니다! 나는 두 가지 주요 사이트에서 titbits을 집어 :

   글쎄, 난 부분적으로 제가했던 것은 나에게 내가 거의 모든 상황에서 당신을 도울 수 생각하는 순위의 가장 복잡한 중 하나이기 때문에, 언급 된 참여자에게 감사하고, 내가 다른 사람으로부터 혜택 때문에, 내가하고 싶다고 이야기했다 내가 아니라 다른 사람을 도움이 될 무슨 희망이 여기에 공유 할 수 있습니다.

   여기 내 테이블 구조를 게시 할 수 없습니다임을 용서, 그것은 관련 테이블의 많은입니다. 난 단지 당신이 쿼리의 종류에 결국 테이블을 개발할 수 있습니다 당신에게 필요한 그렇다면, 쿼리를 게시 할 예정입니다. 그러나 여기 내 시나리오는 다음과 같습니다

   당신은 학교에서 학생들이있다. 클래스가 너무 작을 때 그들은 하나 스트림 A 또는 B, 또는 없음에있을 수 있습니다, 4 클래스 1을 통해 이동합니다. 그들은 각 테이크 4 개 시험 (이 부분은 지금 중요하지 않다), 내 경우에 대한 총 점수를 얻을 수 있도록. 이게 다예요. 허 ??

   확인. 순위들을 이런 식으로 수 있습니다 :

   우리는의 순위를 알고 싶어요

   • 지금까지이 학교를 통과하는 모든 학생 (가장 이제까지 학생)

   • 특정 학년의 모든 학생 (올해의 학생)

   • 특정 클래스의 학생 (그러나 모든 클래스를 통과 한 것 학생, 다른 년간 해당 클래스의 각 그래서 기본적으로 그 / 그녀의 순위를 기억)이 성적표에 나타납니다 순위 보통이다

   자신의 스트림에서 • 학생 (주석이 적용 위)

   • 나는 우리가 각 카테고리에서이 학생을 평가하는에 인구를 알고 싶습니다 또한 것

   ... 모두 하나 개의 테이블 / 쿼리. 지금 당신은 요점을 파악?

   (나에게 영상을 제공하고 나중에 오른쪽으로해야하는 코드의 양을 줄이기 위해 데이터베이스 / 쿼리 내 '프로그래밍'의 많이 할 I 일반적으로처럼. 나는 실제로 :) 내 응용 프로그램에서이 쿼리를 사용하지 않습니다, 하지만 그것은 나를 알고하자 어디 쿼리가에서 와서 내 매개 변수를 보내는 방법을 어떤 결과 것은 내 RDLC 기대합니다)

   당신이 여기있다, 걱정하지 마세요 :

   SELECT Sc.StudentID, Sc.StudentName, Sc.Mark, 
   (SELECT COUNT(Sch.Mark) FROM [StudentScoreRankTermQ] AS Sch WHERE (Sch.Mark >= Sc.Mark)) AS SchoolRank, 
   (SELECT Count(s.StudentID) FROM StudentScoreRankTermQ AS s) As SchoolTotal, 
   (SELECT COUNT(Yr.Mark) FROM [StudentScoreRankTermQ] AS Yr WHERE (Yr.Mark >= Sc.Mark) AND (Yr.YearID = Sc.YearID) ) AS YearRank, 
   (SELECT COUNT(StudentID) FROM StudentScoreRankTermQ AS Yt WHERE (Yt.YearID = Sc.YearID) ) AS YearTotal, 
   (SELECT COUNT(Cl.Mark) FROM [StudentScoreRankTermQ] AS Cl WHERE (Cl.Mark >= Sc.Mark) AND (Cl.YearID = Sc.YearID) AND (Cl.TermID = Sc.TermID)  AND (Cl.ClassID=Sc.ClassID)) AS ClassRank, 
   (SELECT COUNT(StudentID) FROM StudentScoreRankTermQ AS C WHERE (C.YearID = Sc.YearID) AND (C.TermID = Sc.TermID) AND (C.ClassID = Sc.ClassID) ) AS ClassTotal, 
   (SELECT COUNT(Str.Mark) FROM [StudentScoreRankTermQ] AS Str WHERE (Str.Mark >= Sc.Mark)  AND (Str.YearID = Sc.YearID) AND (Str.TermID = Sc.TermID)  AND (Str.ClassID=Sc.ClassID) AND (Str.StreamID = Sc.StreamID) ) AS StreamRank, 
   (SELECT COUNT(StudentID) FROM StudentScoreRankTermQ AS St WHERE (St.YearID = Sc.YearID) AND (St.TermID = Sc.TermID)  AND (St.ClassID = Sc.ClassID) AND (St.StreamID = Sc.StreamID) ) AS StreamTotal, 
   Sc.CalendarYear, Sc.Term, Sc.ClassNo, Sc.Stream, Sc.StreamID, Sc.YearID, Sc.TermID, Sc.ClassID
   FROM StudentScoreRankTermQ AS Sc
   ORDER BY Sc.Mark DESC;
   

   당신이 뭔가를 얻어야한다 :

   +-----------+-------------+------+------------+-------------+----------+-----------+-----------+------------+------------+-------------+------+------+-------+--------+
   | StudentID | StudentName | Mark | SchoolRank | SchoolTotal | YearRank | YearTotal | ClassRank | ClassTotal | StreamRank | StreamTotal | Year | Term | Class | Stream |
   +-----------+-------------+------+------------+-------------+----------+-----------+-----------+------------+------------+-------------+------+------+-------+--------+
   | 1         | Jane        | 200  | 1          | 20          | 2        | 12        | 1         | 9          | 1          | 5           | 2017 | I    | 2     | A      |
   | 2         | Tom         | 199  | 2          | 20          | 1        | 12        | 3         | 9          | 1          | 4           | 2016 | I    | 1     | B      |
   +-----------+-------------+------+------------+-------------+----------+-----------+-----------+------------+------------+-------------+------+------+-------+--------+
   

   분리기를 사용하여 | 결과 테이블을 재구성하는

   테이블에 대한 그냥 생각이, 각 학생이 수업과 관련이있을 것입니다. 각 클래스는 년에 관한 것이다. 각 스트림 클래스에 관한 것이다. 각 용어는 일년에 관한 것이다. 각 시험은 용어와 학생 및 클래스와 일년에 관한 학생은 2017 년 수준의 (b)에에 클래스 2016에서 1A 및 이동에 등을 할 수 있습니다 ...

   날이 베타 결과, 나는 그것이 충분히 테스트하지 않았으며 나는 아직 성능을보기 위해 많은 양의 데이터를 생성 할 수있는 기회를하지 않아도 추가 할 수 있습니다. 그것에 내 첫 눈은 좋은 것을 저에게 말했다. 당신이 이유 또는 내 방식을 지적하고 싶어요 경고를 찾으면 내가 계속 학습 할 수 있도록 그래서, 의견 그렇게하십시오!

from https://stackoverflow.com/questions/4463116/use-access-sql-to-do-a-grouped-ranking by cc-by-sa and MIT license