Mysql 跟踪用户的喜好和厌恶

Question

我正在创建一个公告板应用程序。每个公告都可以被网站的用户喜欢或不喜欢。为了跟踪喜欢和不喜欢，我创建了以下数据库表

id  user_id   bulletin_id   like_dislike
1   1         1             1
2   1         2             0
3   3         1             1
4   2         1             0

在 like_dislike 列中 1 表示“喜欢”，0 表示“不喜欢” 我知道怎么问。 - 公告 1 被点赞了多少次 (2) - 公告 1 被不喜欢了多少次 (1)

但是我如何进行查询以同时提出这两个问题？也就是说，公告 1 被喜欢和不喜欢的次数

liked  disliked
2      1

我已经尝试过该查询

  SELECT count(like_dislike) AS likes, count(like_dislike) AS dislikes FROM bulletins_ld
where bulletins_id = 1 AND likes = 1 AND dislikes = 0

，但我得到的只是两次，这并不奇怪。
我能想到的唯一解决方案是有一个单独的喜欢和不喜欢的专栏

Answer 1

您可以通过聚合查询来完成此操作，在单个 like_dislike 列上使用相反的条件（我假设该列中的“1”表示“喜欢”）。

SELECT bulletin_id, 
       SUM(CASE WHEN like_dislike = 1 THEN 1 ELSE 0 END) AS likes, 
       SUM(CASE WHEN like_dislike = 0 THEN 1 ELSE 0 END) AS dislikes
FROM bulletins_ld
GROUP BY bulletin_id

更新：根据下面评论中的讨论，喜欢/不喜欢列可以规范化到自己的表中，如下所示（故意愚蠢的示例......）：

CREATE TABLE how_user_feels(
    feeling_id INT,
    feeling_desc VARCHAR(20)
)

INSERT INTO how_user_feels(feeling_id, feeling_desc) VALUES
(0, 'Undecided'),
(1, 'Likes It'),
(2, 'Could Do Without It')

公告表中的 Likes_Dislikes 列是然后用外键feeling_id替换，默认为0。假设当用户第一次查看公告时，您在此表中输入一条记录，默认情况下将其设置为“未决定”，然后更新他们对公告进行投票时的记录。您可以像这样查询结果：

SELECT bulletin_id, 
       SUM(CASE WHEN feelings_id = 1 THEN 1 ELSE 0 END) AS likes, 
       SUM(CASE WHEN feelings_id = 2 THEN 1 ELSE 0 END) AS dislikes,
       SUM(CASE WHEN feelings_id = 0 THEN 1 ELSE 0 END) AS doesnt_seem_to_care
FROM bulletins_ld b
INNER JOIN how_user_feels h ON b.feeling_id = h.feeling_id
GROUP BY bulletin_id

请记住，这只是一种方法，可能对您的情况没有用。但是，如果您决定更改或扩展用户表达对公告的感受的模型（例如五星级评级系统），那么您无需更改数据库架构 - 只需更改 how_user_feels 表中的记录以及关联的查询。

Answer 2

1. 郑重声明，还有另一种方法可以获取相同的结果集，更快或更慢取决于平台。这使用标量子查询而不是外部查询上的聚合。这个想法是，如果你从集合的角度思考，这应该会更容易。或者就维度事实而言。

   首先我们必须理顺你们的名字。让我们将您的“表”命名为 bulletin_like 和主公告表 bulletin（bulletin_id 对于它们中的任何一个来说都是一个非常愚蠢的名称，更多的是列名称）。只需调用布尔列 like （如果为 1，like 为 true；如果为 0，like 为 false；这就是 boolean方法）。名称使用单数形式。

   选择名称 AS 公告， 
       （选择 COUNT（喜欢） 
           来自Bulletin_like bl 
           其中 bl.bulletin_id = b.bulletin_id 
           喜欢 = 1
           ）就像，
       （选择 COUNT（喜欢） 
           来自Bulletin_like bl 
           其中 bl.bulletin_id = b.bulletin_id 
           喜欢 = 0
           ）作为不喜欢
   FROMBulletin b

2. 您要求标准化标签。该bulletin_like“表”未规范化。去掉Idiot 列，它除了冗余列和附加索引之外没有任何用途。 PK 为(bulletin_id, user_id)。

   除非您希望用户在每个公告的每个海报上发布多个喜欢和不喜欢的内容。

Answer 3

这个查询对我有用：

SELECT bulletin_id, 
    sum(like_dislike) AS likes, 
    sum((1-like_dislike)) AS dislikes 
FROM bulletins_ld 
    GROUP BY (bulletin_id);

假设 likedislike = 1 表示“喜欢它”，likedislike = 0 表示“不喜欢它”。

ID    LIKES    DISLIKES
1     2    1
2     0    1