我有两个表,其中包含有关两个小部件生产的信息。 Table1
看起来像这样:
Table2看起来像这样:
我想计算每个小部件的平均产量,并按国家/地区代码(ADM0_A3)显示,以便结果看起来像这样(在此阶段不必担心丢失数据,例如BWA没有小部件1的产量)
ADM0_A3 w1avg w2avg
DZA 50000 3450000
AGO 86000 40000
BWA blank 0
CMR 3500 blank
我正在使用的MS ACCESS SQL查询在这里:
SELECT Z.ccode, Z.ave_w1, A.ave_w2
FROM
(
SELECT X.ADM0_A3 as ccode, 0.02 * X.sum_w1 / X.n_w1 AS ave_w1
FROM
(
SELECT t1.ADM0_A3, SUM(t1.production) AS sum_w1, COUNT(t1.production) as n_w1
FROM Table1 t1
GROUP BY t1.ADM0_A3
) X
) Z
JOIN
(
SELECT Y.ADM0_A3, 0.025 * Y.sum_w2 / Y.n_w2 AS ave_w2
FROM
(
SELECT t2.ADM0_A3, SUM(t2.production) AS sum_w2, COUNT(t2.production) as n_w2
FROM Table2 t2
GROUP BY t2.ADM0_A3
) Y
) A
ON A.ADM0_A3 = Z.ccode
我检查了子查询,它们工作正常。但是,当我尝试加入查询时,收到此错误消息"Syntax error in FROM clause"
。我认为该解决方案相当简单,但我看不到它,因此不胜感激任何建议。提前致谢!
您可以尝试这样做:
SELECT adm0_a3, MAX(w1avg) as w1avg, MAX(w2avg) as w2avg
FROM (SELECT t1.ADM0_A3, AVG(t1.production) * 0.02 as w1avg, NULL as w2avg
FROM Table1 as t1
GROUP BY t1.ADM0_A3
UNION ALL
SELECT t2.ADM0_A3, NULL, AVG(t2.production) * 0.02 as w1avg
FROM Table1 as t2
GROUP BY t2.ADM0_A3
) as t
GROUP BY adm0_a3;
我不确定UNION ALL
该FROM
条款是否支持所有版本的MS Access 。如果没有,您可以使用视图解决该问题。
谢谢戈登-那行得通!我需要将第一行更改为SELECT adm0_a3,将MAX(w1avg)设置为w1avgx,将MAX(w2avg)设置为w2avgx,以避免循环引用出现问题。使用MAX消除空值/零值也很简单!