分组topn

select t.uid, row_number over() rank
from table t
group by t.f
order by t.rank
where t.rank < 10 and p

Select * from table,

row_number() over(partition by item order by score desc) rank

where rank<=2;

---

select t.f, t.uid, max(t.timestamp)
from table t
group by t.f

Mysql查询成绩表，取每个人的最高成绩
创建成绩表
CREATE TABLE score (
name varchar(36) DEFAULT NULL,
subject varchar(36) DEFAULT NULL,
score int(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入数据
INSERT INTO score VALUES (‘tom’, ‘语文’, ‘90’);
INSERT INTO score VALUES (‘tom’, ‘数学’, ‘99’);
INSERT INTO score VALUES (‘jack’, ‘语文’, ‘92’);
INSERT INTO score VALUES (‘jack’, ‘数学’, ‘88’);
INSERT INTO score VALUES (‘lucy’, ‘语文’, ‘80’);
INSERT INTO score VALUES (‘lucy’, ‘数学’, ‘90’);
INSERT INTO score VALUES (‘tom’, ‘英语’, ‘99’);
INSERT INTO score VALUES (‘jack’, ‘英语’, ‘66’);
INSERT INTO score VALUES (‘lucy’, ‘英语’, ‘88’);

取每个人最高成绩
1、先按照姓名分组，取每个人的最高成绩
SELECT NAME,max(score) score FROM score GROUP BY NAME

2、join关联查询出来
SELECT b.* FROM
(SELECT NAME, max(score) score FROM score GROUP BY NAME ) a
INNER JOIN score b ON a.name = b.name
AND a.score = b.score

https://blog.csdn.net/weixin_44550562/article/details/89028623

---

select count(uid), gender
from
(
select table_a.uid, table_b.gender
from table_a
join table_b on table_a.uid = table_b.uid
) t1
group by t1.gender