php - 如何以更简洁的方式执行这些MySQL子查询？

使用使用mysql而不是mysqli/pdo的旧代码，因此不必担心，我将在稍后更新查询。

即使我当前的方法可行，但我肯定有一种更干净的方法可以执行此操作，而不是使用查询和3个子查询。我主要想学习如何更好地增强查询并减少查询量。
我想做的是


echo列出每个日期的所有数据，日期显示在顶部
在用户旁边的特定日期显示每个用户的条目数
对于每个日期，在上述2位数据的底部，显示条目数最多的用户

$query = mysql_query('SELECT * FROM entries GROUP BY DATE(dt)');

$g = 0;

while ($row = @mysql_fetch_array($query))
{

    $group[$g] = date('y-m-d', strtotime($row['dt']));
    echo $group[$g] . "<br />";

    //display the person's name for today with their count
    $dayquery = mysql_query('SELECT *, COUNT(username) as total FROM entries WHERE DATE(dt) = "'.$group[$g].'" GROUP BY username ORDER BY COUNT(username) DESC');
        while ($today = @mysql_fetch_array($dayquery))
        {
            echo $today['first_name'] . " | " . $today['total'] . "<br />";
        }

        //display the highest count for today
        $topquery = mysql_query('SELECT COUNT(username) as highest FROM entries WHERE DATE(dt) = "'.$group[$g].'" GROUP BY username ORDER BY COUNT(username) DESC LIMIT 1');
            while ($toptoday = @mysql_fetch_array($topquery))
            {
                echo "Highest today: " . $toptoday['highest'] . "<br /><br />" ;
            }

            //display the users with the highest count for today
            echo "Highest users: ";
            $userstopquery = mysql_query('SELECT *, COUNT(username) as total FROM entries WHERE DATE(dt) = "'.$group[$g].'" AND COUNT(username) = "' . $toptoday['highest'] . '" AND GROUP BY username');
                while ($topusers = @mysql_fetch_array($userstopquery))
                {
                     echo $topusers['first_name'] . "<br />" ;
                }

    $g++;

}

我遇到的麻烦是，当我尝试减少这些子查询并使用MAX时，它只会输出最高计数，但不会输出每个日期的所有数据，这正是我所需要的，包括用户的输出。该天的条目数量最多。

最佳答案

您可以从这样的事情开始。请注意，我没有使用PHP mysql API，因为3或4年前已弃用...

require('path/to/mysqli/connection/stateme.nts');

$array = array();

$query = "
SELECT e.dt
     , e.username
     , COUNT(*) ttl
  FROM entries e
 GROUP
    BY e.dt
     , e.username
 ORDER
    BY e.dt, ttl DESC;
";


$result = mysqli_query($conn,$query);

while ($row = mysqli_fetch_assoc($result))
        {
            $array[] = $row;
        }

print_r($array);