如何以更干净的方式执行这些MySQL子查询？

时间：2021-09-08 06:54:10

Using legacy code which uses mysql instead of mysqli/pdo so don't worry about this, I'll update the queries for this later.

使用使用mysql而不是mysqli / pdo的遗留代码所以不用担心这个问题,我稍后会更新这些查询。

Even though my current method works, I'm positive there is a cleaner way of doing this rather than a query and 3 subqueries. I mainly want to learn how to better enhance my queries and minimizing the amount of them.
What I'm trying to do is

尽管我当前的方法有效,但我很肯定有一种更简洁的方法,而不是查询和3个子查询。我主要想学习如何更好地增强查询并最大限度地减少查询量。我想做的是

  1. echo out all the data for each date with the date displayed on top

    2. 回显每个日期的所有数据,日期显示在顶部

  2. Display the count of entries for each user on that particular day next to the user

    3. 显示用户旁边该特定日期的每个用户的条目数

  3. For each date, at the bottom of the above 2 bits of data, display the user/s with the highest number of entries

    对于每个日期,在上述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++;

}

The trouble I'm having is that when I try and reduce these subqueries and use MAX it will only output the highest count but not all the data for each date, which is what I need, including output of the user/s with the most amount of entries for that given day.

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

1 个解决方案

#1

You could start with something like this. Note that I'm not using PHP's mysql_ API as it was deprecated 3 or 4 years ago...

你可以从这样的事情开始。请注意,我没有使用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);

#1

You could start with something like this. Note that I'm not using PHP's mysql_ API as it was deprecated 3 or 4 years ago...

你可以从这样的事情开始。请注意,我没有使用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);
标签：

相关文章