mysql - 如何在 group by query 中使用 distinct？

Question

我有这个问题

SELECT 
    user_id, 
    played_level, 
    sum( time_taken ) AS time_take
FROM answer
WHERE completed =1
GROUP BY played_level, user_id
ORDER BY time_take
LIMIT 20 

此查询显示所有用时最短的用户 ID。

但现在我只想显示他最短时间的distict user id。

 user_id    played_level    time_take   
     1      18                19
     1      12                21
     2       3                25
     6       3                26
     2       2                27
     6       4                27
     1       8                32

预期输出:

user_id  played_level time_taken
 1         18          19
 2          3          25
 6          3          26  

Answer 1

首先从表中选择所有distict user_id

                $distinct_users = "SELECT DISTINCT user_id FROM answer where addeddate BETWEEN :currdate1 AND :currdate2 AND completed=1";
                $distinct_users_data = $conn->prepare($distinct_users);
                $distinct_users_data->execute(array(':currdate1'=>$prev_date,':currdate2'=>$currdate2));
                $distinct_users_arr = $distinct_users_data->fetchAll();

现在求出这个user_id的最小时间，存入数组

                foreach($distinct_users_arr as $distict_data)
                    {
                    $user_ids=$distict_data['user_id'];


                $all_user_time = "SELECT user_id, played_level, sum( time_taken ) AS time_take FROM answer where addeddate BETWEEN :currdate1 AND :currdate2 
                                  AND completed=1 AND user_id=:user_id GROUP BY played_level, user_id ORDER BY time_take limit 1";
                $all_user_time_data = $conn->prepare($all_user_time);
                $all_user_time_data->execute(array(':currdate1'=>$prev_date,':currdate2'=>$currdate2,':user_id'=>$user_ids));
                $all_user_time_arr = $all_user_time_data->fetchAll();
                $all_count= $all_user_time_data->rowCount();
                foreach($all_user_time_arr as $leading_user)
                            {
                            $new_user_id= $leading_user['user_id'];
                            $new_time= $leading_user['time_take'];

                                $leader_board[] = array(
                                                'user_id'=>$new_user_id, 
                                                'time_taken'=>$new_time
                                            );

                            }


                    }

最后按所用时间对该数组顺序进行排序。我们得到最终结果

foreach ($leader_board as $key => $row) 
                                         {
                                    $sorting[$key] = $row['time_taken'];
                                        }
                            array_multisort($sorting, SORT_ASC, $leader_board);