mysql - SUM 职位，以便越高越好

Question

我有三个表。变化、讨论和对话。每个讨论都有一个对话集合。每个对话都是关于一个变化。然后对话在讨论中排名 1-10。你最终会得到这样的结果:

  Conversations{
    [ id: 1, discussion_id: 1, change_id 1, rank: 1 ],
    [ id: 1, discussion_id: 1, change_id 3, rank: 3 ],
    [ id: 1, discussion_id: 1, change_id 5, rank: 2 ],
    [ id: 1, discussion_id: 1, change_id 2, rank: 4 ],
    [ id: 1, discussion_id: 1, change_id 4, rank: 5 ],
    [ id: 1, discussion_id: 2, change_id 3, rank: 1 ],
    [ id: 1, discussion_id: 2, change_id 2, rank: 2 ],
    [ id: 1, discussion_id: 2, change_id 4, rank: 5 ],
    [ id: 1, discussion_id: 2, change_id 5, rank: 3 ],
    [ id: 1, discussion_id: 2, change_id 1, rank: 4 ]
  }

我想找到得分最高的变化。当我想出这个查询时，我以为我已经做到了:

  SELECT changes.*, SUM(conversations.rank) AS score FROM "changes" 
        INNER JOIN "conversations" ON "conversations"."change_id" = "changes"."id" 
        GROUP BY changes.id

这会将所有排名相加。这意味着越低越好。然而，这个相当原始的解决方案有其缺陷:如果你给一个变化排名 1 和 3，它最终得到的分数与你给一个变化排名 4 的分数相同。

也许要解决这个问题，您可以让它变得越高越好:第一名 = 5 分，第二名 = 4 分，等等。我不知道如何实现这个。这也可能不是最好的方法。

Here您可以找到上下文的 SQL fiddle 。仅供引用:我正在使用事件记录进行此操作。我使用这个生成了查询:

Change.select("changes.*, SUM(conversations.rank) as conversations_rank").group("changes.id").joins(:conversations)

Answer 1

我想出了两种方法。两者都很简单。我只是有点傻:)

第一个，也可能是最简单的一个:

SELECT changes.name, SUM(11 - conversations.rank) as score FROM "changes" 
      INNER JOIN "conversations" 
        ON "conversations"."change_id" = "changes"."id" 
      GROUP BY changes.id 
      ORDER BY score desc

简单的数学嗯。所以如果 conversations.rank = 1，那么它将是 10，因为 1 减去 11 是 10。

也许更好的方法是使用另一张表:

CREATE TABLE ranks (
  rank INT UNSIGNED PRIMARY KEY, 
  value INT UNSIGNED
);

INSERT INTO ranks 
       VALUES (1, 5),
              (2, 4),
              (3, 3),
              (4, 2),
              (5, 1);

然后在 conversations.rank 上加入行列:

SELECT changes.name, SUM(ranks.value) as score FROM "changes"
      INNER JOIN "conversations"
        ON conversations.change_id = changes.id
      INNER JOIN "ranks"
        ON conversations.rank = r.rank
      GROUP BY changes.name;

感谢所有的帮助。