MySQL应用LEFT JOIN，如果有任何来自CURRENT DATE的数据

Question

我正试图使用下面的查询来获取数据，但这里的问题是它没有显示来自advert_customer的数据，而advert_abstract中没有。在IF和LEFT JOIN条件下，我认为总体问题是

以下是表格结构：

advert_customer

+-----+-------------+--------------------+--------+--------------+--------+---------------------+---------------------+-----+--------+---------------------+
| id  | customer_id | title              | budget | registertype | active | start_date          | end_date            | web | gender | created_at          |
+-----+-------------+--------------------+--------+--------------+--------+---------------------+---------------------+-----+--------+---------------------+
| 341 |           3 | test               |  22.00 | paid         |      1 | 2018-11-15 00:00:00 | 2018-11-22 00:00:00 |   1 | all    | 2018-11-15 12:01:07 |
| 340 |           3 | demo testing       |  15.00 | paid         |      1 | 2018-11-15 00:00:00 | 2018-11-25 00:00:00 |   1 | all    | 2018-11-15 10:52:36 |
| 339 |           3 | test data          |   8.00 | paid         |      0 | 2018-11-01 00:00:00 | 2018-11-08 00:00:00 |   1 | all    | 2018-11-14 10:38:57 |
| 336 |           3 | fgdfg              |  18.00 | paid         |      0 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |   1 | all    | 2018-11-13 16:56:52 |
| 335 |           3 | Demo Testing       |   7.00 | paid         |      1 | 2018-11-12 00:00:00 | 2018-11-14 00:00:00 |   0 | all    | 2018-11-12 15:22:49 |
| 334 |           3 | Demo Advertisement |  16.00 | paid         |      1 | 2018-11-12 00:00:00 | 2018-11-17 00:00:00 |   0 | female | 2018-11-12 15:02:36 |
| 333 |           3 | today ads          |  11.00 | paid         |      1 | 2018-11-11 00:00:00 | 2018-11-15 00:00:00 |   1 | male   | 2018-11-05 18:13:45 |
| 331 |         237 | testin15           |  37.00 | paid         |      0 | 2018-10-28 00:00:00 | 2018-10-31 00:00:00 |   0 | male   | 2018-11-04 17:14:32 |
+-----+-------------+--------------------+--------+--------------+--------+---------------------+---------------------+-----+--------+---------------------+

另一张表advert_abstract:

+----+--------------------+-------------+---------+------------------------+-------------+--------------+---------------------------+---------------------+---------------------+
| id | advert_customer_id | customer_id | post_id | impression_count_login | click_count | amount_spent | impression_count_nonlogin | created_at          | updated_at          |
+----+--------------------+-------------+---------+------------------------+-------------+--------------+---------------------------+---------------------+---------------------+
| 12 |                 41 |           3 |    NULL |                   1020 |        NULL |         0.00 |                         1 | 2018-11-15 09:52:58 | 2018-11-15 12:20:04 |
| 11 |                 32 |           3 |    NULL |                   1013 |        NULL |         0.00 |                         1 | 2018-11-15 09:50:01 | 2018-11-15 12:20:04 |
| 10 |                 12 |           3 |    NULL |                     20 |        NULL |         0.00 |                        15 | 2018-11-14 12:49:47 | 2018-11-14 12:49:47 |
|  9 |                111 |           3 |    NULL |                     20 |        NULL |         0.00 |                      NULL | 2018-11-14 12:38:53 | 2018-11-14 12:38:53 |
|  8 |                111 |           3 |    NULL |                     10 |        NULL |         0.00 |                      NULL | 2018-11-14 12:38:36 | 2018-11-14 12:38:36 |
|  6 |                 41 |           3 |    NULL |                   1004 |        NULL |         0.00 |                         1 | 2018-11-14 10:05:40 | 2018-11-14 18:33:01 |
|  5 |                 32 |           3 |    NULL |                   1005 |        NULL |         0.00 |                         1 | 2018-11-14 10:05:26 | 2018-11-14 18:33:01 |
|  4 |                333 |           3 |    NULL |                     15 |        NULL |         0.00 |                         1 | 2018-11-14 10:05:26 | 2018-11-14 10:44:01 |
+----+--------------------+-------------+---------+------------------------+-------------+--------------+---------------------------+---------------------+---------------------+

问题是放置左联接。如果advert_abstract中的join中有任何行，则该表应返回数据，否则这些列的数据将为NULL。

select `eac`.`id`, `eac`.`gender`, `eac`.`start_date`, `eac`.`end_date`, `eac`.`ad_image_path`, `eac`.`ad_link`, `eac`.`requestfrom`, `eac`.`traffic`, `eac`.`position`, `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_login`,`eaa`.`impression_count_nonlogin`,sum(impression_count_login + impression_count_nonlogin) as impression_count , `eac`.`customer_id`, `eaa`.`created_at`,`eac`.`web` 
from `advert_customer` as `eac` 
inner join `advert_customer_regions` as `eacr` on `eac`.`id` = `eacr`.`advert_customer_id` 
inner join `regions` as `er` on `er`.`id` = `eacr`.`region_id`                                  
left join `advert_abstract` as `eaa` on `eac`.`id` =`eaa`.`advert_customer_id`             <<<<<<<-----------------------    I WANT THIS TO HAPPEN IF THERE IS ANY ROW IN THE TABLE FROM CURENT DATE
  where `er`.`name` = 'India' and 
        `eac`.`web` = 1 and
        `eac`.`registertype` = 'paid' and
        `eac`.`active` = 1 and 
        `eac`.`approved_by` = 1 and 
        `eac`.`gender` = 'all' and 
        DATE(eac.start_date) <= DATE(CURDATE()) and
        DATE(eac.end_date) >=   DATE(CURDATE()) and
IF((SELECT CASE WHEN DATE(eaa.created_at) = DATE(CURDATE()) THEN eaa.created_at ELSE NULL END), eaa.created_at , NULL)                  <<<<<<<-----------------------    ON THIS LINE I THINK THE ISSUE IS
group by `eac`.`id`, `eac`.`gender`, `eac`.`start_date`, `eac`.`end_date`, `eac`.`ad_image_path`, `eac`.`ad_link`, `eac`.`requestfrom`, `eac`.`traffic`, `eac`.`position`, `eac`.`registertype`, `eacr`.`region_id`, `eac`.`active`, `eac`.`impression`, `eac`.`center_image_path`, `eac`.`bottom_image_path`, `eac`.`approved_by`, `eac`.`budget`, `eac`.`customer_id`, `eac`.`web`
                                       HAVING impression_count < daily_imp
                                        ORDER BY RAND() LIMIT 25

我不确定如何编写IF AND LEFT JOIN条件，或者是否有其他JOIN方法可以尝试从中获取数据。这样，如果数据在advert_abstract中不可用，我就可以获得所有数据，然后我也会得到其余的细节。

任何建议都会有帮助，谢谢你(提前(！

Answer 1

你并没有真正说出你想要实现的目标。所以我只能猜测。。。但这里有一个建议：

select `eac`.`id`, `eac`.`gender`, `eac`.`start_date`, `eac`.`end_date`, 
`eac`.`ad_image_path`, `eac`.`ad_link`, `eac`.`requestfrom`, 
`eac`.`traffic`, `eac`.`position`, `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_login`,`eaa`.`impression_count_nonlogin`,
sum(impression_count_login + impression_count_nonlogin) as impression_count , 
`eac`.`customer_id`, 
CASE WHEN `eaa`.`created_at` = DATE(CURDATE())  THEN DATE(CURDATE())
     WHEN `eaa`.`created_at` IS NULL            THEN NULL
     ELSE NULL
END AS created_at,
`eac`.`web` 
from `advert_customer` as `eac` 
inner join `advert_customer_regions` as `eacr` on `eac`.`id` = 
`eacr`.`advert_customer_id` 
inner join `regions` as `er` on `er`.`id` = `eacr`.`region_id`                                  
left join `advert_abstract` as `eaa` on `eac`.`id` =`eaa`.`advert_customer_id`         
 where `er`.`name` = 'India' and 
    `eac`.`web` = 1 and
    `eac`.`registertype` = 'paid' and
    `eac`.`active` = 1 and 
    `eac`.`approved_by` = 1 and 
    `eac`.`gender` = 'all' and 
    DATE(eac.start_date) <= DATE(CURDATE()) and
    DATE(eac.end_date)   >=   DATE(CURDATE())            
group by `eac`.`id`, `eac`.`gender`, `eac`.`start_date`, `eac`.`end_date`, 
`eac`.`ad_image_path`, `eac`.`ad_link`, `eac`.`requestfrom`, `eac`.`traffic`, 
`eac`.`position`, `eac`.`registertype`, `eacr`.`region_id`, `eac`.`active`, 
`eac`.`impression`, `eac`.`center_image_path`, `eac`.`bottom_image_path`, 
`eac`.`approved_by`, `eac`.`budget`, `eac`.`customer_id`, `eac`.`web`
                                   HAVING impression_count < daily_imp
                                    ORDER BY RAND() LIMIT 25

那么它是做什么的呢？它实现了与表advert_abstract的正确左联接，因此即使advert_abstract中没有可用数据，也会显示来自advert_customer的数据。其次，它只显示"created_at"日期(如果它存在并且等于当前日期(。否则，将为该字段返回null。

Answer 2

我认为您只需要包含一个"联接条件"。换言之，将所需的筛选器直接放入联接中，并且不要将包含在where子句中。这样，左边的联接将只包括该表中符合标准的行

选择eac.id、eac.gender、eac.start_date、eac.end_date、ea.ad_image_path，eac.ad_link，eac.requestfrom，eac.traffic，eac.position，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，eaa.impression _ count_login，eaa.impression _ count_nonlogin，eac.customer_id，eaa.created_at，eac.web，SUM(预算/(DATEDIFF(结束日期，开始日期((*1000(AS每日_imp，SUM(impression_count_login+impression.count_nonlogin(AS impression_count来自广告客户INNER在eac.id=eacr.advert_customer_id上作为eacr加入advent_customer_regionsINNER在er.id=eacr.region_id上按er加入区域#如果表中有从当前日期开始的任何行，我希望发生这种情况在eac.id=eaa.advert_customer_id上加入advert_abstract作为eaaAND DATE(eaa.created_at(=CURDATE((WHERE er.name="印度"AND eac.web=1AND eac.registertype="已支付"AND eac.active=1AND eac.approved_by=1AND eac.gender="全部"AND CURDATE((介于eac.start_date和eac.end_date之间分组依据eac.id、eac.gender、eac.start_date、eac.end_date、ea.ad_image_path，eac.ad_link，eac.requestfrom，eac.traffic，eac.position，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_login，eaa.impression _ count_nonlogin，eac.customer_id，eaa.created_at，eac.web

请注意，您不需要使用date(curdate())，因为curdate()只是一个日期。我也建议在上面的查询中使用between，我认为这更容易理解。

这些行中缺少一些列别名，请参阅???.

, SUM (???.budget / (DATEDIFF (???.end_date, ???.START_DATE)) * 1000) AS daily_imp
, SUM (???.impression_count_login + ???.impression_count_nonlogin) AS impression_count

使用适当的别名引用所有列是很重要的。