左外部加入丢失的记录

问题描述：

我从几个表中选择数据，但主要想法是产品可能有也可能没有与之相关的折扣记录，如折扣或美元折扣金额。我正在使用左外连接（这可能不正确），并且无论是否存在记录，都会返回相同的美元值和百分比值。左外部加入丢失的记录

查询看起来像：

SELECT Items.ItemID, Items.name, Items.price, 
     ItemDiscounts.percentOff, ItemDiscounts.dollarOff, 
     ItemAttributes.ColorName, ItemStuff.StuffID 
FROM Items, ItemAttributes, ItemStuff 
LEFT OUTER JOIN ItemDiscounts 
    ON ItemDiscounts.ItemID = ItemID 
    AND (
    ItemDiscounts.percentOff > 0 
    OR ItemDiscounts.dollarOff > 0 
) 
WHERE Items.ItemID = ItemAttributes.ItemID 
AND ItemStuff.ItemID = Items.ItemID 
GROUP BY ItemStuff.StuffID

怪异的是，在所有的结果，percentOff返回“1”，而dollarOff返回“0”，不管每个项目都有它自己的相关折扣纪录。对于吐痰，我将ItemDiscounts.percentOff > 0更改为ItemDiscounts.percentOff > 1，然后将dollarAmount更改为全部2，percentOff全部为0。

我对此有些困惑，所以任何帮助将不胜感激。

是否存在GROUP BY '或任何聚合函数（SUM，COUNT ..）在这里没有显示？ – Vatev 2012-07-24 18:37:37

有一个GROUP BY，是的，我嘲笑查询使它更短并且不暴露实际的表/列名，将编辑显示组 – 2012-07-24 18:39:51

试试选择GROUP_CONCAT（ItemDiscounts.dollarOff）而不是ItemDiscounts.dollarOff来查看GROUP BY是否隐藏其中任何一个 – Vatev 2012-07-24 18:46:47

答

您在ON子句中具有对ItemID的非限定引用......不清楚为什么这不会引发“模糊列”异常。（显然，这不是暧昧到MySQL，而MySQL是确定哪些被引用项目Id，赔率是好的，它不是一个你意。

此外，查询包括对ItemStuff行来源的引用，但没有

我还怀疑GROUP BY的行为给你的结果集不符合你的期望（很可能，现在，它掩盖了你的真实问题查询，这可能是您没有打算的CROSS JOIN操作。

我建议您在没有的情况下尝试查询子句，并确认结果集是您期望缺少GROUP BY子句的内容。

注意：大多数其他关系数据库引擎将抛出异常，并显示GROUP BY，就像您在查询中显示的那样。他们（基本上）要求SELECT列表中的每个非聚集都包含在GROUP BY中。你可以让MySQL以相同的方式行事（使用sql_mode的一些特定设置）。MySQL更加自由，但是你找回的结果可能不符合你的期望。

注意：我没有看到这个查询是如何传递语义检查的，并且根据给定的不存在的ItemStuff行源的引用返回任何结果集。

为了提高可读性，我建议您不要使用逗号作为连接运算符，而是使用JOIN关键字。我还建议您将WHERE子句中的连接谓词移至ON子句。我还希望给每个行源一个别名，并使用该别名来限定它的列。

给你展示在您的查询，我会写（部分我可以做的意义上）这样的内容：

SELECT i.ItemID 
    , i.name 
    , i.price 
    , d.percentOff 
    , d.dollarOff 
    , a.ColorName 
    FROM Items i 
    JOIN ItemAttributes a 
    ON a.ItemID = i.ItemID 
    LEFT 
    JOIN ItemDiscounts d 
    ON d.ItemID = i.ItemID 
     AND (d.percentOff > 0 OR d.dollarOff > 0)

我省略选择列表ItemStuff.StuffID，因为我不请参阅任何ItemStuff行源。

我也排除WHERE子句，因为在查询中没有看到任何ItemStuff rowsource。

-- WHERE ItemStuff.ItemID = i.ItemID

我省略了GROUP BY，因为，再说，我没有看到你所查询的任何ItemStuff行来源，而且由于GROUP BY的行为很可能不是我所期望的，而是被掩盖的问题我的查询。

-- GROUP BY ItemStuff.StuffID

UPDATE：

@Kyle，您的查询“超时”这一事实让我相信你生成WAY更多的行比预期的，就像你有一个笛卡尔乘积（表中的每一行都与另一个表中的每一行“匹配”......一个表中的每行10,000行，另一个表中的10,000行将生成100,000,000行

我认为GROUP BY子句是屏蔽真正的问题EM。

我建议在开发时将每个表的PRIMARY KEY作为结果集中的主要列。我会在驱动表中添加一些合理的谓词（例如，i.ItemID IN (2,3,5,7)限制结果集的大小，以及ORDER BY主键...应该有助于识别意外的笛卡尔积。

缺少ItemStuff是打字错误。简单地删除GROUP BY使查询超时，我将考虑将WHERE子句移入连接 – 2012-07-24 19:08:17

@凯尔，事实上你的查询“超时”使我相信你正在产生比你期望的更多的行，就像你有一个笛卡尔积（表中的每一行都被“匹配”到其他表中的每一行...一个表中有10,000行，另一个表中有10,000行，这将产生100,000,000行。 'GROUP BY'掩盖了你的问题。 – spencer7593 2012-07-24 19:11:51

是的。我更深入地研究了它，并且GROUP BY被另一个开发者放在那里，以掩盖这样一个事实，即所谓的“一对一”关系实际上是“一对多”的关系。实际上，我们10年前的数据库已经远远没有正常化，我可能不得不采取不同的路线。我会接受你的回答，因为对于有类似问题的人来说，这似乎最有帮助。感谢您的帮助。 – 2012-07-24 19:19:42

答

当你从查询中删除这些行时，你会得到你想要的吗？

AND（ ItemDiscounts.percentOff> 0 OR ItemDiscounts.dollarOff> 0 ）

相同的结果... 1对于'percentOff'，0对于'dollarOff' – 2012-07-24 18:38:09

答

一旦您指定外的可能空方的绝对值加入，您WHERE条款必须考虑到它。

与下列条款试试：

AND (
ItemDiscounts.percentOff > 0 
OR ItemDiscounts.percentOff is null 
OR ItemDiscounts.dollarOff > 0 
OR ItemDiscounts.dollarOff is null 
)

Additionaly，你指定一个GROUP BY没有汇总。这在大多数情况下是没有意义的。你可能想要ORDER BY排序。

左外部加入丢失的记录

相关推荐