对于保存用户网页访问行为记录的表,如何选择访问多个网页的用户。
该表的结构为:
userId webpageId visitTime 0 123 ... 0 124 ... 1 123 ... ... ... ...
我可以使用以下方法计数:
SELECT userId, COUNT(DISTINCT webpageId) AS count FROM visits GROUP BY userId;
它给我的结果是:
userId count 0 2 1 1 2 6 ... ...
我该如何执行查询,使我得到最终结果,例如:
userId 0 2 ...
每个都是访问多个DISTINCT网页的用户
只需添加having子句
SELECT userId, COUNT(DISTINCT webpageId) AS count FROM visits GROUP BY userId HAVING COUNT(DISTINCT webpageId) > 1
但是如果你只是 ID
ID
SELECT userId FROM visits GROUP BY userId HAVING COUNT(DISTINCT webpageId) > 1
之所以筛选onHAVING子句而不是onWHERE的原因是因为,WHERE子句不能支持 聚集了的 列。
HAVING
WHERE