SQL左外部联接的意外输出 [英] SQL left outer join's unexpected output

问题描述

我有这些桌子， rolls和rollsout.我想执行左外部联接.

I have these tables, rolls and rollsout. I would like to perform a left outer join .

          |type|height|weight|Rate|
          -------------------------
          |RP  |2ft   | 200  | 100|
          |RP  |2ft   | 200  | 100|
          |RP  |2ft   | 200  | 100|
          |LD  |2ft   | 100  | 130|

推出

          |type|height|weight|Rate|
          -------------------------
          |RP  |2ft   | 200  | 100|
          |RP  |2ft   | 200  | 100|

求和，联接和分组后的预期输出==>

Expected output after SUMing, JOINing and GROUPings ==>

          |type|height|SUM(rolls.weight)|SUM(rollsout.weight)|
          ----------------------------------------------------
          |RP  |2ft   | 600             | 400                |
          |LD  |2ft   | 100             | NILL               |

---

我的代码:

---

My code:

 SELECT rolls.hight,rolls.type,SUM(rolls.weight),SUM(rollsout.weight)
 FROM rolls 
 LEFT OUTER JOIN rollsout 
 ON rolls.hight = rollsout.hight AND rolls.type= rollsout.type 
 GROUP BY rolls.hight,rolls.type

但是上面代码的O/P是

But the O/P for the above code is

          |type|height|SUM(rolls.weight)|SUM(rollsout.weight)|
          ----------------------------------------------------
          |RP  |2ft   | 1200            | 1200               |
          |LD  |2ft   | 100             | NILL               |

我不知道我要去哪里错了-您能解释一下吗?

I don't know where I am going wrong — can you explain?

推荐答案

您没有做错任何事情.这就是JOIN的行为

You are not doing anything wrong. That's the behaviour of JOINs

它是左边的行数X右边的行数，在您的情况下为3 x 2 =6.6 x 200 = 1200

It is number of rows on the left X number of rows on the right and in your case, it 3 x 2 = 6. And 6 x 200 = 1200

尝试

Select rolls.height,rolls.type, SUM(rolls.weight) as W, rollsout.Ww
 FROM rolls 
 LEFT JOIN
       (Select height,type, SUM(weight) as Ww 
         From rollsout GROUP BY height, type
       ) as rollsout
ON rolls.height = rollsout.height AND 
rolls.type= rollsout.type 
GROUP BY rolls.height,rolls.type

SQLFiddle

我知道这不适用于SQL Server，但它适用于MySQL

SQLFiddle

I know this won't work in SQL Server, but it worked for MySQL

这篇关于SQL左外部联接的意外输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！