我想对计数的分组结果进行SQL查询。我能够做到这一点的唯一方法是在PHP的帮助下。但我确信可以在一个 SQL 语句中做到这一点。
该表的结构如下： id、user_hash、timestamp
我的小PHP解决方法如下所示：
$sql = 'SELECT count(id) AS ''total clicks'' FROM stats WHERE timestamp > UNIX_TIMESTAMP(NOW() - INTERVAL '.$num_days.' DAY) GROUP BY user_hash ORDER BY count(id) DESC';
$result = $mysqli->query($sql);
$count = array();
while ($row = mysqli_fetch_assoc($result)) {
if (isset($count[$row['total clicks']])) {
$count[$row['total clicks']]++;
} else {
$count[$row['total clicks']] = 1;
}
}
foreach ($count as $k => $v) {
echo $v." users clicked ".$k." times'n";
}
我可能会自责这是多么容易，但我似乎无法获得多合一的SQL解决方案。 :)
请尝试：
$sql = 'SELECT
A.`user_times_clicked`,
COUNT(A.user_hash) AS `user_count`
FROM(
SELECT
count(id) AS `user_times_clicked`,
user_hash
FROM
stats
WHERE
timestamp > UNIX_TIMESTAMP(NOW() - INTERVAL '.$num_days.' DAY)
GROUP BY
user_hash
) AS A
GROUP BY
A.`user_times_clicked`
ORDER BY
COUNT(A.user_hash) DESC'
我希望它有所帮助。