PostgreSQL获取用户表中特定订单的客户与订单ID

首先修正你语句中的几个问题：

• ANY()只能匹配数组元素，但无法保留数组的原始顺序
• 未关联user和orders表，无法获取客户及订单信息
• user是PostgreSQL保留字，引用时需用双引号包裹；另外你提供的user表建表语句有语法错误，外键字段应写成order_id INTEGER REFERENCES orders(order_id)

以下是满足需求的完整SQL语句：

SELECT 
    -- 客户信息
    u.customer_id,
    u.customer_fname,
    u.customer_lname,
    -- 订单信息
    o.order_id,
    -- 商品信息
    i.*,
    -- 商品在数组中的原始顺序位置
    arr.ordinality AS item_display_order
FROM 
    some_chosen_data_in_order AS S
-- 展开id_order数组并保留元素的原始顺序序号
JOIN unnest(S.id_order) WITH ORDINALITY AS arr(item_id, ordinality) ON true
-- 关联items表获取对应商品数据
JOIN items AS i ON i.item_id = arr.item_id
-- 关联user表获取客户信息
JOIN "user" AS u ON u.customer_id = S.customer_id
-- 关联orders表获取订单信息
JOIN orders AS o ON o.order_id = u.order_id
WHERE 
    S.customer_id = ?
-- 按数组中的原始顺序排序结果
ORDER BY 
    arr.ordinality;

关键说明： #

• unnest(... WITH ORDINALITY)：这是PostgreSQL中处理数组并保留顺序的核心方法，它会把数组拆成多行，ordinality字段记录每个元素在原数组中的位置（从1开始计数）
• 多表关联逻辑：通过some_chosen_data_in_order的customer_id关联user表，再通过user表的order_id关联orders表，最终把客户、订单、商品信息整合到结果中
• 排序：最后通过arr.ordinality排序，确保商品的顺序和id_order数组中的顺序完全一致

内容的提问来源于stack exchange，提问作者Macy Creed