如果有来自当前日期的数据，mysql应用左连接

8hhllhi2 于 2021-06-19 发布在 Mysql

关注(0)|答案(2)|浏览(337)

我试图使用下面的查询获取数据，但这里的问题是它没有显示来自的数据 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 |
+-----+-------------+--------------------+--------+--------------+--------+---------------------+---------------------+-----+--------+---------------------+

另一张table `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` 该表应该返回数据，否则这些列的数据将为空。

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` 以及 `LEFT JOIN` 条件或是否有任何其他联接方法我应该尝试从中获取数据。这样我就可以得到所有的数据，如果数据是不可用的 `advert_abstract` 然后我也得出了其余的细节。
任何建议都会有帮助，谢谢你（提前）！

mysql

来源：https://stackoverflow.com/questions/53315401/mysql-apply-left-join-if-there-is-any-data-from-current-date

2条答案

按热度按时间

hs1ihplo1#

我想你只需要加入一个“连接条件”。换句话说，将所需的过滤器直接放入联接中，而不要包含在where子句中。这样，左联接将只包含该表中符合该条件的行

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
  , eaa.impression_count_login, eaa.impression_count_nonlogin
  , eac.customer_id, eaa.created_at, eac.web
  , SUM (budget / (DATEDIFF (end_date, START_DATE)) * 1000) AS daily_imp
  , SUM (impression_count_login + impression_count_nonlogin) AS impression_count
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

   # I WANT THIS TO HAPPEN IF THERE IS ANY ROW IN THE TABLE FROM CURRENT DATE 
LEFT JOIN advert_abstract AS eaa ON eac.id = eaa.advert_customer_id 
                                AND DATE(eaa.created_at) = CURDATE()

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 CURDATE() BETWEEN eac.start_date AND eac.end_date
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, 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

使用适当的别名引用所有列非常重要。

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

5fjcxozz2#

你并不是在说你想要达到的目标。所以我只能猜测。。。但这里有一个建议：

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_customer 即使中没有可用数据也会显示 advert_abstract . 其次，它只显示“created_at”日期（如果它存在并且等于当前日期）。否则，该字段将返回null。

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

我来回答

如果有来自当前日期的数据，mysql应用左连接

2条答案

相关问题

热门标签

最新问答