PostgreSQL报错[42P01]：orders__line_items表缺少FROM子句条目求助

你的问题出在两个核心点：

1. 外层查询直接引用了子查询内部的原始表orders__line_items，但外层只能访问子查询的别名orlin，没法直接用原表名调用字段。
2. 子查询里用SELECT *，三个关联表（orders__line_items、orders、products）都有id字段，导致子查询结果集里存在多个重复的id，不管是子查询内部还是外层调用都会触发歧义错误。

修正方案一：明确指定子查询所需字段（推荐） #

直接在子查询里列出需要的字段，给重复的id起别名，从根源避免歧义：

SELECT
    orlin.line_item_id::character varying AS id,
    CAST(orlin.created_at::timestamptz AS date) AS timestamp,
    orlin.price_set__shop_money__amount AS price,
    orlin.quantity AS quantity,
    orlin._sdc_source_key_id AS prova,
    orlin.product_id AS product_id
FROM
    (SELECT
        orders__line_items.id AS line_item_id,
        orders__line_items.created_at,
        orders__line_items.price_set__shop_money__amount,
        orders__line_items.quantity,
        orders__line_items._sdc_source_key_id,
        orders__line_items.product_id
        -- 如果需要订单或商品表的其他字段，也要明确指定并别名，比如：
        -- shopify_a100."orders".id AS order_id,
        -- shopify_a100."products".title AS product_title
     FROM orders__line_items
     LEFT JOIN shopify_a100."orders" 
         ON orders__line_items._sdc_source_key_id = shopify_a100."orders"."id"
     LEFT JOIN shopify_a100."products" 
         ON orders__line_items."product_id" = shopify_a100."products"."id") AS orlin

修正方案二：保留子查询所有字段但重命名重复id #

如果确实需要子查询返回所有字段，给每个表的id单独起别名，避免字段冲突：

SELECT
    orlin.line_item_id::character varying AS id,
    CAST(orlin.created_at::timestamptz AS date) AS timestamp,
    orlin.price_set__shop_money__amount AS price,
    orlin.quantity AS quantity,
    orlin._sdc_source_key_id AS prova,
    orlin.product_id AS product_id
FROM
    (SELECT
        orders__line_items.*,
        shopify_a100."orders".id AS order_id,
        shopify_a100."products".id AS product_table_id
     FROM orders__line_items
     LEFT JOIN shopify_a100."orders" 
         ON orders__line_items._sdc_source_key_id = shopify_a100."orders"."id"
     LEFT JOIN shopify_a100."products" 
         ON orders__line_items."product_id" = shopify_a100."products"."id") AS orlin

关键提醒 #

多表连接的子查询里别用SELECT *，这种写法不仅会引发字段歧义，还会返回大量不需要的字段，拖慢查询效率。明确指定所需字段是更规范、更安全的做法。

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