mysql - 从具有相同 id 的一组值中选择最大值
问题描述
我有这两张表:
CREATE TABLE Category (
CategoryID int NOT NULL,
CategoryName varchar(255),
PRIMARY KEY (CategoryID)
);
CREATE TABLE Team (
CategoryID int NOT NULL,
TeamName varchar(255) NOT NULL,
Points int(255),
PRIMARY KEY (TeamName),
FOREIGN KEY (CategoryID) REFERENCES Category(CategoryID)
);
我想从每个类别中找到得分最高的球队。
到目前为止,我已经尝试过这些,我知道它们是错误的,但我处于死胡同。任何建议都会有所帮助。
SELECT category.categoryid, team.teamname, MAX(team.points)
FROM category, team
GROUP BY team.teamname
HAVING MAX(team.points);
select category.categoryid, team.teamname
from category
right join team on team.categoryid = category.categoryid
group by team.teamname
having max(team.points);
select team.categoryid, team.teamname
from team, team a
having max(team.points);
select category.categoryid, team.teamname, max(team.points), team.points
from team
inner join category on category.categoryid = team.categoryid
group by teamname
having (select distinct max(team.points) from team);
select category.categoryid, team.teamname, team.points
from team
inner join category on category.categoryid = team.categoryid;
解决方案
8.0 版支持dense_rank()
SELECT teamname
FROM (
SELECT categoryid, teamname,
dense_rank() over(partition by categoryid order by points desc) rnk
FROM team
)t
WHERE rnk = 1
GROUP BY teamname
HAVING count(*) = (SELECT count(*) FROM Category);
推荐阅读
- c++ - 在结构中声明一个常量变量会导致我出现此错误非静态成员引用必须相对于特定对象
- bash - 如何在 bash 脚本中使用 '\n' 连接所有行
- javascript - 在 Javascript 中将货币转换为整数
- tensorflow - 为什么直接比较 tensorflow 的准确率比 keras 差?
- java - java 8 stream api如何在一系列转换中访问先前的转换对象
- mysql - 一段时间后,带有 Kafka 的 Debezium Mysql 连接器未读取 Db 更改,为什么?
- php - 如何解决完整性约束违规:1052 列 'id' in where 子句在 laravel 中不明确
- java - 如何在 Retrofit2 Android 中动态重命名 JSON 响应的键
- file - mmap 文件不同步
- java - 泛型的 Java 编译失败