SQL PostgreSQL JOIN with array type with array elements order，如何实现？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2486725/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL JOIN with array type with array elements order, how to implement?
提问by Adiasz
I have two tables in database:
我在数据库中有两个表：
CREATE TABLE items(
id SERIAL PRIMARY KEY,
... some other fields
);
This table contains come data row with unique ID.
此表包含具有唯一 ID 的数据行。
CREATE TABLE some_chosen_data_in_order(
id SERIAL PRIMARY KEY,
id_items INTEGER[],
);
This table contains array type field. Each row contains values of IDs from table itemsin specific order. For example: {2,4,233,5}.
此表包含数组类型字段。每行包含表中items按特定顺序排列的 ID 值。例如：{2,4,233,5}。
Now, I want to get data from table itemsfor chosen row from table some_chosen_data_in_orderwith order for elements in array type.
现在，我想从表中获取数据，以获取表items中所选行的数据some_chosen_data_in_order，并按数组类型中的元素顺序排列。
My attempt was 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 = ?])
But none of them keep IDs in the same order as in array field. Could you help me, how to get data from itemstable with correspond with array IDs order from some_chosen_data_in_ordertable for specific row?
但是它们都没有以与数组字段中相同的顺序保留 ID。你能帮我吗，如何从items表中获取与some_chosen_data_in_order特定行的表中的数组 ID 顺序相对应的数据？
回答by Marcin Kapusta
SELECT t.*
FROM unnest(ARRAY[1,2,3,2,3,5]) item_id
LEFT JOIN items t on t.id=item_id
The above query select items from itemstable with ids: 1,2,3,2,3,5 in that order.
上面的查询从items表中按 ids: 1,2,3,2,3,5 的顺序选择项目。
回答by Scott Bailey
Probably normalizing your table would be the best advice I can give you.
可能规范化你的表格是我能给你的最好的建议。
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 wikithat works for array's of any data types.
int_array contrib 模块有一个 idx 函数，它会给你 int 在数组中的索引位置。在snippets wiki上还有一个idx 函数，它适用于任何数据类型的数组。
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)
回答by aruis
select distinct on (some_chosen_data_in_order.id)
some_chosen_data_in_order.*,
array_to_json( array_agg(row_to_json( items))
over ( partition by some_chosen_data_in_order.id ))
from some_chosen_data_in_order
left join items on items.id = any (some_chosen_data_in_order.id_items)
回答by Roy Merrill
SELECT I.* FROM items AS I
WHERE I.id IN (SELECT UNNEST(id_items) FROM some_chosen_data_in_order
(ARRAY[SELECT S.id_items FROM some_chosen_data_in_order WHERE id = ?])
