MySQL命令得分最高

I have two tables:

我有两张桌子:

  CREATE TABLE `fl_poll` (
  `id_poll` int(11) NOT NULL AUTO_INCREMENT,
  `id_player` int(11) NOT NULL,
  `position` varchar(50) NOT NULL,
  `score` int(11) NOT NULL,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `ip` varchar(100) NOT NULL,
  PRIMARY KEY (`id_anketa`)
) ENGINE=MyISAM;

CREATE TABLE `fl_player` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_team` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `lastname` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM;

And I would like to ask, is any solution how to select all players from table fl_player, count their score and order by top 3 players who have the highest score and others will be order by their lastname? For example I have six players:

我想问一下,如何从表fl_player中选择所有玩家的任何解决方案,计算得分最高的前三名玩家的得分和顺序,其他人将按姓氏排序?例如,我有六个玩家:

table fl_player:

id     |     lastname    
------------------------
1             Smith           
2            Johnson          
3             Todd             
4             Dragon            
5             Bond             
6             Black       

table fl_poll:

+-----------+-------+
| id_player | score |
+-----------+-------+
|         1 |     2 |
|         2 |     4 |
|         3 |     6 |
|         4 |     8 |
|         5 |    10 |
|         6 |    12 |
+-----------+-------+

And I wish result like this:

我希望这样的结果:

+-----------+----------------------------------------+
| id_player |                lastname                |
+-----------+----------------------------------------+
|         6 | Black <-- top 3 with highest score     |
|         5 | Bond                                   |
|         4 | Dragon                                 |
|         2 | Johnson <-- from now order by lastname |
|         1 | Smith                                  |
|         3 | Todd                                   |
+-----------+----------------------------------------+

5 个解决方案

#1

  SELECT   fl_poll.id_player, fl_player.lastname, fl_poll.score, 1 AS type
  FROM     fl_player
      JOIN fl_poll ON fl_poll.id_player = fl_player.id
  WHERE    fl_poll.score >= (
             SELECT MIN(score) FROM (
               SELECT score FROM fl_poll ORDER BY score DESC LIMIT 3
             ) t
           )

UNION

  SELECT   fl_poll.id_player, fl_player.lastname, fl_poll.score, 2 AS type
  FROM     fl_player
      JOIN fl_poll ON fl_poll.id_player = fl_player.id
  WHERE    fl_poll.score < (
             SELECT MIN(score) FROM (
               SELECT score FROM fl_poll ORDER BY score DESC LIMIT 3
             ) t
           )

ORDER BY CASE WHEN type = 1 THEN score END DESC, lastname

#2

Something like

SELECT s.id_player, n.lastname FROM fl_poll AS s INNER JOIN fl_player AS n ON n._id = s.id_player ORDER BY s.score DESC;

can do the job.

可以做这个工作。

#3

(SELECT * FROM user ORDER BY user_id DESC LIMIT 2)
UNION
(SELECT * FROM user ORDER BY username);

#4

This should do the trick. Join to a sub-select to get the top 3, order by their score in descending order. The players not in the top 3 will not have a score, so their score from the top3 subselect will be NULL (because they did not match one of the top 3), and they will appear after the top 3. They will simply be sorted in order by last_name.

这应该可以解决问题。加入子选择以获得前三名,按降序排列。不在前三名中的玩家将没有得分,因此他们从top3子选择中得分将为NULL(因为他们与前3名中的一名不匹配),并且他们将出现在前3名之后。他们将被简单地排序按last_name顺序排列。

SELECT po.id_player, lastname

FROM   fl_player pl

     LEFT JOIN 

(

SELECT pl.id, SUM(score) score

FROM   fl_player pl

       JOIN fl_poll po ON pl.id = po.id_player

GROUP BY pl.id

ORDER BY SUM(score) DESC

LIMIT 3

) as top3 on fl.id = top3.id

ORDER BY top3.score DESC, last_name

Use this if you want to include each player's score:

如果您想要包含每个玩家的分数,请使用此选项:

SELECT po.id_player, lastname, SUM(score) score

FROM   fl_player pl

       JOIN fl_poll po ON pl.id = po.id_player

     LEFT JOIN 

(

SELECT pl.id, SUM(score) score

FROM   fl_player pl

       JOIN fl_poll po ON pl.id = po.id_player

GROUP BY pl.id

ORDER BY SUM(score) DESC

LIMIT 3

) as top3 on fl.id = top3.id

GROUP BY pl.id

ORDER BY top3.score DESC, last_name

#5

It is extremely dangerous (and wrong) to depend on union to return results in a particular order.

依靠联盟以特定顺序返回结果是极其危险的(也是错误的)。

Here is an entirely different approach. Create a list of the top three ids by score. If the user is in that list, then order them by score. Otherwise order them by name.

这是一种完全不同的方法。按分数创建前三个ID的列表。如果用户在该列表中,则按分数对其进行排序。否则按名称订购。

You can get the list by using substring_index(group_concat( . . . )). The rest is just setting up the order by clause to use this information:

您可以使用substring_index(group_concat(...))获取列表。其余的只是设置order by子句来使用这些信息:

select p.id_player, p.lastname
from fl_player p cross join
     (select substring_index(group_concat(id order by score desc), ',', 3) as ids
      from fl_player
     ) top3
order by find_in_set(id, top3.ids) > 0,
         (case when find_in_set(id, top3.ids) > 0 then score end) desc,
         name