MySQL MySQL选择不同计数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15710930/
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
MySQL Select Distinct Count
提问by Jako
+----------+----------+
| user_id | video_id |
+----------+----------+
| 1 | 1 |
| 1 | 1 |
| 1 | 2 |
| 2 | 1 |
| 2 | 2 |
+----------+----------+
I have a table setup similar to the one above. I would like to return a total count from my query.
我有一个类似于上面的表设置。我想从我的查询中返回一个总数。
For each user_idthey need to have a DISTINCT video_id. So above, user_id = 1would have 2 unique video_id's and user_id = 2would have 2 unique video_id's. This would make the total 4. I'm not sure the best way to organize my query to achieve the desired result.
对于每个user_id他们需要有一个DISTINCT video_id. 所以在上面，user_id = 1会有 2 个唯一的 video_id 和user_id = 22 个唯一的 video_id。这将使总数为 4。我不确定组织查询以实现所需结果的最佳方式。
Basically for each user_id, I need something like, COUNT(DISTINCT video_id)
基本上对于每个 user_id，我需要类似的东西， COUNT(DISTINCT video_id)
I would like the final result just to return total count of everything.
我希望最终结果只是返回所有内容的总数。
回答by Taryn
If you want to get the total count for each user, then you will use:
如果您想获得每个用户的总数，那么您将使用：
select user_id, count(distinct video_id)
from data
group by user_id;
Then if you want to get the total video_ids then you will wrap this inside of a subquery:
然后，如果你想获得总的 video_ids 那么你将把它包装在一个子查询中：
select sum(cnt) TotalVideos
from
(
select user_id, count(distinct video_id) cnt
from data
group by user_id
) d
See SQL Fiddle with Demoof both.
请参阅SQL Fiddle 和两者的演示。
The first query will give you the result of 2for each user_idand then to get the total of all distinct video_ids you sum the count.
第一个查询将为您2提供每个的结果，user_id然后获得所有不同 video_id 的总数，您将计数相加。
回答by Trenton Trama
select user_id, count(distinct video_id) from TABLE group by user_id;
回答by user3242558
For total count...
对于总数...
select distinct count(video_id) from Table
where...
group...
回答by bazeusz
For now a total count of unique user_id, video_id pairs can be calculated with the following query
现在，可以使用以下查询计算唯一 user_id、video_id 对的总数
select count(distinct user_id, video_id) from table;
