select score,sex,name,max(score) from table group by sex
这样试试?或者你union all试试
select name,sex,score from table where sex='f' order by score desc union all select name,sex,score from table where sex='m' order by score desc limit 2
select t.name as '男生姓名',t.cj as '男生最高成绩',tt.name as '女生姓名',tt.cj as '女生最高成绩' from (select * from student where sex = '男' order by cj desc limit 1) t,(select * from student where sex = '女' order by cj desc limit 1) tt本回答被提问者采纳
第2个回答 2017-09-30
考试可以这么写,实际开发中,要是写这么复杂,效率大打折扣。。。。
第3个回答 2018-08-08
mysql> SELECT MAX(score) AS "最高成绩",sex AS "性别",name AS "姓名" FROM t1 GROUP BY sex ; +--------------+--------+--------+ | 最高成绩 | 性别 | 姓名 | +--------------+--------+--------+ | 98 | 男 | 张三 | | 89 | 女 | 李四 | +--------------+--------+--------+
或者
(SELECT * FROM t1 WHERE sex="男" ORDER BY score DESC LIMIT 1) -- 男最高成绩 UNION (SELECT * FROM t1 WHERE sex="女" ORDER BY score DESC LIMIT 1); --女最高成绩