sql从数据库中选择最大值的行，并按2列分组

qco9c6ql 于 2021-06-20 发布在 Mysql

关注(0)|答案(1)|浏览(202)

这个问题在这里已经有答案了：

sql仅选择列上具有最大值的行[重复]（27个答案）
两年前关门了。
我有这张table：
餐桌优惠

+------+--------------+----------+----------------+
 | 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

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

sql mysql Database left-join Max

来源：https://stackoverflow.com/questions/52056551/sql-select-rows-with-max-value-from-database-and-group-by-2-columns

1条答案

按热度按时间

pengsaosao1#

你的预期结果并不表明 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
                   );

赞(0）回复(0）举报 2021-06-20

我来回答

sql从数据库中选择最大值的行，并按2列分组

1条答案

相关问题

热门标签

最新问答