mysql - SQL从数据库中选择具有最大值的行，并按2列分组

这个问题已经有了答案：
SQL select only rows with max value on a column [duplicate]
27个答案
我有这张桌子：
桌上提供

 +------+--------------+----------+----------------+
 | id   | player_id    | team_id  | valore         |
 +------+--------------+----------+----------------+
 | 1    | 1            | 1        | 230            |
 | 2    | 1            | 3        | 150            |
 | 3    | 9            | 3        | 150            |
 | 4    | 1            | 5        | 100            |
 | 5    | 7            | 5        | 37             |
 | 6    | 7            | 1        | 38             |
 +------+--------------+----------+----------------+

我期待这个结果，
我想创建这样的视图：

+------+--------------+----------+----------------+
| id   | player_id    | team_id  | valore         |
+------+--------------+----------+----------------+
| 1    | 1            | 1        | 230            |
| 3    | 9            | 3        | 150            |
| 6    | 7            | 1        | 38             |
+------+--------------+----------+----------------+

我尝试使用以下SQL代码：
创建视图…

select t1.*
  from offers t1
       left join ( select player_id,
                          team_id,
                          max(valore) as valore
                     from offers
                 group by player_id,
                          team_id) t2
                 on t1.player_id = t2.player_id
                    and t1.team_id = t2.team_id
                    and t1.valore = t2.valore

但是结果和第一个表一样…它不会改变任何东西。
有人能帮我吗？

最佳答案

您的预期结果并不表示team_id在GROUP BY子句中，它实际上是基于player_id的。所以，从GROUP BY子句中删除它，并将ON子句更改为t1.player_id = t2.player_id and t1.valore = t2.valore
所以，你的问题是：

create view...
    select t1.*
    from offers t1 inner join
        (select player_id, max(valore) as valore
         from offers
         group by player_id
        ) t2
       on t1.player_id = t2.player_id and
          t1.valore = t2.valore;

但是，我会这样做：

create view v1 as
    select o.*
    from offers o
    where valore = (select max(o1.valore)
                    from offer o1
                    where o1.player_id = o.player_id
                   );

关于mysql - SQL从数据库中选择具有最大值的行，并按2列分组，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/52056551/