MySQL 查找排名前 3 的球队

Question

我需要按以下方式在我的排名页面上显示列表

Team_Name      Played       Won      Lost     Points     In_Top_3
Some Team      5            5        0        11          Yes

Points are calculated as

• 2 Points / match winning

• +1 if the match won was a quarter final

• +2 if the match won was a semi-final

• +3 if the match won was a Grand-finale

我成功实现了其中的大部分目标，但其中一个是 In_Top_3，尽管有一种廉价的解决方法来添加 $count并检查 <=3并在该列下显示"is"，但有什么方法可以使用 SQL 查询来完成此操作。

我当前的SQL查询如下

SELECT
    T.id, T.name, T.status, IFNULL(T.image, 'no-image.png') AS DP,
    (SELECT COUNT(*)
    FROM badminton_matches MT
    WHERE (MT.team_one = T.id OR MT.team_two = T.id)) 
  AS played,
    (SELECT COUNT(*)
    FROM badminton_match_results R
    WHERE R.winner_id = T.id) AS won,
    (SELECT COUNT(*)
    FROM badminton_matches MT JOIN badminton_match_results MR
        ON (MR.match_id = MT.id)
    WHERE (MT.team_one = T.id OR MT.team_two = T.id) AND MR.winner_id != T.id) AS lost,
    (
   ((SELECT COUNT(*)
    FROM badminton_match_results R
    WHERE R.winner_id = T.id) * 2) 
     + 
   ((SELECT COUNT(*)
    FROM badminton_match_results R JOIN badminton_matches M ON (M.id = R.match_id AND M.match_type = 'quarter')
    WHERE R.winner_id = T.id))
  ) AS Points
FROM badminton_teams T
ORDER BY (Points) DESC

当我有以下表结构时，如何标记排名前 3 的团队以及当前的查询，并且

表格:团队

+------------+----------------------------+------+-----+-------------------+----------------+
| Field      | Type                       | Null | Key | Default           | Extra          |
+------------+----------------------------+------+-----+-------------------+----------------+
| id         | int(11)                    | NO   | PRI | NULL              | auto_increment |
| name       | varchar(150)               | NO   |     | NULL              |                |
| image      | text                       | YES  |     | NULL              |                |
| status     | enum('active','in-active') | NO   |     | active            |                |
| added_on   | datetime                   | NO   |     | CURRENT_TIMESTAMP |                |
| updated_on | datetime                   | YES  |     | NULL              |                |
+------------+----------------------------+------+-----+-------------------+----------------+

表:匹配

+------------+---------------------------------------+------+-----+-------------------+----------------+
| Field      | Type                                  | Null | Key | Default           | Extra          |
+------------+---------------------------------------+------+-----+-------------------+----------------+
| id         | int(11)                               | NO   | PRI | NULL              | auto_increment |
| team_one   | int(11)                               | NO   | MUL | NULL              |                |
| team_two   | int(11)                               | NO   |     | NULL              |                |
| added_on   | datetime                              | NO   |     | CURRENT_TIMESTAMP |                |
| match_type | enum('pool','quarter','semi','final') | NO   |     | pool              |                |
| sets       | smallint(2)                           | NO   |     | 1                 |                |
+------------+---------------------------------------+------+-----+-------------------+----------------+

表格:比赛结果

+-----------+----------+------+-----+-------------------+----------------+
| Field     | Type     | Null | Key | Default           | Extra          |
+-----------+----------+------+-----+-------------------+----------------+
| id        | int(11)  | NO   | PRI | NULL              | auto_increment |
| match_id  | int(11)  | NO   | MUL | NULL              |                |
| winner_id | int(11)  | NO   | MUL | NULL              |                |
| added_on  | datetime | NO   |     | CURRENT_TIMESTAMP |                |
+-----------+----------+------+-----+-------------------+----------------+

Answer 1

这里有一个类似的问题:

How to add ROW INDEX as a column to SQL SELECT query?

从这个问题延伸，你想要类似的东西:

SET @row_num = 0;
SELECT
    T.id, T.name, T.status, IFNULL(T.image, 'no-image.png') AS DP,
    (SELECT COUNT(*)
    FROM badminton_matches MT
    WHERE (MT.team_one = T.id OR MT.team_two = T.id)) 
      AS played,
    (SELECT COUNT(*)
    FROM badminton_match_results R
    WHERE R.winner_id = T.id) AS won,
    (SELECT COUNT(*)
    FROM badminton_matches MT JOIN badminton_match_results MR
        ON (MR.match_id = MT.id)
    WHERE (MT.team_one = T.id OR MT.team_two = T.id) AND MR.winner_id != T.id) AS lost,
    (
       ((SELECT COUNT(*)
    FROM badminton_match_results R
    WHERE R.winner_id = T.id) * 2) 
         + 
       ((SELECT COUNT(*)
    FROM badminton_match_results R JOIN badminton_matches M ON (M.id = R.match_id AND M.match_type = 'quarter')
    WHERE R.winner_id = T.id))
      ) AS Points,

    /* here is the magic */
    (@row_num
:= @row_num + 1) < 4 AS row_index

    FROM badminton_teams T
    ORDER BY
(Points) DESC

这将添加一个名为 row_index 的额外列，其中 1 表示位于前 3 个中，0 表示不在前 3 个中。

请记住，您必须在每次 SELECT 之前且在同一 session 内调用 SET。