PostgreSQL：筛选table1中未在table2出现的ID+日期唯一数据并关联表

现有表结构及数据 #

表table1 #

create table table1 (
    tableRes_id int,
    colDate1 TIMESTAMP,
    unique (tableRes_id, colDate1)
);

insert into table1 values 
(1,'2023-03-01 19:02:11.000'),
(1,'2023-03-02 12:29:01.000'),
(2,'2023-03-01 16:01:25.000'),
(1,'2023-03-03 12:41:56.000');

表table2 #

create table table2 (
    tableRes_id int,
    colDate2 TIMESTAMP,
    unique (tableRes_id, colDate2)
);

insert into table2 values 
(1,'2023-03-02 12:29:01.000'),
(2,'2023-03-01 16:01:25.000');

表tableRes #

create table tableRes (
    id int,
    val text,
    code text
);

insert into tableRes values 
(1,'val1','4qw544w'),
(2, 'val2',' fd87rr'),
(3, 'val3',' asd8d78'),
(4, 'val4',' qe9547fd');

查询需求 #

基于tableRes_id和日期（忽略时间部分），获取table1中所有未在table2中出现的记录，并关联tableRes表获取对应的val字段。

期望结果 #

|1|val1|2023-03-01 19:02:11.000|
-------------------------------
|1|val1|2023-03-03 12:41:56.000|

解决方案SQL #

SELECT 
    t1.tableRes_id,
    tr.val,
    t1.colDate1
FROM table1 t1
JOIN tableRes tr ON t1.tableRes_id = tr.id
WHERE NOT EXISTS (
    SELECT 1
    FROM table2 t2
    WHERE t2.tableRes_id = t1.tableRes_id
      AND DATE(t2.colDate2) = DATE(t1.colDate1)
);

思路说明 #

1. 用DATE()函数提取时间戳的日期部分，忽略时分秒，实现按日期维度匹配；
2. 通过NOT EXISTS子查询筛选出table1中，相同tableRes_id且对应日期未在table2中出现的记录；
3. 关联tableRes表，匹配获取对应的val字段值。

内容的提问来源于stack exchange，提问作者majid