mysql - 错误 : Unknown Column in 'field list'

Question

当列 ls.amount 在字段列表中时，我收到一个错误我运行以下查询。

任何人都可以帮助我诊断问题。

SELECT c.name, ic.keyword, COUNT(ic.keyword), SUM(ls.amount), ls.buyer FROM in_clicks AS ic
       INNER JOIN ads AS a ON ic.ad_id = a.id
       INNER JOIN ad_groups AS ag ON a.ad_group_id = ag.id
       INNER JOIN campaigns AS c ON ag.campaign_id = c.id;
       INNER JOIN leads AS l ON (ic.id = l.in_click_id)
       INNER JOIN lead_status AS ls ON (l.id = ls.lead_id)
WHERE ic.create_date LIKE '%2011-08-19%' AND ic.location NOT LIKE '%Littleton%' AND discriminator LIKE '%AUTO_POST%'
GROUP BY ic.keyword ORDER BY COUNT(ic.keyword) DESC

准确的错误信息是:

Error Code: 1054
Unknown column 'ls.amount' in 'field list'

Answer 1

在第 4 行删除分号 (;)。我怀疑这会在您定义 ls 别名之前结束您的查询。

SELECT c.name,
       ic.keyword,
       COUNT(ic.keyword),
       SUM(ls.amount),
       ls.buyer
FROM   in_clicks AS ic
       INNER JOIN ads AS a
         ON ic.ad_id = a.id
       INNER JOIN ad_groups AS ag
         ON a.ad_group_id = ag.id
       INNER JOIN campaigns AS c
         ON ag.campaign_id = c.id
       INNER JOIN leads AS l
         ON ( ic.id = l.in_click_id )
       INNER JOIN lead_status AS ls
         ON ( l.id = ls.lead_id )
WHERE  ic.create_date LIKE '%2011-08-19%'
       AND ic.location NOT LIKE '%Littleton%'
       AND discriminator LIKE '%AUTO_POST%'
GROUP  BY ic.keyword
ORDER  BY COUNT(ic.keyword) DESC