PostgreSQL的JOIN与数组元素顺序数组类型，如何实现？ [英] 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[],

）;

此表包含数组类型的字段。每一行包含从表在特定的顺序项的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}.

现在，我想从表中的数据项目从表中选择行 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.

我试图JOIN：

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在同一顺序中的数组字段。你能帮助我，如何从项目的数据表与阵列的ID为了对应从 some_chosen_data_in_order 表中特定的行？

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?

推荐答案

也许你的正常化表将是我可以给你最好的建议。

Probably normalizing your table would be the best advice I can give you.

该INT_ARRAY的contrib模块有一个IDX的功能，这将使你在数组中的INT的索引位置。也有是在的IDX功能片段维基，对于任何数据类型的数组的作品。

The int_array contrib module has an idx function that will give you the int's index position in the array. Also there is an idx function on the snippets wiki that works for array's of any data types.

SELECT i.*, idx(id_items, i.id) AS idx
FROM some_chosen_data_in_order s
JOIN items i ON i.id = ANY(s.id_items)
ORDER BY idx(id_items, i.id)

这篇关于PostgreSQL的JOIN与数组元素顺序数组类型，如何实现？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！