mysql - 组合两个不同的数据集时子查询不返回数据

Question

我正在尝试将两个不同的数据集与 Google BigQuery 上的子查询结合起来。

当我单独使用子查询时，它会运行并返回每年的总平均温度。但是，当我将查询合并为子查询时，它只返回所有规定期间的平均温度，而不是每年的平均温度。

我的代码:

#standardSQL
SELECT t1.year, gas_fuel, liquid_fuel, solid_fuel, cement, annual_anomaly, (SELECT avg(temp)
FROM `bigquery-public-data.noaa_gsod.gsod*`
WHERE year > '1982') as average_temperature
FROM `cs779-term-project.CO2_FossilFuel.GlobalEmissions` as t1
JOIN `cs779-term-project.CO2_FossilFuel.SeaLevels` as t2
on t1.year = t2.year
Where t1.year > 1982
ORDER BY t1.year ASC;

此外，当我在子查询中添加 GROUP BY year 时，它会显示以下错误:

Error: Scalar subquery produced more than one element

谢谢!

Answer 1

如果你需要年的平均温度，那么你需要在子查询条件中使用查询中的年份:

SELECT t1.year, gas_fuel, liquid_fuel, solid_fuel, cement, annual_anomaly
, (SELECT avg(temp)
  FROM `bigquery-public-data.noaa_gsod.gsod*`
  WHERE year = t1.year) as average_temperature
FROM `cs779-term-project.CO2_FossilFuel.GlobalEmissions` as t1
JOIN `cs779-term-project.CO2_FossilFuel.SeaLevels` as t2
on t1.year = t2.year
Where t1.year > 1982
ORDER BY t1.year ASC;