PHP / MySQL从第二个表中选择基于X行中1个的列表[关闭]

I have two tables

  • Player. fields: id, name
  • Event. fields: event_id, player_id, rank

This is a scoring / qualifier list query where I'm asked to provide a list of Players where 1 in 15 in an event qualify. I.e. If there are 15-29 1 person will qualify, 30-44 2 people etc. Based on the Rank 1 being first place, 2 second, etc.

I'm asked to provide a list of Players where they have qualified.
The other requirement is that the Player must have played in a minimum of 20 events.

I'm aware of "top" however I can't see how I could have the top value dynamic based on the number of players in the event.

Player Table

ID NAME
1 player1
2 player2
3 player3
4 player4
5 player5
6 player6
7 player7
8 player8
9 player9
10 player10
11 player11
12 player12
13 player13
14 player14
15 player15
16 player16
17 player17
18 player18
19 player19
20 player20
21 player21
22 player22
23 player23
24 player24
25 player25
26 player26
27 player27
28 player28
29 player29
30 player30
31 player31
32 player32
33 player33
34 player34
35 player35
36 player36
37 player37
38 player38
39 player39
40 player40
41 player41
42 player42
43 player43
44 player44
45 player45
46 player46
47 player47

Event Table

EVENTID PLAYERID RANK
1 2  10
1 10 9
1 3  2 
1 1  15
1 4  1
1 5  16
1 41 4
1 30 3
1 6  6
1 11 7
1 12 5
1 13 8
1 30 11
1 29 14
1 28 13
1 7  12
2 1  1
2 2  31
2 3  30
2 4  29
2 5  24 
2 6  3 
2 7  4
2 8  5
2 9  28
2 10 27
2 11 26
2 12 6
2 13 7 
2 14 8
2 15 9
2 16 10
2 17 11
2 18 12
2 40 13
2 41 14
2 18 15
2 20 16
2 21 17
2 22 18
2 23 19
2 24 20
2 25 21
2 26 22
2 30 23
2 31 2
2 32 25

Expected Result (without 20 games filter)

player4
player1
player31

Thanks in advance.

You can do it this way (without the second requirement)

SELECT p.name
  FROM 
(
  SELECT eventid, COUNT(*) total
    FROM event
   GROUP BY eventid
) t JOIN event e
    ON t.eventid = e.eventid
   AND e.rank <= FLOOR(t.total / 15) JOIN player p
    ON e.playerid = p.id

Output:

|     NAME |
|----------|
|  player1 |
|  player4 |
| player31 |

Here is SQLFiddle demo


UPDATE you can meet your second requirement this way

SELECT p.name,
IF((
  SELECT COUNT(DISTINCT eventid)
    FROM event
   WHERE playerid = e.playerid
) >= 20, 'Yes', 'No') eligible
  FROM 
(
  SELECT eventid, COUNT(*) total
    FROM event
   GROUP BY eventid
) t JOIN event e
    ON t.eventid = e.eventid
   AND e.rank <= FLOOR(t.total / 15) JOIN player p
    ON e.playerid = p.id 

Sample output:

|     NAME | ELIGIBLE |
|----------|----------|
|  player1 |       No |
|  player4 |       No |
| player31 |       No |

Here is SQLFiddle demo