mysql - 按组中前 n(最少)个项目进行计数和分组

Question

我已经经历了几个“n from M”类型的解决方案，但无法接近我所追求的目标，尽管这个问题可能以前已经以某种其他格式提出过。

我已经尝试过此 MySQL Group By with top N number of each kind 中的示例还有这个http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/这些似乎都不适用于我想做的事情。

我想做的是确定运行比赛中最好的团队，个人运行者不是问题，性别、年龄组都可以照顾到。团队奖励的规则基于俱乐部的成员(member)资格。

1. 俱乐部必须至少有 3 名参赛者才有资格参加团体赛。
2. 只有每个俱乐部的前 3 名参赛者才有资格参加比赛。
3. 球队名次由符合资格的选手总和决定，因此 A 俱乐部获得第 2、9 和 10 名的选手可获得 21 分，B 俱乐部获得第 4、5 和 6 名的选手可获得 15 分，依此类推。

我有一个包含以下字段的表:

+---------------+-------------+------+-----+---------+----------------+
| Field         | Type        | Null | Key | Default | Extra          |
+---------------+-------------+------+-----+---------+----------------+
| id            | int(11)     | NO   | PRI | NULL    | auto_increment |
| runner_id     | int(11)     | YES  |     | NULL    |                |
| club_id       | int(11)     | YES  |     | NULL    |                |
| race_id       | int(11)     | YES  |     | NULL    |                |
| race_number   | int(11)     | YES  |     | NULL    |                |
| category      | varchar(20) | YES  |     | NULL    |                |
| finish_time   | int(11)     | YES  |     | NULL    |                |
| race_position | int(11)     | YES  |     | NULL    |                |
+---------------+-------------+------+-----+---------+----------------+

只有club_id和race_position与查询相关。 runner_id、club_id 和 race_id 是外键，我需要在创建结果时能够从这些表中提取数据(given_name、family_name、age、club_name 等)。

这是典型数据:

+----+-----------+---------+---------+-------------+-----------+-------------+---------------+
| id | runner_id | club_id | race_id | race_number | category  | finish_time | race_position |
+----+-----------+---------+---------+-------------+-----------+-------------+---------------+
| 53 |        26 |       1 |      85 |          17 | Msenior   |        1666 |            11 |
| 35 |        39 |       1 |      85 |           4 | Munder_18 |        1503 |             4 |
| 63 |        61 |       2 |      85 |          27 | Mvet_50   |        1610 |             9 |
| 42 |        46 |       2 |      85 |          11 | Lvet_40   |        1773 |            14 |
| 38 |        42 |       2 |      85 |           7 | Lunder_18 |        1793 |            17 |
| 56 |        36 |       9 |      85 |          20 | Msenior   |        1561 |             6 |
| 44 |        48 |       9 |      85 |          13 | Msenior   |        1667 |            12 |
| 64 |        62 |       9 |      85 |          28 | Msenior   |        1660 |            10 |
| 49 |        52 |       9 |      85 |          18 | Msenior   |        1432 |             1 |
| 47 |        51 |      10 |      85 |          16 | Msenior   |        1779 |            15 |
| 61 |        59 |      11 |      85 |          25 | Mvet_50   |        1502 |             3 |
| 33 |        38 |      11 |      85 |           2 | Munder_18 |        1440 |             2 |
| 65 |        63 |      11 |      85 |          29 | Mvet_40   |        1566 |             8 |
| 54 |        54 |      12 |      85 |          19 | Msenior   |        1785 |            16 |
| 58 |        56 |      12 |      85 |          23 | Msenior   |        1546 |             5 |
| 37 |        41 |      12 |      85 |           6 | Munder_18 |        1668 |            13 |
| 45 |        49 |      14 |      85 |          14 | Mvet_50   |        1565 |             7 |
+----+-----------+---------+---------+-------------+-----------+-------------+---------------+

我想要的最终结果是这样的:

+----+-----------+---------+---------+-------------+-----------+-------------+---------------+
| id | runner_id | club_id | race_id | race_number | category  | finish_time | race_position |
+----+-----------+---------+---------+-------------+-----------+-------------+---------------+
| 33 |        38 |      11 |      85 |           2 | Munder_18 |        1440 |             2 |
| 61 |        59 |      11 |      85 |          25 | Mvet_50   |        1502 |             3 |
| 65 |        63 |      11 |      85 |          29 | Mvet_40   |        1566 |             8 |
| 49 |        52 |       9 |      85 |          18 | Msenior   |        1432 |             1 |
| 56 |        36 |       9 |      85 |          20 | Msenior   |        1561 |             6 |
| 64 |        62 |       9 |      85 |          28 | Msenior   |        1660 |            10 |
| 58 |        56 |      12 |      85 |          23 | Msenior   |        1546 |             5 |
| 37 |        41 |      12 |      85 |           6 | Munder_18 |        1668 |            13 |
| 54 |        54 |      12 |      85 |          19 | Msenior   |        1785 |            16 |
| 63 |        61 |       2 |      85 |          27 | Mvet_50   |        1610 |             9 |
| 42 |        46 |       2 |      85 |          11 | Lvet_40   |        1773 |            14 |
| 38 |        42 |       2 |      85 |           7 | Lunder_18 |        1793 |            17 |
+----+-----------+---------+---------+-------------+-----------+-------------+---------------+

因此，尽管 runner_id 52 赢得了比赛，但他并不属于获胜队伍。

我在 Codeigniter/Datamapper ORM 下运行所有​​这些，但我可以通过这一层传递完整的 SQL 查询字符串。

我希望这一切都有道理。

Answer 1

MySQL 缺乏解决这个问题的重要功能(CTE、窗口函数)，但您可以使用一些用户定义的变量并支付性能成本来解决它们:

SELECT s1.id, s1.runner_id, s1.club_id, s1.race_id, s1.race_number, s1.category,
  s1.finish_time, s1.race_position
FROM (
  SELECT t1.*,
    @club_rank := if(@prev_club = t1.club_id, @club_rank + 1, 1) club_rank,
    @prev_club := t1.club_id
  FROM t t1
  CROSS JOIN (SELECT @prev_club := NULL, @club_rank := 1) init
  ORDER BY t1.club_id, t1.race_position
) s1
JOIN (
  SELECT club_id, count(*) teamSize, sum(race_position) teamPosition FROM t
  GROUP BY club_id
) s2 ON s1.club_id = s2.club_id
WHERE club_rank <= 3 AND teamSize >= 3
ORDER BY teamPosition, race_position

输出:

| ID | RUNNER_ID | CLUB_ID | RACE_ID | RACE_NUMBER |  CATEGORY | FINISH_TIME | RACE_POSITION |
|----|-----------|---------|---------|-------------|-----------|-------------|---------------|
| 33 |        38 |      11 |      85 |           2 | Munder_18 |        1440 |             2 |
| 61 |        59 |      11 |      85 |          25 |   Mvet_50 |        1502 |             3 |
| 65 |        63 |      11 |      85 |          29 |   Mvet_40 |        1566 |             8 |
| 49 |        52 |       9 |      85 |          18 |   Msenior |        1432 |             1 |
| 56 |        36 |       9 |      85 |          20 |   Msenior |        1561 |             6 |
| 64 |        62 |       9 |      85 |          28 |   Msenior |        1660 |            10 |
| 58 |        56 |      12 |      85 |          23 |   Msenior |        1546 |             5 |
| 37 |        41 |      12 |      85 |           6 | Munder_18 |        1668 |            13 |
| 54 |        54 |      12 |      85 |          19 |   Msenior |        1785 |            16 |
| 63 |        61 |       2 |      85 |          27 |   Mvet_50 |        1610 |             9 |
| 42 |        46 |       2 |      85 |          11 |   Lvet_40 |        1773 |            14 |
| 38 |        42 |       2 |      85 |           7 | Lunder_18 |        1793 |            17 |

fiddle here .