Mysql 基于两个单元格值的 SQL 过滤表

我有一张表，上面有联系人详细信息"lead"，还有一张表，上面有关于发送到lead"mail_log"的邮箱的信息

我想获得所有已收到mail_id=1但未收到mail_id=2的线索.

因此，我需要潜在客户的邮箱地址和最后一封邮件的发送日期(在本例中为mail_id 1)

非常感谢.

CREATE TABLE `lead` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `data_id` INT(11) NULL DEFAULT NULL,
    `email` VARCHAR(200) NULL DEFAULT NULL COLLATE 'latin1_german2_ci',
    `deleted` TINYINT(1) NULL DEFAULT NULL,
    PRIMARY KEY (`id`) USING BTREE
)COLLATE='latin1_german2_ci' ENGINE=MyISAM AUTO_INCREMENT=4 ;

INSERT INTO `lead` (`id`, `data_id`, `email`, `deleted`) VALUES (1, 217, 'lead217@example.com', NULL);
INSERT INTO `lead` (`id`, `data_id`, `email`, `deleted`) VALUES (2, 578, 'lead578@example.com', NULL);
INSERT INTO `lead` (`id`, `data_id`, `email`, `deleted`) VALUES (3, 124, 'lead124@example.com', NULL);



CREATE TABLE `mail_log` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `lead_id` INT(11) NULL DEFAULT NULL,
    `mail_id` INT(11) NULL DEFAULT NULL,
    `timestamp` DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (`id`) USING BTREE
)COLLATE='latin1_german2_ci' ENGINE=MyISAM ROW_FORMAT=FIXED AUTO_INCREMENT=4 ;

INSERT INTO `mail_log` (`id`, `lead_id`, `mail_id`, `timestamp`) VALUES (1, 217, 1, '2022-03-03 00:00:00');
INSERT INTO `mail_log` (`id`, `lead_id`, `mail_id`, `timestamp`) VALUES (2, 578, 1, '2022-03-03 00:00:00');
INSERT INTO `mail_log` (`id`, `lead_id`, `mail_id`, `timestamp`) VALUES (3, 217, 2, '2022-03-14 00:00:00');