关于聚合函数中的查询问题

Question

sudo -u postgres psql -c "CREATE DATABASE sanguo;"
sudo -u postgres psql sanguo -c "CREATE TABLE heroes(id serial, name VARCHAR(20),fight int,intelligence int,city VARCHAR(20),nation VARCHAR(20),items VARCHAR(20));"
sudo -u postgres psql sanguo -c  "INSERT INTO heroes(name, fight, intelligence,nation,city,items) VALUES('刘备',60,65,'蜀','涿','双股剑'),('关羽',96,96,'蜀','解良','青龙偃月刀 赤兔马'),('张飞',98,60,'蜀','涿','丈八蛇矛'),('吕布','100','86','董卓','长安','方天画戟'),('马超','97','86','蜀','天水','铁骑尖'),('赵云','97','90','蜀','蓟','龙胆'),('典韦','96','57','魏','陈留','铁戟'),('许褚','96','65','魏','陈留',''),('甘宁','95','84','吴','建邺','双匕首'),('庞德','95','80','魏','天水',''),('黄忠','94','85','蜀','长沙','猛将弓'),('文丑','94','79','袁绍','蓟',''),('太史慈','93','82','吴','建邺',''),('华雄','92','82','董卓','长安',''),('孙策','92','92','吴','建邺','霸王枪'),('颜良','92','80','袁绍','蓟',''),('张辽','92','93','魏','蓟',''),('魏延','91','81','蜀','信阳',''),('夏侯渊','91','85','魏','陈留',''),('孙坚','90','93','吴','建邺',''),('文鸯','90','77','魏','蓟',''),('夏侯惇','90','89','魏','陈留','碎岩'),('周泰','90','77','吴','建邺',''),('曹彰','88','82','魏','许昌',''),('姜维','88','89','蜀','天水',''),('曹操','85','96','魏','陈留','孟德新书 玉玺 铜雀台赋'),('孙权','83','91','吴','建邺',''),('诸葛亮','60','98','蜀','南阳',''),('董卓','60','50','董卓','长安','魔王扑'),('袁绍','60','60','袁绍','长安','王道剑');"

由以上语句 可以生成 heroes表，SELECT * FROM heroes;查询结果如下。

聚合函数max，查询结果如下

\c sanguo

SELECT 
    nation,
    max(fight)
FROM 
    heroes
GROUP BY
    nation
;

目前的问题是：
想要查询出每个国家中，武力(fight)最高的那个人是谁，应该怎么办呢？

Answer 1

解决方法

想要查询出每个国家中，武力(fight)最高的那个人是谁
之前的SQL语句修改一下：

SELECT nation, 
       name, 
       MAX(fight) AS max_fight 
FROM heroes 
GROUP BY nation;

---

如有问题随时沟通
如有帮助欢迎采纳