从mysql db中提取数据，其中用户有多行，但查找具有两个最大值的行

Question

I have a table called leaders. in it I have id,userid,bmid,reps,rounds,ts. Basically I need to put the top ten unique users out of the table that have the most rounds. Now each time someone enters their reps,rounds its as a pair so someone might have 12 rounds 13 reps so if that is their max and it is within the top ten of all users then I need to pull that info plus their corresponding reps. I thought I had this but it is actually pulling their max rounds and their max reps from different rows. What I have is below.

 SELECT max(l.rounds) as rounds, l.reps, m.name, l.userid 
 from leaders l 
 inner join members m on m.id = l.userid 
 where m.genre = 'male' and l.bmid  = 1 
 group by l.userid 
 order by  rounds desc,reps desc

the join is to the members table to get some info on them.

Answer 1

I guess you need a subquery to achieve this. Take a look at this:

How to select the first/least/max row per group in SQL

EDIT: Try this code: http://sqlfiddle.com/#!2/8bb81/3

CREATE TABLE `leaders` (
  `id` int(11) AUTO_INCREMENT,
  `userid` int(11),
  `rounds` int(11),
  `reps` int(11),
  PRIMARY KEY (`id`)
);

INSERT INTO `leaders` (userid, rounds, reps) VALUES
  (1, 5, 3), (1, 7, 2), (1, 7, 1), (1, 7, 8),
  (2, 7, 6), (2, 7, 9), (2, 4, 3),
  (3, 7, 2), (3, 3, 5),
  (4, 8, 9);

SELECT
  userid,
  MAX(rounds) AS max_rounds, (
    SELECT MIN(reps)
    FROM leaders l2
    WHERE l2.userid = l1.userid AND l2.rounds <> l1.rounds
  ) AS min_reps
FROM leaders l1
GROUP BY userid
ORDER BY max_rounds DESC, min_reps ASC;