php - 多个左连接不适用于子查询

Question

我知道已经发布了一个与此类似的问题，但我无法通过尝试在 google 上找到的东西找到我的 sql 查询的问题

我收到的错误是“您的 SQL 语法有误”

这是我的代码

select 
    campaigns.campaign_name,
    calls.count,
    first.count as first,
    second.count as second,
    third.count as third,
    fourth.count as fourth,
    fifth.count as fifth,
    sixth.count as sixth
    from campaigns
    LEFT JOIN 
    (
        select count(*) as count, campaign_id FROM calls where direction = 'Incoming' group by campaign_id
    ) as calls
    ON campaigns.id = calls.campaign_id
    LEFT JOIN 
    (
        select count(*) as count, 
        campaign_id FROM calls 
        where direction = 'Incoming' 
        AND DATE(created_at) BETWEEN '2014-01-01' AND '2016-01-01'
        group by campaign_id
    ) as first
    LEFT JOIN 
    (
        select count(*) as count, 
        campaign_id FROM calls 
        where direction = 'Incoming' 
        AND DATE(created_at) BETWEEN '2014-01-01' AND '2016-01-01'
        group by campaign_id
    ) as second
    ON campaigns.id = second.campaign_id
    LEFT JOIN 
    (
        select count(*) as count, 
        campaign_id FROM calls 
        where direction = 'Incoming' 
        AND DATE(created_at) BETWEEN '2014-01-01' AND '2016-01-01'
        group by campaign_id
    ) as third
    ON campaigns.id = third.campaign_id
    LEFT JOIN 
    (
        select count(*) as count, 
        campaign_id FROM calls 
        where direction = 'Incoming' 
        AND DATE(created_at) BETWEEN '2014-01-01' AND '2016-01-01'
        group by campaign_id
    ) as fourth
    ON campaigns.id = fourth.campaign_id
    LEFT JOIN 
    (
        select count(*) as count, 
        campaign_id FROM calls 
        where direction = 'Incoming' 
        AND DATE(created_at) BETWEEN '2014-01-01' AND '2016-01-01'
        group by campaign_id
    ) as fifth
    ON campaigns.id = fifth.campaign_id
    LEFT JOIN 
    (
        select count(*) as count, 
        campaign_id FROM calls 
        where direction = 'Incoming' 
        AND DATE(created_at) BETWEEN '2014-01-01' AND '2016-01-01'
        group by campaign_id
    ) as sixth
    ON sixth.campaign_id = campaigns.id
    group by campaigns.id

错误似乎在第69行sql 查询的最后第二行 ON sixth.campaign_id = campaigns.id

Answer 1

没有理由进行如此复杂的查询。只需使用条件聚合:

select c.campaign_name,
       sum(direction = 'Incoming') as cnt,
       sum(direction = 'Incoming' and DATE(created_at) BETWEEN '2014-01-01' AND '2016-01-01') as first,
       . . .
from campaigns c left join
     calls
     on c.campaign_id = calls.campaign_id
group by c.campaign_name;

您的所有条件似乎都相同，但您可以为其他条件添加额外的 sum()。