Detect TOP 5 from B for each record in A

问题:

I'm creating an online competition where entrants are allocated to a judge, and this judge decides if they proceed to the next round.

Once the first round closes, the second round is prepared in the database as follows:

Each contestant/entrant who is put through to round 2 is allocated to 5 judges. These judges should be allocated based on how many contestants are already allocated to them. In other words, each judge should have a similar number of people to judge in round two.

At present I have the following SQL statement in a stored procedure, which is called 1 time when the competition is moved to round 2:

INSERT INTO recEntrantStatus (entrantId, roundId, judgeId, notified, voted, enterNextRound)
SELECT 
    r.entrantId, 
    (@input + 1), 
    j.Id /*Now getting tblJudges Id*/,  -- fixed name
    0, 
    0, 
    0
FROM recEntrantStatus r
-- Get all of the judges
    CROSS JOIN (SELECT TOP(5) MEM.Id, ISNULL(ES.EntrantCount, 0) EntrantCount
                FROM recMembers MEM
                LEFT JOIN
                (
                    SELECT judgeid, COUNT(judgeid) AS 'EntrantCount' -- name this
                    FROM recEntrantStatus
                    WHERE roundId = 1
                    GROUP BY judgeid
                ) ES ON MEM.Id = ES.judgeid
                WHERE MEM.Privilege = 2
                ORDER BY EntrantCount ) AS j
WHERE r.roundId = @input
  AND r.voted = 1
  AND r.enterNextround = 1

However, this only calculates the 5 judges on the one occasion and allocates these same 5 judges to every contestant. What I actually need however is for the 5 judges to be recalculated for every contestant moved into round 2.

Any suggestions for this would be appreciated.

---

EXTRA CLARIFICATION

So, the aim is to calculate how many records each judge has for the current round(roundId), then calculate/select the 5 judges with the fewest records. Then it should create 5 new records in the 'recEntrantStatus' table for the entrantId, with each record being the same except for the 'judgeId' column which will contain the relevant idea of one of the 5 judges. With five records in total, that means each contestant/entrant will have a record for each of 5 judges for the current round.

Then the process will be repeated for the next identified entrant/contestant(r.entrantId). Since we just added 5 new records, the 5 judges with the least number of contestants/records will have changed. So we determine who these are before creating a new row in the db for each of these 5 judges/ the contestant.

---

SQL FIDDLE

http://sqlfiddle.com/#!6/426d5

回答1:

Ill admit I might be mis-reading the request here (somewhat tired)

As this is effectively a calculation based on an ongoing changing dataset its would not to me seem to be a set based operation..

As much as Im loathe to recommend Cursors I feel that this is probably the correct approach here

Cursor each row from recEntrantStatus that needs moving up a round and perform the insert based on that row in turn ...

EDIT::

declare @entrant bigint

DECLARE entrant_cursor CURSOR FOR 
SELECT entrantID
FROM  recEntrantStatus r
WHERE r.roundId = @input
AND r.voted = 1
AND r.enterNextround = 1

OPEN entrant_cursor



FETCH NEXT FROM entrant_cursor 
INTO @entrant

 WHILE @@FETCH_STATUS = 0
   BEGIN
    INSERT INTO recEntrantStatus (entrantId, roundId, judgeId, notified, voted, enterNextRound)
SELECT 
    r.entrantId, 
  (@input + 1), 
j.Id /*Now getting tblJudges Id*/,  -- fixed name
0, 
0, 
0
FROM recEntrantStatus r
-- Get all of the judges
CROSS JOIN (SELECT TOP(5) MEM.Id, ISNULL(ES.EntrantCount, 0) EntrantCount
            FROM recMembers MEM
            LEFT JOIN
            (
                SELECT judgeid, COUNT(judgeid) AS 'EntrantCount' -- name this
                FROM recEntrantStatus
                WHERE roundId = 1
                GROUP BY judgeid
            ) ES ON MEM.Id = ES.judgeid
            WHERE MEM.Privilege = 2
            ORDER BY EntrantCount ) AS j
WHERE r.roundId = @input
AND r.voted = 1
AND r.enterNextround = 1
and r.entrantID = @entrant

FETCH NEXT FROM entrant_cursor 
INTO @entrant

END 
CLOSE entrant_cursor;
DEALLOCATE entrant_cursor;

Apologies for the formatting ...

MS Link for cursors https://msdn.microsoft.com/en-GB/library/ms180169.aspx

EDIT 2 :

Ok there was a slight mistake in your original SQL change the line from my solution: ORDER BY EntrantCount ) AS j

to ORDER BY ISNULL(ES.EntrantCount, 0) ) AS j

The reason for this is you were ordering by the underlying EntrantCount not the one where you changed nulls to 0