php - 如何在列 SQL 中获取 5 个最新值和计数

Question

根据表格，我必须计算前 3 个最频繁的 bookID 和 bookID 的计数。

我已经想出了如何获得前 3 名，但是我不知道如何在逗号后的括号中显示计数的数量。

我必须将它们放在括号中，用逗号分隔，如下所示：

[top1 bookid , the number of count] => [17,6]
[top2 bookid , the number of count] => [13,6]
[top3 bookid , the number of count] => [16,5]

$query ="SELECT bookID FROM issue_books GROUP BY bookID ORDER BY COUNT(*) DESC LIMIT 3 ";

$query_run = mysqli_query($connection,$query);

  foreach ($query_run as $row)
  { 
    echo '['". $row['bookID'];.",".xxxxx."']';
  }

有没有办法一起显示频率数的计数？

标签： phpsql

Answer 1

将计数添加到选择子句？

$query = "SELECT bookID, COUNT(*) AS cnt FROM issue_books GROUP BY bookID ORDER BY cnt DESC LIMIT 3 ";
$query_run = mysqli_query($connection, $query);
foreach ($query_run as $row) {
    echo "[" . $row['bookID'] . ", " . $row['cnt'] . "]\n";
}