MYSQL count of count？
MYSQL count of count？
提问于 2012-10-07 18:17:44
我有一个mysql表，如下所示：
id, visitorid, pageid当访问者访问网站时，它将访问者id和页面id存储为一行。
我试图提取的访问者的数量谁点击网站正好X次。(对于图表)。那么有多少人只访问一个页面，有多少人访问2个页面...
到目前为止，我有：
SELECT COUNT(visid),visid FROM vislog GROUP BY visid ORDER BY COUNT(visid) DESC但我不知道下一步该怎么计算。
可以像MySQL查询一样做吗？
回答 5
Stack Overflow用户
回答已采纳
发布于 2012-10-07 18:44:11
我可以这样解决它：
SELECT cnt, COUNT(cnt) FROM (
SELECT COUNT(visid) as cnt FROM vislog GROUP BY visid
) x GROUP BY cnt ORDER BY cnt ASC小x很重要。
Stack Overflow用户
发布于 2012-10-07 18:32:05
您可以将您的查询封装在另一个查询中：
SELECT
cnt AS page_visits
, COUNT(*) AS number_of_visitors
FROM
( SELECT
COUNT(*) AS cnt --- use: COUNT(DISTINCT page_id)
--- for a different count
FROM vislog
GROUP BY visid
) AS grp
GROUP BY cnt
ORDER BY number_of_visitors ;或者(我认为这对于将数字传递到图表更有意义)，删除与the相同的ORDER BY：
ORDER BY cnt ;Stack Overflow用户
发布于 2012-10-07 18:20:41
一种方法是将此查询封装到另一个查询中：
SELECT COUNT(visid) FROM (
SELECT COUNT(visid) AS cvisid, visid
FROM vislog
GROUP BY visid
HAVING cvisid = 2) AS c但是我认为你需要获得访问量的直方图:这可以用PHP来完成(假设查询与问题中的相同)：
$results = array();
// query preparation skipped, as it's obviously done by the OP himself
while ($row = $sth->fetch()) {
$count = $row['cvisid'];
if (isset($results[$count])) {
$results[$count]++;
}
else {
$results[$count] = 1;
}
}或者使用MySQL本身：
SELECT cvisid,
COUNT(cvisid) AS cnt
FROM (
SELECT visid,
COUNT(visid) AS cvisid
FROM vislog
GROUP BY visid ) AS c
GROUP BY cvisid页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/12767914
复制相关文章
相似问题
腾讯云开发者
