Mysql嵌套查询访问父字段值

Question

我需要访问嵌套到嵌套查询中的父字段值。这是我的查询

    SELECT

        (
            sum(co.product_baseprice)  + (
select COALESCE(sum(testsum), 0) 
            from (SELECT
                        COALESCE (SUM(rm_baseprice), 0) * COALESCE (rm_quantity, 0) as testsum
                    FROM
                        rm_orders
                    WHERE
                        order_id = o.id
                    GROUP BY
                        id                      
                    order by 
                        id) as temp1)
        ) AS p_baseprice,
        (
            sum(co.product_vat) 
        ) AS p_vat,
        o.surcharge_value_id
    FROM
        orders AS o
    INNER JOIN customer_orders AS co ON o.id = co.order_id 
    WHERE
        1 = 1
        -- o.id = 62720
    GROUP BY
        o.id
    ORDER BY
        ValueAfterVAT DESC
    LIMIT 25;

在 sub to sub 查询中，您发现字段 o.id 是订单表的字段。当我尝试执行此查询时，我会看到类似

的错误

"Error Code: 1054. Unknown column 'o.id' in 'where clause'  0.000 sec"

如何在嵌套到嵌套查询中访问 o.id。

Answer 1

实际上你不需要在内部查询中与父字段连接，将它放在外部查询中。

SELECT 
    sum(co.product_baseprice)  + (p_baseprice.testsum), 
     o.id, co.product_vat AS p_vat,
     o.surcharge_value_id
FROM
    orders AS o
    INNER JOIN customer_orders AS co ON o.id = co.order_id 
-- here query from SELECT was placed there. Note! LEFT OUTER
-- used since you used COALESCE
    LEFT OUTER JOIN ( 
         SELECT order_id,
                COALESCE (SUM(rm_baseprice), 0) * COALESCE (rm_quantity, 0) as testsum
         FROM
            rm_orders
         GROUP BY
            order_id
    ) as p_baseprice ON p_baseprice.order_id = o.id
    WHERE ...