mysql - 从每个小组问题中获得最短的时间

Question

我试图获得每个组的最短时间，但我无法让它正常工作，min()函数似乎不起作用，只返回第一个记录每个组的发现。

我尝试过的查询:

SELECT 
    *, 
    MIN(TIME_TO_SEC(IF(LENGTH(`time`)<6,CONCAT('00:',`time`),`time`))) 
FROM `test` 
GROUP BY `number`;

时间字段/列需要采用 Varchar 格式!

预期结果:

1 - 59:09 - name3
2 - 48:02 - name2

http://sqlfiddle.com/#!9/972be/1

Answer 1

伙计，你的方法是对的，只需要一点点调整。

select t1.`number`, t1.`name`,t1.`time` from
  (SELECT `number`,`name`,`time`,
          TIME_TO_SEC(IF(LENGTH(`time`)<6,CONCAT('00:',`time`),`time`)) as `min`
  FROM `test` GROUP BY `number`,`name`)t1
  left join
 (SELECT `number`,`name`,`time`,
         TIME_TO_SEC(IF(LENGTH(`time`)<6,CONCAT('00:',`time`),`time`)) as `min`
 FROM `test` GROUP BY `number`,`name`)t2
    on t1.`number`=t2.`number` and t1.`min` > t2.`min`
 where t2.`number` is null;

新的

 select t1.`map`,t1.`record`,t1.`name` from
(select `map`,`record`,`name`,
 replace(IF(LENGTH(`achieved`)<6,CONCAT('00:',`achieved`),`achieved`),':','') as mins
 from `records` where `record` = 'speedrun') t1
inner join
(select `map`,
min(replace(IF(LENGTH(`achieved`)<6,CONCAT('00:',`achieved`),`achieved`),':','')) as mins
from `records`
where `record` = 'speedrun' group by `map`) t2
on t1.`map` = t2.`map` and t1.mins = t2.mins