mysql查询只返回一行，因为其中包含sum函数

8xiog9wr 于 2021-06-18 发布在 Mysql

关注(0)|答案(1)|浏览(398)

我正在尝试创建一个广告查询，在这里我要获取每个广告的所有印象的数据。一个用户可以有多个广告和印象将被计算在一个表的基础上每天。所以每一天我都会有一排不同的队伍。下面是我的查询当前的样子。

SELECT  
   eac.id,
   eac.gender,
   eac.start_date,
   eac.end_date,
   eac.ad_image_path,
   eac.ad_link,
   eac.requestfrom,
   eac.traffic,
   eac.registertype,
   eacr.region_id,
   eac.active,
   eac.impression,
   eac.center_image_path,
   eac.bottom_image_path,
   eac.approved_by,
   er.name as country_name,
   eac.budget,
   sum(budget/ (DATEDIFF(end_date,start_date)) *1000) as daily_imp,  
   eaa.impression_count,
   eac.customer_id,
   eaa.created_at
FROM 
   `enrich_advert_customer` eac         
   JOIN `enrich_advert_customer_regions` eacr ON eac.id = eacr.advert_customer_id  
   JOIN `enrich_regions` er ON er.id = eacr.region_id
   LEFT JOIN `enrich_advert_abstract` eaa on eac.id = eaa.advert_customer_id        
WHERE 
   eac.requestfrom ='web' AND
   eac.registertype = 'paid' AND
   eac.active = 1 AND
   eac.approved_by = 1 AND
   eac.gender ='male' AND
   er.name = 'india' AND
   eac.start_date <= '2018-11-5' AND
   eac.end_date >= '2018-11-10'

但问题是如果我用的是

sum(budget/ (DATEDIFF(end_date,start_date)) *1000) as daily_imp

然后一次只返回一行。
如果你能指出我犯了什么错误，那会很有帮助的。
谢谢您！

mysql

来源：https://stackoverflow.com/questions/53167748/mysql-query-returning-only-one-row-because-of-sum-function-in-it

1条答案

按热度按时间

68bkxrlz1#

你需要加上 group by 子句和其他列

SELECT  eac.id,eac.gender,eac.start_date,eac.end_date,eac.ad_image_path,eac.ad_link,eac.requestfrom,eac.traffic,eac.registertype,eacr.region_id,eac.active,eac.impression,eac.center_image_path,eac.bottom_image_path,eac.approved_by,er.name as country_name,eac.budget,sum(budget/ (DATEDIFF(end_date,start_date)) *1000) as daily_imp  ,eaa.impression_count,eac.customer_id,eaa.created_at

FROM 
    `enrich_advert_customer` eac 

JOIN 
    `enrich_advert_customer_regions` eacr
   ON eac.id = eacr.advert_customer_id 

JOIN 
    `enrich_regions` er
   ON er.id = eacr.region_id

LEFT JOIN 
    `enrich_advert_abstract` eaa
    on eac.id = eaa.advert_customer_id

WHERE eac.requestfrom ='web' AND
      eac.registertype = 'paid' AND
      eac.active = 1 AND
      eac.approved_by = 1 AND
      eac.gender ='male' AND
      er.name = 'india' AND
      eac.start_date <= '2018-11-5' AND
      eac.end_date >= '2018-11-10'
group by eac.id,eac.gender,eac.start_date,eac.end_date,eac.ad_image_path,eac.ad_link,eac.requestfrom,eac.traffic,eac.registertype,eacr.region_id,eac.active,eac.impression,eac.center_image_path,eac.bottom_image_path,eac.approved_by,er.name,eac.budget,  eaa.impression_count,eac.customer_id,eaa.created_at

赞(0）回复(0）举报 2021-06-18

我来回答

mysql查询只返回一行，因为其中包含sum函数

1条答案

相关问题

热门标签

最新问答