SQL 查询查找比赛中的获胜者

Question

我有一个查询返回结果如下：

  Race   | Candidate | Total Votes | MaxNoOfWinners
    ---------------------------------------------------
    1      | 1         | 5000        | 3
    1      | 2         | 6700        | 3
    2      | 1         | 100         | 3
    2      | 2         | 200         | 3
    2      | 3         | 300         | 3
    2      | 4         | 400         | 3
    ...

我想知道是否可以编写一个查询来仅返回特定比赛的获胜者（基于 MaxNoOfWinners 和 TotalVotes）。所以对于上述我只会回来

Race   | Candidate | Total Votes | MaxNoOfWinners
---------------------------------------------------
1      | 1         | 5000        | 3
1      | 2         | 6700        | 3
2      | 2         | 200         | 3
2      | 3         | 300         | 3
2      | 4         | 400         | 3
...

Answer 1

这是一个解决方案...我没有测试，所以可能有拼写错误。这个想法是使用 SQL Server 的 RANK() 函数根据选票按种族给出排名，不包括那些不符合条件的人。请注意，使用 RANK() 而不是 ROW_NUMBER() 将在结果中包含平局。

WITH RankedResult AS
(
  SELECT Race, Candidate, [Total Votes], MaxNoOfWinners, RANK ( )  OVER (PARTITION BY Race ORDER BY [Total Votes] DESC) AS aRank
  FROM Results
)
SELECT Race, Candidate, [Total Votes], MaxNoOfWinners
FROM RankedResult
WHERE aRANK <= MaxNumberOfWinners

Answer 2

这是一个完整的工作示例，假设有两个表进行竞赛和候选，

Create Table #Race(Race_id int , MaxNoOfwinners int ) 

INSERT INTO #Race (Race_id , MaxNoOfwinners)
VALUES (1,3), 
       (2,3),
       (3,1)


CREATE TABLE #Candidate (CandidateID int , Race_ID int , Total_Votes int )
INSERT INTO #Candidate (CandidateID  , Race_ID  , Total_Votes  )
VALUES (1,1,5000),
        (2,1,6700),
        (1,2,100),
        (2,2,200),
        (3,2,300),       
        (4,2,400),        
        (1,3,42),
        (2,3,22)


;WITH CTE as (
SELECT 
    RANK() OVER(PARTITION BY race_id ORDER BY  race_id, total_votes DESC ) num,
    CandidateID  , Race_ID  , Total_Votes
From 
    #Candidate)
SELECT * FROM cte inner join #Race r
on CTE.Race_ID = r.Race_id
and num <= r.MaxNoOfwinners

DROP TABLE #Race
DROP TABLE #Candidate

结果如下

num                  CandidateID Race_ID     Total_Votes Race_id     MaxNoOfwinners
-------------------- ----------- ----------- ----------- ----------- --------------
1                    2           1           6700        1           3
2                    1           1           5000        1           3
1                    4           2           400         2           3
2                    3           2           300         2           3
3                    2           2           200         2           3
1                    1           3           42          3           1

Answer 3

WITH q0 AS (SELECT qry.*, rank() AS r 
   FROM qry OVER (PARTITION BY race ORDER BY total_votes DESC))
SELECT q0.race, q0.candidate, q0.total_votes FROM q0 WHERE r<=q0.max_winners;