选择用户在mysql中有多个不同的记录 [英] select users have more than one distinct records in mysql

问题描述

对于一个保存用户网页访问行为记录的表，我该如何选择访问多个网页的用户.

For a table that holds the records of user's webpages visiting behavior, how can I select users that visit more than one webpages.

此表的结构为:

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
 ...             ...

我该如何查询能得到最终结果的查询，如:

How can I excute query that gives me the final result like:

userId
  0
  2
 ...

每个都是访问多个DISTINCT网页的用户

each is user that visit more than one DISTINCT webpages

推荐答案

只需添加Haveing子句

just add having clause

SELECT userId, COUNT(DISTINCT webpageId) AS count 
FROM visits 
GROUP BY userId
HAVING COUNT(DISTINCT webpageId) > 1

但是如果您只使用ID

SELECT userId
FROM visits 
GROUP BY userId
HAVING COUNT(DISTINCT webpageId) > 1

• SQLFiddle演示
• SQLFiddle Demo

之所以要对HAVING子句而不是对WHERE进行过滤，是因为WHERE子句不能支持聚集的列.

the reason why you are filtering on HAVING clause and not on WHERE is because, WHERE clause cannot support columns that where aggregated.

这篇关于选择用户在mysql中有多个不同的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！