sql - 如何在 Select 查询中比较多列与多字段数组？

Question

我在 API 服务器上创建查询时有点卡住了，我只想用修改后的轨道数据进行响应。我有 tracks 表，其中有几列是这样的。

tracks(id, audio_fingerprint, name, creation_date, modified_date)

现在我只想要在上次获取时间戳后更新的轨道(音频指纹数组和上次获取时间戳作为 API 请求参数传递)。

SELECT * from tracks WHERE (audio_fingerprint, modified_date) IN (Array(audioFingerprint, > lastFetchedTimestamp));

(^^ 无效查询，仅供理解)。

谢谢

Answer 1

示例数据:

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;