PostgreSQL JOIN with array type with array elements order，如何实现? [英] PostgreSQL JOIN with array type with array elements order, how to implement?

问题描述

我在数据库中有两个表:

I have two tables in database:

CREATE TABLE items(
 id SERIAL PRIMARY KEY,
 ... some other fields
);

此表包含具有唯一 ID 的数据行.

This table contains come data row with unique ID.

CREATE TABLE some_chosen_data_in_order(
 id SERIAL PRIMARY KEY,
 id_items INTEGER[],
);

此表包含数组类型字段.每行包含表 items 中特定顺序的 ID 值.例如:{2,4,233,5}.

This table contains array type field. Each row contains values of IDs from table items in specific order. For example: {2,4,233,5}.

现在，我想从表 items 中获取数据，用于从表 some_chosen_data_in_order 中选择的行以及数组类型元素的顺序.

Now, I want to get data from table items for chosen row from table some_chosen_data_in_order with order for elements in array type.

我的尝试是加入:

SELECT I.* FROM items AS I 
JOIN some_chosen_data_in_order AS S ON I.id = ANY(S.id_items) WHERE S.id = ?

第二次尝试是子查询，如:

Second attempt was subquery like:

SELECT I.* FROM items AS I 
WHERE I.id = ANY 
(ARRAY[SELECT S.id_items FROM some_chosen_data_in_order  WHERE id = ?])

但它们都没有以与数组字段中相同的顺序保留 ID.你能帮我吗，如何从 items 表中获取与特定行的 some_chosen_data_in_order 表中的数组 ID 顺序相对应的数据?

But none of them keep IDs in the same order as in array field. Could you help me, how to get data from items table with correspond with array IDs order from some_chosen_data_in_order table for specific row?

推荐答案

SELECT t.*
FROM unnest(ARRAY[1,2,3,2,3,5]) item_id
LEFT JOIN items t on t.id=item_id

上述查询从 items 表中选择 ids 为 1,2,3,2,3,5 的项目.

The above query select items from items table with ids: 1,2,3,2,3,5 in that order.

这篇关于PostgreSQL JOIN with array type with array elements order，如何实现?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！