mysql - 根据其他表的条件从表中选择

Question

广告表

+----+----------+-------------------+
| id | username |    web_address    |
+----+----------+-------------------+
|  1 | admin    | my-best-positions |
|  2 | admin    | after_after       |
|  3 | admin    | after             |
|  4 | admin    | effort            |
+----+----------+-------------------+

事件日志表

+----+----------+-------------------+------------+
| id | username |    web_address    | timeperiod |
+----+----------+-------------------+------------+
|  5 | admin    | my-best-positions | 1565021746 |
|  4 | admin    | my-best-positions | 1564935346 |
|  3 | admin    | after_after       | 1564935346 |
|  2 | admin    | after             | 1564935346 |
|  1 | admin    | effort            | 1564935346 |
+----+----------+-------------------+------------+

上表中的时间段如下:

1565021746 = GMT: Monday, August 5, 2019 4:15:46 PM

1564935346 = GMT: Sunday, August 4, 2019 4:15:46 PM

fiddle 在哪里？

https://www.db-fiddle.com/f/BJJjFHbB1dY7EU1p3auRj/2

代码在哪里？

SELECT * 
FROM   advertisements 
WHERE  IF(EXISTS (SELECT 1 
                  FROM   activitylogs 
                  WHERE  web_address = advertisements.web_address 
                         AND Timestampdiff(day, From_unixtime(timeperiod), 
                             From_unixtime(Unix_timestamp())) 
                             >= 1 
                         AND Date(From_unixtime(timeperiod)) <> CURRENT_DATE() 
                  ORDER  BY timeperiod DESC) 
           OR NOT EXISTS (SELECT 1 
                          FROM   activitylogs 
                          WHERE  web_address = advertisements.web_address), 
       web_address <> '', 
              NULL) 

电流输出是多少？

+----+----------+-------------------+
| id | username |    web_address    |
+----+----------+-------------------+
|  1 | admin    | my-best-positions |
|  2 | admin    | after_after       |
|  3 | admin    | after             |
|  4 | admin    | effort            |
+----+----------+-------------------+

预期的输出是什么？

输出不应包含那些具有当天时间段的行，而只包含那些时间段比当前时间戳小 1 天的行。例如，即使 my-best-positions 有一个今天的时间段。

请帮忙，因为它对我来说是一场噩梦。

Answer 1

SELECT a.* 
  FROM advertisements a
  LEFT
  JOIN activitylogs l
    ON l.web_address = a.web_address
   AND DATE(FROM_UNIXTIME(l.timeperiod)) = CURDATE()
 WHERE l.id IS NULL;