简单的 sql 查询问题，求教

[xuyl]

表如下：

score

id | name | sex | score|

1 | tony | man | 80 |

2 | lily | woman | 90 |

---

3 | lucy | woman | 85 |

---

4 | andy | man | 95 |

---

要查出man和woman的最高分，如下

---

sex | name | score |

---

man | andy | 95 |

---

woman | lily | 90 |

---

select 语句怎么写？
我是菜鸟，只会写这样 select sex,max(score) as score from score group by score; 能否不用子查询就把name也查出来？

[Cee]

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

[xuyl]

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

[caigaopei]

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

[xuyl]

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

[Gonster]

>.<要不按分数排个序

[staticor]

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

[Cee]

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

[caigaopei]

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

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

[caigaopei]

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

[feiyuanqiu]

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

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

[liboyue]

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

[cha1]

我的笨方法：
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;

[bin456789]

我也有个笨方法
在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

[bin456789]

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

[kid813]

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

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

[bigfella]

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

[bin456789]

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

[lilydjwg]

@zqhong 我也是这么想的～

[ETiV]

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

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

[omengye]

还是来比一比办法笨好了
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

[bigfella]

@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