我在adser中有三个表，advertiser_clicks和advertiser_cost，我在其中使用了具有多个聚合函数的联接。但是我无法从表中获取实际记录。请在下面的表结构中查找。
表1：广告商
................
id name
................
1 Your Survey
2 Vindale
表2：advertiser_clicks
在此表中adv_id是广告客户表ID的外键
.............................................................
id adv_id survey_id survey_name clicks dt_datetime
.............................................................
1 1 101 survey1 1 2017-11-30
2 2 102 survey2 1 2017-11-29
3 1 103 survey3 1 2017-11-28
表3：advertiser_cost
在此表中，a_click_id是Advertiser_clicks表adv_id的外键
.......................................................
ac_id a_click_id cpi conversion cost profit
......................................................
1 1 10 1 8 2
2 2 10 1 5 5
3 3 5 1 5 5
我要低于输出：-当我选择开始日期：2017-11-29和结束日期：2017-11-30时，条件是我要低于输出。这里的总点击次数，CPI，成本和利润是所有数据的总和介于该日期范围之间。
.............................................................
adv_id advertiser totalclick cpi cost profit
.............................................................
1 Your survey 2 20 13 8
2 vindale 0 0 0 0
我写了下面的查询，它返回的确切数据
在该日期范围内，但我不会返回vindale的记录
广告客户为零值。请在下面查询我。
SELECT sum(advertiser_clicks.`clicks`) as totalclick,sum(advertiser_cost.cpi) as revenue,sum(advertiser_cost.`conversion`) as conversion,sum(advertiser_cost.`cost`) as cost,SUM(advertiser_cost.profit) as profit,advertiser.name as advertiser,advertiser.id as adv_id FROM `advertiser_clicks` LEFT JOIN advertiser_cost on advertiser_cost.a_click_id=advertiser_clicks.`id` inner join advertiser on advertiser.id=advertiser_clicks.`adv_id` where advertiser_clicks.`dt_datetime`>='2017-11-29' and advertiser_clicks.`dt_datetime`<='2017-11-30' group by advertiser.name,advertiser.id
我的查询返回此输出：
.............................................................
adv_id advertiser totalclick cpi cost profit
.............................................................
1 Your survey 2 20 13 8
最佳答案
嗨，我已经解决了我的问题：
SELECT sum(advertiser_clicks.`clicks`) as totalclick,sum(advertiser_cost.cpi) as revenue,sum(advertiser_cost.`conversion`) as conversion,sum(advertiser_cost.`cost`) as cost,SUM(advertiser_cost.profit) as profit,advertiser.name as advertiser,advertiser.id as adv_id FROM `advertiser` left join advertiser_clicks on advertiser.id=advertiser_clicks.`adv_id` and (advertiser_clicks.`dt_datetime` between '$startdate' and '$enddate' ) LEFT JOIN advertiser_cost on advertiser_cost.a_click_id=advertiser_clicks.`id` group by advertiser.name,advertiser.id