SQL左外部联接的意外输出

一尘不染

SQL左外部联接的意外输出

sql

我有这些表， rolls和rollsout。我想执行左外部联接。

劳斯莱斯

          |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|

SUMing，JOINing和GROUPings之后的预期输出==>

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

我的代码：

 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是

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

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

阅读 206

2021-03-08

共1个答案

一尘不染

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

它是左边的行数X右边的行数，在您的情况下为3 x 2 = 6。 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

2021-03-08