MySQL 多个 LEFT OUTER JOIN 错误

发布于 2024-11-04 08:48:36 字数 989 浏览 0 评论 0原文

我希望有人能帮助我解决我的 MySQL 问题。我有一个错误，如果捐款表上有一个左外连接，则金额结果为 100 美元（这是正确的）。如果我包含另一个表的第二个左外连接（ikes）。如果我有 2 个 ike，金额就会增加一倍（200 美元），如果我有 3 个 ike，金额就会增加三倍（300 美元）。对于我的一生，我无法弄清楚这一点。 ikes与捐款金额有什么关系？我已将查询分开，它们可以自行工作。但它们共同导致了问题。

任何人都可以看到问题吗？我已包含查询和下表。

         SELECT COUNT(i.type) AS xlike, 
                SUM(c.amount) AS amount, 
                w.* 
           FROM wish w 
LEFT OUTER JOIN contributions c ON w.ID=c.receiveid
LEFT OUTER JOIN ikes i ON w.ID=i.wishid 
          WHERE w.ID = 236

表格：

CREATE TABLE IF NOT EXISTS `contributions` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `amount` decimal(19,2) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

CREATE TABLE IF NOT EXISTS `ikes` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `type` enum('likes','dislikes') NOT NULL,
  `wishid` int(11) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

原文

I hope someone can help me with my MySQL problem. I have a bug where if there is one left outer join on contribution table, result of amount is $100 (which is correct). If I include a second left outer join of another table (ikes). And I have 2 ikes, it doubles amount ($200), if I have 3 ikes, it triples ($300). For the life of me, I cannot figure this out. What do the ikes have any to do with the contribution amount? I've separated the queries and they work by themselves. But together they cause the problem.

Can anyone see the problem? I've included the query and the tables below.

         SELECT COUNT(i.type) AS xlike, 
                SUM(c.amount) AS amount, 
                w.* 
           FROM wish w 
LEFT OUTER JOIN contributions c ON w.ID=c.receiveid
LEFT OUTER JOIN ikes i ON w.ID=i.wishid 
          WHERE w.ID = 236

Tables:

CREATE TABLE IF NOT EXISTS `contributions` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `amount` decimal(19,2) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

CREATE TABLE IF NOT EXISTS `ikes` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `type` enum('likes','dislikes') NOT NULL,
  `wishid` int(11) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

白芷 2024-11-11 08:48:36

虽然大多数人会告诉您使用 JOIN，但您必须注意，如果多个子记录与其关联，则连接将重复父记录。这就是聚合函数中的值膨胀的原因。

我将您的查询重写为：

   SELECT w.*,
          COALESCE(x.amount, 0) AS amount,
          COALESCE(y.type, 0) AS type
     FROM WISH w 
LEFT JOIN (SELECT c.receiveid,
                  SUM(c.amount) AS amount
             FROM CONTRIBUTIONS c
         GROUP BY c.receiveid) x ON x.receiveid = w.ID
LEFT JOIN (SELECT i.wishid,
                  COUNT(i.type) AS type
             FROM IKES i
         GROUP BY i.wishid) y ON y.wishid = w.ID
    WHERE w.ID = 236

While most will tell you to use JOINs, you have to be aware that joins will duplicate parent records if more than one child record is associated to it. This is what can inflate values from aggregate functions.

I re-wrote your query as:

   SELECT w.*,
          COALESCE(x.amount, 0) AS amount,
          COALESCE(y.type, 0) AS type
     FROM WISH w 
LEFT JOIN (SELECT c.receiveid,
                  SUM(c.amount) AS amount
             FROM CONTRIBUTIONS c
         GROUP BY c.receiveid) x ON x.receiveid = w.ID
LEFT JOIN (SELECT i.wishid,
                  COUNT(i.type) AS type
             FROM IKES i
         GROUP BY i.wishid) y ON y.wishid = w.ID
    WHERE w.ID = 236

回复收藏 0 原文

~没有更多了~