0 レビュー
1 回答
php-複数の左結合がサブクエリで機能しない
これに似た質問がすでに投稿されていることは知っていますが、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クエリの最後の2行目ON sixth.campaign_id = campaigns.id
わからない
0
レビュー
答え :
解決策：
このような複雑なクエリを実行する理由はありません。条件付き集計を使用するだけです：
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()を追加できます。
わからない
同様の質問
私たちのウェブサイトで同様の質問で答えを見つけてください。