mysql - 使用 GROUP BY 计算没有重复行的 SUM

Question

SELECT SUM(man+woman) AS over65, 
       a.cod, 
       a.city, 
       b.cod2 
  FROM a LEFT JOIN 
         b ON b.cod2 = a.cod 
 GROUP BY cod

--

table  a
cod    city 
28001  rome 
28001  rome 
28002  milan 
28002  milan 

table b
cod2  age man woman 
28001  65 156   220 
28001  66 250   280 
28001  67 350   483 
28002  65 556   524 
28002  66 650   683 
28002  67 450   342 

result Is:
cod    city  over65 
28001  rome    3478 
28002  milan   6410 

instead Of :   
cod     city   over65 
28001   rome     1739 
28802   milan    3205

我应该为此做什么？谢谢

Answer 1

使用子查询去除表a中的重复项。

SELECT SUM(man+woman) AS over65, 
       a.cod, 
       a.city, 
       b.cod2 
  FROM (SELECT DISTINCT cod, city
        FROM a) AS a 
  LEFT JOIN 
         b ON b.cod2 = a.cod 
 GROUP BY a.cod

我还想知道为什么表 a 首先有这些重复项。如果给定 cod 的 city 始终相同，则数据未正确标准化。