尝试返回已解决故障单的所有我的用户的列表以及解决的故障单数量.这段代码有效,但是在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