mysql 选择性select,mysql SELECT在单个表中每个类别的最佳选择

小编典典

试试这个：

SELECT gamers.*

FROM gamers

INNER JOIN

(SELECT

max(score) as maxscore,

gameid from gamers

GROUP BY gameid) AS b

ON (b.gameid = gamers.gameid AND b.maxscore=gamers.score) ;

ORDER BY score DESC, gameid;

这将输出：

+---------+--------+-------+

| gamerid | gameid | score |

+---------+--------+-------+

| 4 | 1 | 90 |

| 5 | 2 | 40 |

| 8 | 3 | 30 |

+---------+--------+-------+

3 rows in set (0.00 sec)

您可以做的另一种选择是创建一个临时表或视图(如果您不喜欢子查询)。

create temporary table games_score (

SELECT max(score) as maxscore, gameid FROM gamers GROUP BY gameid

);

然后：

SELECT gamers.*

FROM gamers

INNER JOIN games_score AS b ON (b.gameid = gamers.gameid AND b.maxscore=gamers.score)

ORDER BY score DESC, gameid;

或视图：

create or replace view games_score AS

SELECT max(score) as maxscore, gameid FROM gamers GROUP BY gameid;

然后：

SELECT gamers.*

FROM gamers

INNER JOIN games_score AS b ON (b.gameid = gamers.gameid AND b.maxscore=gamers.score)

ORDER BY score DESC, gameid;

2021-03-08