MySQL SELECT多个DISTINCT COUNT

本文介绍了MySQL SELECT多个DISTINCT COUNT的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

这就是我想要做的.我有一个带有用户评估的表，其中可能包含重复的行.我希望仅获取每个用户的DISTINCT值.

Here is what I'm trying to do. I have a table with user assessments which may contain duplicate rows. I'm looking to only get DISTINCT values for each user.

在下表的示例中.如果只有user_id 1和50属于特定位置，则应仅将每个用户的唯一video_id作为COUNT返回.用户1传递了视频1、2和1.因此，应该只有2条记录，而用户50传递了视频2.因此，该位置的总数为3.我想我需要在查询中包含两个DISTINCT's，但不确定如何执行此操作.

In the example of the table below. If only user_id 1 and 50 belongs to the specific location, then only the unique video_id's for each user should be returned as the COUNT. User 1 passed video 1, 2, and 1. So that should only be 2 records, and user 50 passed video 2. So the total for this location would be 3. I think I need to have two DISTINCT's in the query, but am not sure how to do this.

+-----+----------+----------+ | id | video_id | user_id | +-----+----------+----------+ | 1 | 1 | 1 | | 2 | 2 | 50 | | 3 | 1 | 115 | | 4 | 2 | 25 | | 5 | 2 | 1 | | 6 | 6 | 98 | | 7 | 1 | 1 | +-----+----------+----------+

这是我当前查询的样子.

This is what my current query looks like.

$stmt2 = $dbConn->prepare("SELECT COUNT(DISTINCT user_assessment.id) FROM user_assessment LEFT JOIN user ON user_assessment.user_id = user.id WHERE user.location = '$location'"); $stmt2->execute(); $stmt2->bind_result($video_count); $stmt2->fetch(); $stmt2->close();

因此，我的查询返回了该特定位置的所有计数，但并没有忽略每个特定用户的非唯一结果.

So my query returns all of the count for that specific location, but it doesn't omit the non-unique results from each specific user.

希望这很有意义，谢谢您的帮助.

Hope this makes sense, thanks for the help.

推荐答案

下面的查询加入了一个子查询，该子查询可为每个用户获取不同的视频.然后，主查询会对这些数字求和，以获取该位置的视频总数.

The query below joins a sub-query that fetches the distinct videos per user. Then, the main query does a sum on those numbers to get the total of videos for the location.

SELECT SUM(video_count) FROM user u INNER JOIN ( SELECT ua.user_id, COUNT(DISTINCT video_id) as video_count FROM user_assessment ua GROUP BY ua.user_id) uav on uav.user_id = u.user_id WHERE u.location = '$location'

请注意，由于您已经在使用绑定，因此还可以在绑定参数中传递$location.我将其留给您，因为这不是问题的一部分. ;-)

Note, that since you already use bindings, you can also pass $location in a bind parameter. I leave this to you, since it's not part of the question. ;-)