mysql/mariadb - LEFT JOIN 聚合不返回所有值

Question

首先，我不知道如何表达这个问题，所以如果有更好的术语或措辞，请随时编辑。

<小时/>

这是我的架构:http://sqlfiddle.com/#!9/ca46c1/2

CREATE TABLE map
  (
    id INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT
  );

CREATE TABLE vote_map
  (
    id INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
    user_id INT UNSIGNED NOT NULL,
    map_id INT UNSIGNED NOT NULL,
    score ENUM("-1", "0", "1")
  );

 CREATE VIEW view_vote_map_rank AS
  SELECT
    map.id AS map_id,
    COALESCE( SUM(CAST(CAST(score AS char) AS SIGNED)), 0) AS score
  FROM vote_map
  RIGHT JOIN
    map ON map.id = vote_map.map_id
  GROUP BY map_id;


INSERT INTO map (id) VALUES (1);
INSERT INTO map (id) VALUES (2);
INSERT INTO map (id) VALUES (3);
INSERT INTO map (id) VALUES (4);
INSERT INTO map (id) VALUES (5);

INSERT INTO vote_map (user_id, map_id, score) VALUES (1, 1, '1');
INSERT INTO vote_map (user_id, map_id, score) VALUES (2, 2, '1');

SELECT * FROM map;
SELECT * FROM view_vote_map_rank;

我得到的结果是

map_id  score
3   0
1   1
2   1

但这并不完整。我还期望 id 4 和 5 也在那里，得分也是 0 。我不确定为什么它在第一个 0 之后停止。我错过了什么？

Answer 1

在 View 中，您可以使用以下内容:

SELECT 
  a.id,
  sum(IF(b.score IS NULL,0,b.score)) as `score`
FROM map a
LEFT JOIN vote_map b
ON a.id = b.map_id
GROUP BY a.id

更简单，在您的 SQL Fiddle 中，它似乎返回正确的结果