mysql-LEFT JOIN不返回所有行

问题描述

问题:我的LEFT JOIN没有返回左侧表格中的所有数据.

Problem: My LEFT JOIN is not returning all the data from the left table.

这是我的表模式:

CREATE TABLE item_to_map
  (
    id INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
    item_id INT UNSIGNED NOT NULL,
    map_id INT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NOT NULL,
    date DATETIME DEFAULT CURRENT_TIMESTAMP,

    INDEX(item_id),
    INDEX(map_id),
    INDEX(date),

    FOREIGN KEY (item_id)
      REFERENCES item (id)
        ON DELETE CASCADE,
    FOREIGN KEY (user_id)
      REFERENCES user (id)
        ON DELETE CASCADE,
    FOREIGN KEY (map_id)
      REFERENCES map (id)
        ON DELETE CASCADE
  );

CREATE TABLE vote_item
  (
    id INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
    user_id INT UNSIGNED NOT NULL,
    item_to_map_id INT UNSIGNED NOT NULL,
    date DATETIME DEFAULT CURRENT_TIMESTAMP,
    score ENUM("-1", "1"),

    INDEX(user_id),
    INDEX(item_to_map_id),
    INDEX(date),
    UNIQUE(user_id, item_to_map_id),

    FOREIGN KEY (item_to_map_id)
      REFERENCES item_to_map (id)
        ON DELETE CASCADE,
    FOREIGN KEY (user_id)
      REFERENCES user (id)
        ON DELETE CASCADE
  );

这是我到目前为止在 vote_item 中拥有的数据(忽略 user_id 映射到多个 item_to_map_id 的事实)

Here's the data I have so far in vote_item (ignore the fact that user_id maps to multiple item_to_map_id)

+----+---------+----------------+---------------------+-------+
| id | user_id | item_to_map_id | date                | score |
+----+---------+----------------+---------------------+-------+
|  1 |      12 |              1 | 2017-07-05 09:41:32 | 1     |
|  2 |      12 |              1 | 2017-07-05 09:41:32 | 1     |
|  3 |      12 |              1 | 2017-07-05 09:41:33 | 1     |
|  4 |      12 |              2 | 2017-07-05 09:45:29 | 1     |
|  5 |      12 |              2 | 2017-07-05 09:45:29 | -1    |
|  6 |      12 |              2 | 2017-07-05 09:45:29 | -1    |
|  7 |      12 |              3 | 2017-07-05 09:56:28 | 1     |
|  8 |      12 |              3 | 2017-07-05 09:56:29 | -1    |
+----+---------+----------------+---------------------+-------+

这是 item_to_map 表:

+----+---------+--------+---------+---------------------+
| id | item_id | map_id | user_id | date                |
+----+---------+--------+---------+---------------------+
|  1 |       1 |     20 |      12 | 2017-07-05 09:38:23 |
|  2 |       1 |     20 |      12 | 2017-07-05 09:38:23 |
|  3 |       1 |     20 |      12 | 2017-07-05 09:38:23 |
|  4 |       2 |     20 |      12 | 2017-07-05 09:38:23 |
|  5 |       2 |     20 |      12 | 2017-07-05 09:38:23 |
|  6 |       2 |     20 |      12 | 2017-07-05 09:38:24 |
+----+---------+--------+---------+---------------------+

我的尝试

SELECT
  i.id,
  i.item_id,
  v.item_to_map_id,
  IFNULL( SUM(CAST(CAST(v.score AS char) AS SIGNED)), 0 ) AS score
FROM item_to_map i
LEFT JOIN
  vote_item v ON i.id = v.item_to_map_id
GROUP BY v.item_to_map_id;

但这返回

+----+---------+----------------+-------+
| id | item_id | item_to_map_id | score |
+----+---------+----------------+-------+
|  4 |       2 |           NULL |     0 |
|  1 |       1 |              1 |     3 |
|  2 |       1 |              2 |    -1 |
|  3 |       1 |              3 |     0 |
+----+---------+----------------+-------+

我所期望的

我希望ID 5和ID 6也能显示

What I expected

I expected id 5 and 6 to also show up

推荐答案

通过所有(未汇总的)列进行汇总并解决您的问题:

Aggregate by all the (unaggregated) columns and fix your problem:

SELECT i.id, i.item_id, v.item_to_map_id,
       COALESCE( SUM(CAST(CAST(v.score AS char) AS SIGNED)), 0 ) AS score
FROM item_to_map i LEFT JOIN
     vote_item v
     ON i.id = v.item_to_map_id
GROUP BY i.id, i.item_id, v.item_to_map_id;

这篇关于mysql-LEFT JOIN不返回所有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！