合计mysql php,PHP-MYSQL唯一值与总计

尝试返回已解决故障单的所有我的用户的列表以及解决的故障单数量.这段代码有效,但是在while循环内查询似乎是一种不好的做法.

有没有更有效的方法？

//Get all users who have resolved a ticket

$stmt = $db->prepare("SELECT DISTINCT resolvedby FROM tickets");

$stmt->execute();

while($rows = $stmt->fetch()){

$user = $rows['resolvedby'];

echo "$user "; //Echo each user

$new = $db->prepare("SELECT count(resolvedby) as total FROM tickets WHERE resolvedby = :u ");

$new->bindParam('u',$user);

$new->execute();

$row = $new->fetch();

echo $row['total']; //Echo the total of each user

echo "
";

}

返回值：

User Total Resolved

Shawn 40

David 38

Jeff 52

Frank 47

解决方法:

您需要使用汇总功能计数分组：

SELECT

resolvedby

, COUNT(*) AS total

FROM

tickets

GROUP BY

resolvedby

当然,您想使用一些WHERE子句来限制结果.

标签：count,distinct,mysql,php

来源： https://codeday.me/bug/20191024/1922882.html