mysql - 根据引用表对数据进行分组

Question

省表的数据:

idProv  Province
1       JT
2       JB
3       BT

城市表的数据:

idCity  IdProv  City
  1     1       BL  
  2     1       ML  
  3     1       MD  
  4     2       CB  
  5     2       TM  
  6     3       YK  
  7     3       SM  

数据表的数据:

idData  idCity  population  data1   data2
1       1       100         200     200
2       2       100         200     300
3       3       50          100     150
4       4       20          10      20
5       5       20          20      40
6       6       50          200     400
7       7       50          100     200

我想对每个省的数据表中的数据进行分组。

输出应该是:

Province    population  data1   data2
JT          250         500     750
JB          40          30      60
BT          100         300     600

SQL FIDDLE

Answer 1

使用INNER JOIN合并3个表，并获取每列的SUM，例如population、data1和data2。然后我应用了 GROUP BY 和 ORDER BY 来获得您想要的结果。请参阅下面我的查询和演示:

SELECT 
      Province, 
      SUM(population) AS Population, 
      SUM(data1) AS Data1, 
      SUM(data2) AS Data2
FROM data A 
INNER JOIN City B ON A.idCity=B.idCity
INNER JOIN Province C ON B.Idprov=C.Idprov
GROUP BY Province
ORDER BY Province DESC

<强> SQL FIDDLE DEMO HERE