select用户在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.

对于包含用户网页访问行为记录的表，如何选择访问多个网页的用户。

The structure of this tables is:

这个表的结构是：

userId        webpageId       visitTime
  0              123            ...
  0              124            ...
  1              123            ...
 ...             ...            ...

I can count using:

我可以算一下：

SELECT userId, COUNT(DISTINCT webpageId) AS count FROM visits GROUP BY userId;

It gives me the result like:

它给我的结果如下：

userId          count
  0               2
  1               1
  2               6
 ...             ...

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

如何执行查询，给出最终结果，如：

userId
  0
  2
 ...

each is user that visit more than one DISTINCT webpages

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

3 个解决方案

#1

just add having clause

只需添加having子句

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

but if you only what the ID

但如果你只是ID

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

SQLFiddle Demo
SQLFiddle演示

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

您之所以过滤HAVING子句而不是WHERE是因为，WHERE子句不支持聚合的列。

#2

Try this:

尝试这个：

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

More: HAVING

更多：HAVING

#3

While HAVING is a good approach in this case, remember that queries can be nested:

虽然在这种情况下HAVING是一种很好的方法，但请记住查询可以嵌套：

SELECT userId, pageCount
FROM (
    SELECT userId, COUNT(DISTINCT webpageId) AS pageCount
    FROM visits 
    GROUP BY userId) AS n
WHERE pageCount > 1

The actual query plans may differ, especially if HAVING is an optimized case, but there is no reason why the plans must be different. (Compare plans on the specific RDBMS/version if it is an issue or concern.)

实际的查询计划可能会有所不同，特别是如果HAVING是一个优化案例，但没有理由为什么计划必须不同。（比较特定RDBMS /版本的计划，如果它是一个问题或关注点。）

#1