我在API服务器上创建查询时有些卡住,我只想用修改后的跟踪数据进行响应。我有tracks
几列这样的表。
tracks(id, audio_fingerprint, name, creation_date, modified_date)
现在,我只想要在上次获取的时间戳(作为API请求参数传递的音频指纹和上一次获取的时间戳的数组)之后更新的轨道。
SELECT * from tracks WHERE (audio_fingerprint, modified_date) IN (Array(audioFingerprint, > lastFetchedTimestamp));
(^^这是无效的查询,仅用于理解)。
谢谢
示例数据:
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;
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句