简单的 sql 查询问题，求教

SELECT name, sex, max(score) AS score FROM score GROUP BY sex;

@Cee group by是对sex聚合分组，这样取出来的name是不对应score的。

select name, sex ,max(score) from score group by sex;
+------+-------+------------+
| name | sex | score |
+------+-------+------------+
| tony | man | 95 |
| lily | woman| 90 |
+------+-------+------------+

@caigaopei 你看一下结果，tony是错的，应该是andy

>.<要不按分数排个序

groupby max取得 socre + id+ sex, 再join 添加上name.

@xuyl 嗯，那還是做個子查詢或者 Join 好了

@xuyl select name,sex,max(score) from score group by name LIMIT 0,2; 这个笨办法不知道可行不..

+------+-------+------------+
| name | sex | max(score) |
+------+-------+------------+
| andy | man | 95 |
| lily | woman | 90 |
+------+-------+------------+

@xuyl 好像还是不对.. 菜鸟飘过...

跟这个问题类似，可以直接参考这里 8L 的回答：

https://www.v2ex.com/t/193875

这样行不？
select name, max(score) from score where sex = man

我的笨方法：
SELECT name, sex, max(score) AS score FROM score WHERE sex = man
UNION
SELECT name, sex, max(score) AS score FROM score WHERE sex = womon;

我也有个笨方法
在mssql上可用

SELECT *
FROM ( SELECT TOP 1
Sex ,
Name ,
Score
FROM dbo.Score
WHERE Sex = 'man'
ORDER BY Score DESC
) AS t1
UNION ALL
SELECT *
FROM ( SELECT TOP 1
Sex ,
Name ,
Score
FROM dbo.Score
WHERE Sex = 'woman'
ORDER BY Score DESC
) AS t2;

Sex Name Score
-------------------------------------------------- -------------------------------------------------- -----------
man andy 95
woman lily 90

找到一个更好的查询方法，请自己翻译到mysql中
SELECT *
FROM ( SELECT * ,
RANK() OVER ( PARTITION BY Sex ORDER BY Score DESC ) AS rank
FROM dbo.Score
) AS t
WHERE t.rank = 1;

在SELECT语句中，所有不出现在聚合函数（MAX）的列名都必须出现在GROUP BY中。

但是写GROUP BY name显然不是想要的结果，所以应该只能写子查询。

子查询可行~ 笨方法
select name, sex, score from Score where score in (select max(score) from Score group by sex )

@bigfella 如果第二高分的男生跟第一高分的女生同分，那结果就有误了

@zqhong 我也是这么想的～

这标题起的太误导人了……

MySQL 就没有能解决这类问题的“简单的SQL语句”

还是来比一比办法笨好了
SELECT
s1.sex,
s2.NAME,
s1.score
FROM
(
SELECT
sex,
max(score) AS score
FROM
score
GROUP BY
sex
) s1
LEFT JOIN score s2 ON s1.score = s2.score
ORDER BY s1.score DESC

@bin456789 摁~ 没想到， 改了下， 加了性别的条件，应该差不错吧
select * from (select t.sex, max(t.score) as score from score t group by sex) aa, score bb where bb.sex = aa.sex and bb.score = aa.score