mysql - 加入创建错误的结果查询

Question

当我在查询中进行求和和左联接时，结果不再正确。这是因为在一张表中我join的id比较常见，如何防止求和出错？

我有一个名为 Norm 的表

ID: 1  LocationID: 1   Norm: 0,22

ID: 1  LocationID: 1   Norm: 0,25

我有一个名为 Hour 的表

ID: 1   LocationID: 1

ID: 2   LocationID: 1

ID: 3   LocationID: 1

查询:

SELECT N.LocationID, SUM(N.Norm) FROM Norm N

结果:

LocationID: 1   Sum(N.Norm): 0,47

查询:

SELECT N.LocationID, SUM(N.Norm) FROM Norm N LEFT JOIN Hour H ON 
N.LocationID = H.LocationID

结果:

LocationID: 1 Sum(N.Norm): 1,41

脚本:

CREATE TABLE Norm` ( `ID` INT NOT NULL , `LocationID` INTNOT NULL , 
`Norm` DECIMAL(10,2) NOTNULL );

INSERT INTO `Norm`(`ID`, `LocationID`, `Norm`) 
VALUES (1,1, 0.22);

INSERT INTO `Norm`(`ID`, `LocationID`, `Norm`) 
VALUES (2,1, 0.25)


CREATE TABLE `Hour` ( `ID` INT NOT NULL , `LocationID` INTNOT NULL );

INSERT INTO `Hour`(`ID`, `LocationID`) VALUES (1, 1);
INSERT INTO `Hour`(`ID`, `LocationID`) VALUES (2, 1);
INSERT INTO `Hour`(`ID`, `LocationID`) VALUES (3, 1);

我需要连接，但希望总和与第一个一样正确，我该怎么做？

Answer 1

你可以试试这个

   SELECT N.LocationID
        , SUM(N.Norm) 
     FROM Norm N 
LEFT JOIN (SELECT distinct locationid FROM Hour ) AS H 
       ON N.LocationID = H.LocationID
 GROUP BY N.LocationID