mysql需要几个distinct,MySQL SELECT多个DISTINCT COUNT

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.

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'

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