我有一個表格，其中包含聯系方式“lead”和另一個表格，其中包含有關已發送到潛在客戶“mail_log”的電子郵件的資訊
我想獲取所有已收到 mail_id = 1 但未收到 mail_id = 2 的潛在客戶。并且不應洗掉潛在客戶 = 1。并且 mail_id 1 的時間戳應該超過一周前
結果，我需要潛在客戶的電子郵件地址和最后一封郵件的發送日期（在本例中為 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, '[email protected]', NULL);
INSERT INTO `lead` (`id`, `data_id`, `email`, `deleted`) VALUES (2, 578, '[email protected]', NULL);
INSERT INTO `lead` (`id`, `data_id`, `email`, `deleted`) VALUES (3, 124, '[email protected]', 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');
uj5u.com熱心網友回復：
由于您只需要來自Lead的資料，因此您可以在此處使用：
select *
from `lead` l
where deleted is null
and exists (
select * from mail_log ml
where ml.lead_id = l.data_id and ml.mail_id = 1 and ml.timestamp < date_add(curdate(), interval -1 week)
)
and not exists (
select * from mail_log ml
where ml.lead_id = l.data_id and mail_id = 2
);
DB<>小提琴
uj5u.com熱心網友回復：
select l.email,
ml.timestamp
from lead l
join mail_log ml on l.id = ml.lead_id
where l.deleted <> 1
and ml.mail_id = 1
and ml.timestamp < DATEADD(day, -7, CAST(GETDATE() AS date))
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/444078.html