如何在选择查询中比较多列和多字段数组？ [英] How to compare multiple column with multiple field array in Select query?

问题描述

我在API服务器上创建查询时有些卡住，我只想用修改后的跟踪数据进行响应。我有 tracks 表，上面有几列。

I get stuck a bit at query creation at API server, I just want to respond with modified tracks data. I have tracks table with few columns like this.

tracks（id，audio_fingerprint，名称，creation_date，modified_date）

现在，我只想要在上次获取时间戳（音频指纹和上一次获取的时间戳）之后更新的曲目

Now I just want tracks which are updated after it's last fetched timestamp (Array of audio fingerprint and last fetched timestamp passed as API request parameter).

SELECT *从轨道WHERE（音频指纹，修改日期）IN（Array（audioFingerprint，> lastFetchedTimestamp））;

（^^这是无效的查询，仅用于理解）。

(^^ It is invalid query, just used for understanding).

谢谢

推荐答案

示例数据：

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 查询并将其与您的表连接：

Place your arguments in a with query and join it with your table:

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，您还可以使用派生表：

---

Instead of CTE you can also use a derived table:

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;

这篇关于如何在选择查询中比较多列和多字段数组？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！