从 MySQL 表中返回每个唯一用户以及每个用户的行数计数 - Returning each unique user from a MySQL table and also the count of the number of rows for each user

Returning each unique user from a MySQL table and also the count of the number of rows for each user

本文关键字：用户唯一 MySQL 返回 | 更新日期: 2023-09-27

我正在使用以下MySQL查询为数据库中的用户生成表。查询旨在为每个用户仅返回一行，即使每个用户有多行也是如此。这工作正常，但是我还需要计算每个用户的唯一条目数，以输入它在此处声明的表中。我是否需要使用另一个查询来返回所有条目的计数，如果是，如何将其与已有的代码集成？

$query="SELECT from_user, COUNT(*) AS num FROM tracks GROUP BY from_user ORDER BY COUNT(*) DESC";
$result=mysql_query($query) or die(mysql_error());
        while ($row = mysql_fetch_array($result)) {
        $user = $row['from_user'];
        echo "<tr>";
        echo "<td>".$user."</td>";
        echo "<td>uploads (**HERE**)</td>";
        echo "<td>favourites (count)</td>";
        echo "</tr>";
        }
?>
</table>

因为您已经创建了自定义字段"num"，所以您可以使用它来获取计数！

在user = ...后添加以下行

$count = $row['num'];

然后你可以

echo "<td>uploads ($count)</td>";

知道您的字段名称会错过您的表格结构，但是，如果我很好地理解您的问题，您可以在 mysql 中使用 count + distinct 。您也可以查看此答案。

SELECT DISTINCT(from_user) AS user, 
COUNT(from_user) AS num 
FROM tracks 
GROUP BY from_user 
ORDER BY num DESC";

对于第二个问题，您可以执行第二个查询，或执行连接跟踪。

我认为，在您的情况下，您更容易在循环内进行第二次查询以从"用户"结果中获取所有详细信息。

$query1="SELECT DISTINCT(from_user), COUNT(*) AS num 
FROM tracks 
GROUP BY from_user 
ORDER BY COUNT(*) DESC";
$query2="SELECT * FROM tracks";
$result1=mysql_query($query1) or die(mysql_error());
$result2=mysql_query($query2) or die(mysql_error());
$user_array = array();
while ($row = mysql_fetch_array($result1)) {
    $user = $row['from_user'];
    $num = $row['num'];
    $uploads_array = array();
    while ($sub_row = mysql_fetch_array($result2)) { 
      if( $sub_row['from_user'] == $user ) {
            //for example only due to the unknown structure of your table
            $uploads_array[] = array( 
                "file_name" => $sub_row['file_name'], 
                "file_url" => $sub_row['file_url'] 
            ); 
      }
    }
    $user_array[] = array( 
        "name" => $user, 
        "num_entry" => $num, 
        "actions" => $uploads_array
    );
}
// now the table with all data is stuctured and you can parse it
foreach($user_array as $result) {
    $upload_html_link_arr = array();
    $user = $result['name'];
    $num_entry = $result['num_entry'];
    $all_actions_from_user_array = $result['actions'];
    foreach($all_actions_from_user_array as $upload) { 
        $upload_html_link_arr[] = sprintf('<a href="%s">%s</a>', $upload["file_url"],$upload["file_name"]);
    }
    $upload_html_link = implode(', ',$upload_html_link_arr);
    $full_row = sprintf("<tr><td>%s</td><td>uploads : %s</td><td>favourites (%d)</td></tr>", $user, $upload_html_link, $num_entry);
    // now just echo the full row or store it to a table for the final echo.
    echo $full_row;
}

我希望这有帮助，迈克