MYSQL UNION和ORDER BY无法正常工作

我有一个mysql 查询,如下所示

(SELECT order_product.op_id,order_product.ocat_id,order_product.op_partnunber,order_product.op_name,order_product.op_upc,order_product.op_desc,order_stockavailable.osa_id,order_stockavailable.of_id,order_stockavailable.osa_stocka,order_category.ocat_name
FROM 
    order_product 
    LEFT JOIN order_category 
    ON order_product.ocat_id = order_category.ocat_id
    LEFT JOIN order_stockavailable 
    ON  order_product.op_id = order_stockavailable.op_id)
UNION 
(SELECT order_product.op_id,order_stockavailable_attributes.id,order_stockavailable_attributes.of_id,order_stockavailable_attributes.opap_stock,order_category.ocat_name
FROM order_product 
    LEFT JOIN order_category 
    ON order_product.ocat_id = order_category.ocat_id
    LEFT JOIN order_stockavailable 
    ON  order_product.op_id = order_stockavailable.op_id 
    LEFT JOIN order_stockavailable_attributes 
    ON  order_product.op_id = order_stockavailable_attributes.op_id)
ORDER BY order_product.op_name

查询是givng错误,T

来自其中一个SELECT的表’order_product’不能在全局ORDER子句中使用

我检查了MysqL手册,但没有得到任何线索,任何帮助都会非常棒.

最佳答案

SELECT * 
FROM (
    SELECT order_product.op_id,order_category.ocat_name
    FROM 
        order_product 
        LEFT JOIN order_category 
        ON order_product.ocat_id = order_category.ocat_id
        LEFT JOIN order_stockavailable 
        ON  order_product.op_id = order_stockavailable.op_id
    UNION 
    SELECT order_product.op_id,order_category.ocat_name
    FROM order_product 
        LEFT JOIN order_category 
        ON order_product.ocat_id = order_category.ocat_id
        LEFT JOIN order_stockavailable 
        ON  order_product.op_id = order_stockavailable.op_id 
        LEFT JOIN order_stockavailable_attributes 
        ON  order_product.op_id = order_stockavailable_attributes.op_id
) t
ORDER BY op_name

顺便说一句：没有必要将UNION的各个SELECT放入括号中.

MYSQL UNION和ORDER BY无法正常工作

猜你在找的MySQL相关文章