mysql不计算重复行 [英] mysql do not count duplicate rows

问题描述

我有一个名为ptb_profile_views的表；

i have a a table called ptb_profile_views;

id  |  profile_id  |  viewed_profile  |  date

1          1               5
2          1               5
3          1               5
4          2               5
5          3               5

好，因此在此示例中，用户1,2和3正在检出用户5的个人资料，

ok so in this example users 1,2 ans 3 are checking out user 5's profile,

我在这里获得的mysql假设是要计算用户获得的视图数，但是在用户1多次检出用户5的情况下，我不想返回重复的行，

the mysql i've got here is suppose to count the number of views a user has got, but where user 1 has checked out user 5 several times i do not want to return duplicate rows,

因此，与其说用户5已被查看5次，不如说他们实际上只被查看过3次，因为我只想计算不同的值.

so instead of saying user 5 has been viewed 5 times, they will have only actually been viewed 3 times because i only want to count distinct values.

这是我的mysql有人可以帮我吗:

heres my mysql can someone please help me:

function check_profile_views() {
            global $connection;
            global $_SESSION;
            $query = "SELECT COUNT(profile_id) FROM ptb_profile_views WHERE viewed_profile_id=".$_SESSION['user_id']." AND profile_id!='0'";
            $check_profile_views_set = mysql_query($query, $connection);
            confirm_query($check_profile_views_set);
            return $check_profile_views_set;        
        }

php:

$check_profile_views_set = check_profile_views();
while ($views = mysql_fetch_array($check_profile_views_set)) {  
?>
<? echo"". $views['COUNT(profile_id)'] ."";?> viewed your profile

推荐答案

只需添加DISTINCT

SELECT COUNT(DISTINCT profile_id) totalCOUNT FROM ptb_profile_views...

并回显别名，

$views['totalCOUNT']

• SQLFiddle演示
• SQLFiddle Demo

这篇关于mysql不计算重复行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！