如果列数据重复，mysql不会对行计数两次 [英] mysql don't count row twice if column data is duplicated

问题描述

我正在计数名为ptb_profile_views的mysql表中的行.

I am counting the rows in a mysql table called ptb_profile_views.

我的桌子看起来像这样:

My table looks like this:

id  |  profile_id   | viewed_profile_id  |  date_time  |

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

此刻，我的查询计数了表中的所有行.但是，我只希望它计算profile_id中不包含重复值的行.

At the moment my query counts all the rows in the table. However, I only want it to count the rows which do not contain duplicate values in profile_id.

因此，如果用户2/profile_id 2多次查看用户6的个人资料，在这种情况下，他们这样做，我只希望查询对它进行一次计数.我正在尝试使用distinct，但无法正常工作.有人可以告诉我我要去哪里了吗?

So if user 2/profile_id 2 views user 6's profile several times, which in this case they do, I only want the query to count it once. I am trying to use distinct but its not working. Can someone please show me where I'm going wrong?

function check_profile_views() {
    global $connection;
    global $_SESSION;
    $query = "SELECT DISTINCT COUNT(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;        
}

推荐答案

尝试以下操作(假设viewed_profile_id为6):

Try the following (assuming viewed_profile_id is 6):

SELECT COUNT(DISTINCT profile_id) FROM ptb_profile_views WHERE viewed_profile_id=6

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