MySQL左连接显示不正确的数据

Question

我有5个表，它们的结构是一样的。只有PAGEVISITS字段是唯一的

即。表1：

ITEM |   PAGEVISITS   |  Commodity
1813       50            Griddle
1851       10            Griddle
11875      100           Refrigerator
2255       25            Refrigerator

即。表2：

ITEM |   PAGEVISITS   |  Commodity
1813       0             Griddle
1851       10            Griddle
11875      25            Refrigerator
2255       10            Refrigerator

我想让它把Commodity加起来，输出如下：

table1   |   table2   |  Commodity
60           10          Griddle
125          35          Refrigerator

有些数据实际上是正确的，但根据以下查询，有些数据是错误的：

SELECT
SUM(MT.PAGEVISITS) as table1,
SUM(CT1.PAGEVISITS) as table2,
SUM(CT2.PAGEVISITS) as table3,
SUM(CT3.PAGEVISITS) as table4,
SUM(CT4.PAGEVISITS) as table5,
(COUNT(DISTINCT MT.ITEM)) + (COUNT(DISTINCT CT1.ITEM)) + (COUNT(DISTINCT CT2.ITEM)) + (COUNT(DISTINCT CT3.ITEM)) + (COUNT(DISTINCT CT4.ITEM)) as Total,
MT.Commodity
    FROM table1 as MT
       LEFT JOIN table2 CT1
       on MT.ITEM = CT1.ITEM
       LEFT JOIN table3 CT2
       on MT.ITEM = CT2.ITEM
       LEFT JOIN table4 CT3
       on MT.ITEM = CT3.ITEM
       LEFT JOIN table5 CT4
       on MT.ITEM = CT4.ITEM
GROUP BY Commodity

我相信这可能是由于不正确地使用LEFT JOIN造成的。我也尝试过INNER JOIN，但结果并不一致。

Answer 1

我将对所有这五个表执行UNION，以将它们作为一个行集(内联视图)，然后对其运行查询，从如下所示开始...

SELECT SUM(IF(t.source='MT',t.pagevisits,0)) AS table1
     , SUM(IF(t.source='CT1',t.pagevisits,0)) AS table2
     , t.commodity
  FROM ( SELECT 'MT' as source, table1.* FROM table1 
          UNION ALL  
         SELECT 'CT1', table2.* FROM table2
          UNION ALL
         SELECT 'CT2', table3.* FROM table3
          UNION ALL
         SELECT 'CT3', table4.* FROM table4
          UNION ALL
         SELECT 'CT4', table5.* FROM table5
      ) t
GROUP BY t.commodity

(但我会为这些表中的每个指定列列表，而不是使用'.*‘并使我的查询依赖于没有人在这些表中添加/删除/重命名/重新排序列。)

我包含一个“额外的”字面值(别名为"source")来标识行来自哪个表。我可以在SELECT列表中的表达式中使用条件测试，以确定行是否来自特定的表。

这种方法特别灵活，可以用来获得更复杂的结果集。例如，如果我还想从table3获得总的页面访问次数，那么将4和5相加，以及单个计数。

SUM(IF(t.source IN ('CT2','CT3','CT4'),t.pagevisits,0) AS total_345

要获得与您的COUNT(DISTINCT item) + COUNT(DISTINCT item) + ...表达式等效的内容...

我将使用一个从"source“和"item”列生成单个值的表达式，小心地保证任何特定的"source"+"item“不会创建其他"source"+"item”的副本。(例如，如果我们只是连接字符串，我们没有任何方法来区分'A'+'11‘和’A1‘+’1‘。)我在这里看到的最常见的方法是仔细选择分隔符，它保证不会出现在这两个值中。我们可以区分'A::11‘和'A1::1'，所以像这样的东西可以工作：

 COUNT(DISINCT CONCAT(t.source,'::',t.item))

在当前查询中，如果item为NULL，则该行不包括在计数中。要完全复制该行为，您需要类似以下内容：

 COUNT(DISINCT IF(t.item IS NOT NULL,CONCAT(t.source,'::',t.item),NULL)) AS Total

当然，获取整个五个表的不同项值的计数要简单得多(但是，它确实返回了一个不同的结果)

 COUNT(DISINCT t.item)

但是为了回答您关于使用LEFT JOIN的问题，左侧的表是“驱动程序”，因此必须在该表中有匹配的行，才能从右侧的表中检索相应的行。也就是说，不会返回右侧表中不匹配的行。

如果您拥有的基本上是五个“分区”，并且您希望处理所有行，而不管匹配的行是否出现在任何其他“分区”中，那么我将使用UNION ALL方法，简单地将所有这些表中的所有行连接在一起，并处理这些行，就像它们来自单个表一样。

注意:对于非常大的表，这可能不是一个可行的方法，因为MySQL将不得不实现该内联视图。还有其他不需要将所有行连接在一起的方法。

如果那些表中有您不需要在查询中引用的列，则在SELECT from each table中指定仅包含您需要的列的列表可能有助于提高性能。