MySQL左连接查询语法错误

Question

我不断收到此查询的语法错误。我正在尝试计算行数:

1) 列 yield _campaign_free_id = 43

2) 列 yield _campaign_paid_id = 43

因此有两个输出:paid_views、free_views

SELECT 

cp.campaign_paid AS paid_views
cf.campaign_free AS free_views

FROM

(
    SELECT COUNT(earning_paid_campaign_id) AS campaign_paid
    FROM earnings
    WHERE earning_paid_campaign_id = 43
) cp

LEFT JOIN

(
    SELECT COUNT(earning_free_campaign_id) AS campaign_free
    FROM earnings
    WHERE earning_free_campaign_id = 43
) cf

ON cf.earning_campaign_free_id = cp.earning_paid_campaign_id

这是错误:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use

near 'cf.campaign_free AS free_views

FROM

( SELECT COUNT(earning_paid_campaign_i' at line 4

如何修复此错误？

Answer 1

试试这个。

SELECT 

cp.campaign_paid AS paid_views,
cf.campaign_free AS free_views

FROM

(
    SELECT earning_paid_campaign_id,COUNT(earning_paid_campaign_id) AS campaign_paid
    FROM earnings
    WHERE earning_paid_campaign_id = 43
) cp

LEFT JOIN

(
    SELECT earning_campaign_free_id,COUNT(earning_free_campaign_id) AS campaign_free
    FROM earnings
    WHERE earning_free_campaign_id = 43
) cf

ON cf.earning_campaign_free_id = cp.earning_paid_campaign_id

您正在尝试加入ON cf.earning_campaign_free_id = cp.earning_paid_campaign_id的基础，但是当您编写子查询时，mysql创建temporary 表，并且您没有选择该 id。所以出现错误