mysql - sql连接两个表的数据

Question

我想知道是否有人帮我连接两个表中的数据...花了一整天的时间没有成功...

代码 1 选择:年份|营业额1 |数量1 |欧洲每订单1

SELECT Year(table1.ContractDate) AS Year,
  Sum(table1.TPrice) AS Turnover1,
  Count(table1.id) AS Quantity1,
  ROUND(Sum(table1.TPrice) / Count(table1.id), 0) AS EurPerOrder1
FROM table1
GROUP BY Year(table1.ContractDate) * 100
ORDER BY table1.ContractDate DESC         

代码2选择:年份|营业额2 |定量2 |欧洲每订单2

SELECT Year(table2.date) AS Year,
  Sum(table2.price) AS Turnover2,
  Count(table2.rid) AS Quantiry2,
  ROUND(Sum(table2.price) / Count(table2.rid), 0) AS EurPerOrder2
FROM table2
GROUP BY Year(table2.date) * 100
ORDER BY table2.date DESC

我需要加入如下数据:

年份 |营业额1 |数量1 |欧洲每订单1 |营业额2 |定量2 |欧洲每订单2

我需要将两个表中的所有数据按年份分组。即使 table2 也没有 2013 年，无论如何我希望它显示 0 或空...我使用示例尝试了不同的方法，但没有任何效果，所以我认为可能会出现问题，因为第二个表没有表 1 上的所有年份...

Answer 1

首先:您可以阅读有关 JOINS here 的很好的解释。

好的，根据你需要的问题LEFT JOIN。这意味着 table1 中的所有数据，仅来自 table2 的匹配数据。

SELECT 必须如下所示:

SELECT Year(table1.ContractDate) AS Year,
  Sum(table1.TPrice) AS Turnover1,
  Count(table1.id) AS Quantiry1,
  ROUND(Sum(table1.TPrice) / Count(table1.id), 0) AS EurPerOrder1, 
    Sum(table2.price) AS Turnover2,
    Count(table2.rid) AS Quantiry2, 
  ROUND(Sum(table2.price) / Count(table2.rid), 0) AS EurPerOrder2 
FROM 
    table1 t1
    LEFT JOIN table2 t2 ON Year(table1.ContractDate) = Year(table2.date)
GROUP BY 
    Year(table1.ContractDate) * 100, Year(table2.date) * 100
ORDER BY 
    table1.ContractDate DESC, table2.date DESC

当然，您需要处理NULL值。请参阅link

请检查SQL，如有错误请更正。我没有实时数据可供检查(通过运行)。