MySQL 获取有条件的表之间的记录

Question

我遇到了一个大问题，我有以下 SQL 结构，其中 contracts 表是动态生成的，具有随机名称，如 _xyz， _xxx等:

CREATE TABLE contract_xyz(
  id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
  created_at DATETIME NOT NULL
 );
CREATE TABLE contract_events(
    id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
    id_contract INT(11) NOT NULL,    
    table_contract VARCHAR(255) NOT NULL,
    created_at DATETIME NOT NULL
);
INSERT INTO contract_xyz (id,created_at) VALUES (1,'2016-11-01');
INSERT INTO contract_xyz (id,created_at) VALUES (2,'2016-10-21');
INSERT INTO contract_xyz (id,created_at) VALUES (3,'2016-11-04');
INSERT INTO contract_events(id,id_contract,table_contract,created_at) VALUES (1,1,'contract_xyz','2016-11-03');
INSERT INTO contract_events(id,id_contract,table_contract,created_at) VALUES (2,3,'contract_xyz','2016-11-04');

每个合约都可以有自己的事件。我需要解决以下问题:

获取所有在 2 天内没有新事件，或根本没有任何事件且创建时间超过 2 天的合约。

我试过 LET JOIN 但结果不正确。我得到的最接近的是以下查询:

SELECT `contract_xyz`.*
FROM `contract_xyz`
WHERE EXISTS(SELECT 1
         FROM `contract_events`
         WHERE
           `contract_events`.id_contract = `contract_xyz`.id AND `contract_events`.table_contract = 'contract_xyz'
           AND DATEDIFF(CURDATE(), `contract_events`.created_at) >= 2
         ORDER BY `contract_events`.created_at DESC
         LIMIT 1)
  OR (NOT EXISTS(SELECT 1
                 FROM `contract_events`
                 WHERE `contract_events`.id_contract = `contract_xyz`.id AND
                       `contract_events`.table_contract = 'contract_xyz') AND
      DATEDIFF(CURDATE(), `contract_xyz`.created_at) >= 2);

但我仍然找不到没有任何事件的合约，并且是在 2 天前创建的。

Answer 1

我会为每个契约(Contract)创建一个包含最大事件日期的子查询。我会在这个子查询上离开 join contracts 表。您可以根据最大事件日期和创建日期字段进行过滤以获得预期结果:

select c.*
from contract_xyz c
left join 
    (select id_contract,
           max(created_at) max_event_date
     from contract_events
     group by id_contract) t on c.id-t.id_contract
 where
     DATEDIFF(CURDATE(), t.max_event_date) >= 2
     or (t.max_event_date is null and DATEDIFF(CURDATE(), c.created_at) >= 2)

或者，您不使用子查询，而是直接使用 group by 连接 2 个表，并在 having 子句中进行过滤。