时间戳

首页 > 解决方案 > 分组前只获取唯一记录

mysql - 分组前只获取唯一记录

问题描述

我有一个名为的表votes，该表可能包含具有这些列的重复记录business_id和user_id. 我创建了一个视图，将显示每个企业的票数，但不幸的是，重复的数据也被计算在内，这不应该被计算在内。下面是我的查看代码

(
    SELECT
        `v`.`business_id` AS `business_id`,
        COUNT(`v`.`vote_id`) AS `num_votes`
    FROM
        `connectn_top100`.`votes` `v`
    WHERE
        (`v`.`year` = 2019)
    GROUP BY
        `v`.`business_id`
)

实际数据将是

------------------------------------
id | business_id | user_id | vote_id
------------------------------------
1  | 12          |  12     | 1      
------------------------------------
2  | 12          |  12     | 1      
------------------------------------
3  | 12          |  12     | 1      
------------------------------------
4  | 13          |  15     | 1      
------------------------------------
5  | 13          |  15     | 1      
------------------------------------
6  | 12          |  16     | 1      
------------------------------------
7  | 23          |  16     | 1      
------------------------------------

所以结果应该是

------------------------------------
id | business_id | user_id | vote_id
------------------------------------
1  | 12          |  12     | 1      
------------------------------------
4  | 13          |  15     | 1      
------------------------------------
6  | 12          |  16     | 1      
------------------------------------
7  | 23          |  16     | 1      
------------------------------------

所以它应该是 user_id 必须有唯一的 business_id 和 business_id 必须有唯一的 user_id

尝试添加另一个 GROUP BY 但似乎是一个错误。

标签： mysqlsqlgroup-by

解决方案

如果你想每个user_id只计算一次business_id，你可以使用count(distinct ...)：

SELECT
    `v`.`business_id` AS `business_id`,
    COUNT(DISTINCT `v`.`user_id`) AS `num_votes`
FROM `connectn_top100`.`votes` `v`
WHERE `v`.`year` = 2019
GROUP BY `v`.`business_id`

编辑：从您的示例数据中，看起来您想要：

SELECT
    `v`.`business_id` AS `business_id`,
    `v`.`user_id`,
    COUNT(DISTINCT `v`.`vote_id`) AS `num_votes`
FROM `connectn_top100`.`votes` `v`
WHERE `v`.`year` = 2019
GROUP BY `v`.`business_id`, `v`.`user_id`

推荐阅读