外部联接未提供预期结果 [英] Outer join not giving expected result

问题描述

我有三个表，我想计算每个表中每个度假村的记录数.我收到无法解释的意外结果.

I have three tables and I want to count the number of records for each resort in each of the table. I'm getting an unexpected result that I cannot explain.

我的表格如下:

CREATE TABLE `game_items` (
  `id_items` int(11) NOT NULL,
  `id_resort` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `game_items` (`id_items`, `id_resort`) VALUES
(36, 81),
(38, 81),
(39, 67);

CREATE TABLE `game_slopes` (
  `id_slopes` int(11) NOT NULL,
  `id_resort` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `game_slopes` (`id_slopes`, `id_resort`) VALUES
(16, 81);

CREATE TABLE `game_staff` (
  `id_staff` int(11) NOT NULL,
  `id_resort` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `game_staff` (`id_staff`, `id_resort`) VALUES
(1, 69),
(3, 67),
(5, 81),
(7, 81),
(8, 81),
(12, 81);

CREATE TABLE `game_resorts` (
  `id_resort` int(11) NOT NULL,
  `id_player` int(11) DEFAULT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `game_resorts` (`id_resort`, `id_player`) VALUES
(66, 59),
(67, 60),
(68, 61),
(69, 62),
(70, 63),
(81, 67),
(82, 68);

我的查询

SELECT `game_players_tbl`.`id_player`, `game_resorts`.`id_resort`,
COUNT(game_items_tbl.id_items) as item_count,
COUNT(game_slopes_tbl.id_slopes) as slope_count,
COUNT(game_staff_tbl.id_staff) as staff_count
FROM `game_resorts`
INNER JOIN `game_players` as `game_players_tbl` ON `game_resorts`.`id_player` = `game_players_tbl`.`id_player`
LEFT OUTER JOIN `game_items` as `game_items_tbl` ON `game_resorts`.`id_resort` = `game_items_tbl`.`id_resort`
LEFT OUTER JOIN `game_slopes` as `game_slopes_tbl` ON `game_resorts`.`id_resort` = `game_slopes_tbl`.`id_resort`
LEFT OUTER JOIN `game_staff` as `game_staff_tbl` ON `game_resorts`.`id_resort` =`game_staff_tbl`.`id_resort`
GROUP BY `game_resorts`.`id_resort`
ORDER BY `game_resorts`.`reputation` DESC

结果是:

id_player   id_resort   item_count  slope_count     staff_count     
61  68  0   0   0   
63  70  0   0   0   
67  81  8   8   8   
68  82  0   0   0   
62  69  0   0   1   
59  66  0   0   0   
60  67  1   0   1   

但我希望:

id_player   id_resort   item_count  slope_count     staff_count     
61  68  0   0   0   
63  70  0   0   0   
67  81  2   1   4   
68  82  0   0   0   
62  69  0   0   1   
59  66  0   0   0   
60  67  1   0   1   

我不明白为什么我的度假胜地ID 81每次都能得到8.我尝试了其他方法，但从未得到正确的结果.

I don't understand why I get 8 in each count for resort ID 81. I've tried different alternatives but never get the correct result.

添加了game_resorts

Added game_resorts

推荐答案

您遇到的主要问题是表game_items具有game_resorts的多个记录.这导致您复制所有连接到game_resorts表的数据.正如@Jorge Campos所说，最好为每个表创建单独的计数，然后将它们加入到您的Resorts表中.

The main issue you have is your table game_items has multiples records for the game_resorts. This is causing you to duplicate all your data that is joined to the game_resorts table. As @Jorge Campos stated, it would be best for you to create separate counts for each table and then join them to your resorts table.

SQL查询

SELECT `game_players_tbl`.`id_player`, `game_resorts`.`id_resort`,
game_items_tbl.Count AS item_count,
game_slopes_tbl.Count AS slope_count,
game_staff_tbl.Count AS staff_count
FROM `game_resorts`
INNER JOIN `game_players` AS `game_players_tbl` ON `game_resorts`.`id_player` = `game_players_tbl`.`id_player`
LEFT OUTER JOIN (
    SELECT  `game_items`.`id_resort`, COUNT(`game_items`.`id_items`) AS Count FROM `game_items` GROUP BY `game_items`.`id_resort`
) AS `game_items_tbl` ON `game_resorts`.`id_resort` = `game_items_tbl`.`id_resort`
LEFT OUTER JOIN (
    SELECT  `game_slopes`.`id_resort`, COUNT(`game_slopes`.`id_slopes`) AS Count FROM `game_slopes` GROUP BY `game_slopes`.`id_resort`
) AS `game_slopes_tbl` ON `game_resorts`.`id_resort` = `game_slopes_tbl`.`id_resort`
LEFT OUTER JOIN (
    SELECT  `game_staff`.`id_resort`, COUNT(`game_staff`.`id_staff`) AS Count FROM `game_staff` GROUP BY `game_staff`.`id_resort`
) AS `game_staff_tbl` ON `game_resorts`.`id_resort` =`game_staff_tbl`.`id_resort`
GROUP BY `game_resorts`.`id_resort`
ORDER BY `game_resorts`.`reputation` DESC

我不再懒惰，而是继续对剩余的所有表进行计数.

Instead of being lazy, I went ahead and performed counts all the remaining tables.

修复了最后一个子查询，以纠正@remyremy所述的表

Fixed last subquery to correct table as stated by @remyremy

这篇关于外部联接未提供预期结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！