示例数据:
create table tracks (audio_fingerprint text, modified_date date);
insert into tracks values
('a', '2017-01-10'),
('b', '2017-01-10'),
('a', '2017-02-10'),
('b', '2017-02-10'),
('c', '2017-02-01');
将参数放在with
查询中,并将其与表连接:
with given_values (fingerprint, last_fetched) as (
values
('a', '2017-01-01'::date),
('b', '2017-02-01')
)
select *
from tracks t
join given_values v
on t.audio_fingerprint = v.fingerprint
and t.modified_date > v.last_fetched;
audio_fingerprint | modified_date | fingerprint | last_fetched
-------------------+---------------+-------------+--------------
a | 2017-01-10 | a | 2017-01-01
a | 2017-02-10 | a | 2017-01-01
b | 2017-02-10 | b | 2017-02-01
(3 rows)
除了CTE,您还可以使用派生表:
select *
from tracks t
join (
values
('a', '2017-01-01'::date),
('b', '2017-02-01')
) v(fingerprint, last_fetched)
on t.audio_fingerprint = v.fingerprint
and t.modified_date > v.last_fetched;