MySQL 选择用户在mysql中有多个不同的记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15237740/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
select users have more than one distinct records in mysql
提问by zoujyjs
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 网页的用户
回答by John Woo
just add having clause
只需添加 have 子句
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
the reason why you are filtering on HAVINGclause and not on WHEREis because, WHEREclause cannot support columns that where aggregated.
您过滤HAVING子句而不是过滤的原因WHERE是，WHERE子句不能支持聚合的列。
回答by Iswanto San
回答by Iswanto San
While HAVINGis 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 HAVINGis 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/版本的计划。）
