on子句中的未知列，不能在Workbench中复制

Question

下面的查询生成错误

'on子句‘中未知列'a.household_id’

在phpmyadmin中，但在MySQL工作台中没有。phpmyadmin (3.5.3)正在查看运行MySQL服务器5.5.27的远程服务器，Workbench 6.1.4正在查看运行5.5.37的本地主机。我在Workbench中复制了MySQL文档中描述的未知列错误，因此文档没有提供消除此错误的直接方向。

SET @end_year = (select if(month(now()) < 7, year(now())  -1, year(now())));
SET @start_year = @end_year - 4;

SELECT if(MONTH(contact_date) >= 7, CONCAT('FY ', YEAR(contact_date) + 1), CONCAT('FY ', YEAR(contact_date))) AS FY, 
    FORMAT(SUM(A.size), 0) AS DI, FORMAT(COUNT(c.household_id), 0) AS DH
FROM contact c
JOIN 
    (
    SELECT m.household_id, if(COUNT(dob) = 0, 1, COUNT(dob)) size
    FROM member m
    GROUP BY m.household_id
    ) A ON a.household_id = c.household_id
WHERE contact_date BETWEEN CONCAT(@start_YEAR - 1, '-07-01') AND CONCAT(@end_YEAR, '-06-30')
GROUP BY if(MONTH(contact_date) >= 7, YEAR(contact_date) + 1, YEAR(contact_date));

Answer 1

您的表别名是A，而不是a，我想这在您的情况下很重要。尝试以下几点：

A ON A.household_id = c.household_id