mysql - 复杂的 MySQL JOIN SELECT

Question

好的，在开始之前，我先声明一下:

1. 我没有创建该表。
2. 无法更改表格。
3. 问题按“原样”描述。没有遗漏任何重要的内容。

考虑 2 个表:

CREATE TABLE `answers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `question_id` int(11) NOT NULL DEFAULT '0',
  `user_id` int(11) NOT NULL DEFAULT '0',
  `text` TEXT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

CREATE TABLE `rating` (
  `answer_id` int(11) NOT NULL DEFAULT '0',
  `direction` varchar(10) NOT NULL DEFAULT '',
  `user_id` int(11) NOT NULL DEFAULT '0',
  UNIQUE KEY (`answer_id`, `user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

现在是烦人的部分。答案表是不言自明的，所以我只给出一些评级行的示例

+-------------+-------------+-----------+
|  answer_id  |  direction  |  user_id  |
+-------------+-------------+-----------+
|      1      |    up       |    23     |
|      1      |    down     |    26     |
|      2      |    up       |    32     |
|      2      |    up       |    42     |
|      2      |    up       |    22     |
|      2      |    down     |    23     |
|      3      |    up       |    45     |
|      3      |    up       |    22     |
+-------------+-------------+-----------+

现在我想要做的是选择所有答案，其中每个答案对应的“向上”COUNT、“向下”COUNT 以及“向上”和“向下”COUNT 之间的差值。

假设upcount是“向上”的总数，downcount是“向下”的总数，score是它们的差值，类似:

SELECT [SOMETHING?] AS `upcount`,
[SOMETHING?] AS `downcount`,
(`upcount`-`downcount`) AS `score`,
ans.* FROM answers AS ans LEFT JOIN rating AS r ON ans.id=r.answer_id
[...] ORDER BY `score`, `upcount`

当然，我强烈怀疑该解决方案是否会像我给出的示例一样。我只是想说明每条记录都应该有加计数、减计数、分数和所有答案字段。

非常感谢任何帮助，谢谢!

ps。我知道如果上下是两个独立的列并且带有数值，我可以轻松使用 SUM()。但就像我说的，我没有创建这个表;)

Answer 1

可能是带有 GROUP BY 子句的简单联接，但基于 IF 语句进行汇总，而不是执行计数:-

SELECT a.id, 
        SUM(IF(b.direction='up', 1, 0)) AS upcount, 
        SUM(IF(b.direction='down', 1, 0)) AS downcount,
        SUM(IF(b.direction='up', 1, -1)) AS score
FROM answers a
LEFT OUTER JOIN rating b
ON a.id = b.answer_id
GROUP BY a.id
ORDER BY score, upcount

编辑 - 避免使用 SUM 的 IF 语句的另一种可能的解决方案是连接评级表两次并使用 COUNT(DISTINCT )。由于 user_id 对于答案来说是唯一的，因此我们可以计算不同的用户 ID。缺点是获得总体分数有点困惑。

SELECT a.id, 
        COUNT(DISTINCT b1.user_id) AS upcount, 
        COUNT(DISTINCT b2.user_id) AS downcount, 
        (COUNT(DISTINCT b1.user_id) - COUNT(DISTINCT b2.user_id)) AS score
FROM answers a
LEFT OUTER JOIN rating b1
ON a.id = b1.answer_id
AND b1.direction='up'
LEFT OUTER JOIN rating b2
ON a.id = b2.answer_id
AND b2.direction='down'
GROUP BY a.id
ORDER BY score, upcount