gpt4 book ai didi

mysql - 使用 WooCommerce 的 'O.order_id' SQL 查询中的未知列 'on clause'

转载 作者:行者123 更新时间:2023-11-29 01:34:54 26 4
gpt4 key购买 nike

我下面的查询返回以下错误消息“‘on 子句’中的未知列‘O.order_id’”。我猜这是一个优先级问题?但我没有足够的背景来弄清楚它可能在哪里。

我最近刚刚添加了“where”子句,以便我可以过滤产品类别。

SELECT
MAX(
CASE WHEN OIM.meta_key = 'enrolment_id' THEN(
SELECT
firstname
FROM
wp_sp_enrolments E
WHERE
OIM.meta_value = E.id
)
END
) AS 'firstname',
MAX(
CASE WHEN OIM.meta_key = 'enrolment_id' THEN(
SELECT
surname
FROM
wp_sp_enrolments E
WHERE
OIM.meta_value = E.id
)
END
) AS 'surname',
O.order_item_name AS 'school',
MAX(
CASE WHEN OIM.meta_key = 'class' THEN OIM.meta_value
END
) AS 'class',
MAX(
CASE WHEN OIM.meta_key = 'fee-type' THEN OIM.meta_value
END
) AS 'fees',
MAX(
CASE WHEN OIM.meta_key = 'enrolment_Id' THEN(
SELECT
UM.meta_value
FROM
wp_usermeta UM,
wp_sp_enrolments E
WHERE
UM.meta_key = '_user_phone' AND UM.user_id = E.memberid AND E.id = OIM.meta_value
LIMIT 1
)
END
) AS 'phone', MAX(
CASE WHEN OIM.meta_key = 'enrolment_id' THEN(
SELECT
U.user_email
FROM
wp_users U,
wp_sp_enrolments E
WHERE
U.id = E.memberid AND E.id = OIM.meta_value
LIMIT 1
)
END
) AS 'email', P.post_status AS 'status'
FROM
wp_woocommerce_order_items O,
wp_terms T,
wp_term_taxonomy TT,
wp_term_relationships TR,
wp_woocommerce_order_itemmeta OIM
INNER JOIN wp_posts P ON
P.ID = O.order_id
WHERE
T.name = 'workshops' AND T.term_id = TT.term_id AND TT.taxonomy = 'product_cat' AND TT.term_id = TR.term_taxonomy_id AND TR.object_id = OIM.meta_value AND OIM.meta_key = '_product_id' AND OIM.order_item_id = O.order_item_id
GROUP BY
O.order_item_id

最佳答案

如您所料,问题在于逗号运算符和连接运算符的优先级。

关于 https://dev.mysql.com/doc/refman/8.0/en/join.html据称:

However, the precedence of the comma operator is less than that of INNER JOIN, CROSS JOIN, LEFT JOIN, and so on. If you mix comma joins with the other join types when there is a join condition, an error of the form Unknown column 'col_name' in 'on clause' may occur.

意思是,最好不要混合。

所以不是内连接,而是

, wp_posts P

并在WHERE

and P.id=O.order_id and P.id is not null

或者,您可以将逗号分隔的表格放在括号中:

(wp_woocommerce_order_items O,
wp_terms T,
wp_term_taxonomy TT,
wp_term_relationships TR,
wp_woocommerce_order_itemmeta OIM )
INNER JOIN ...

只是另一个例子,为什么我对逗号运算符有如此强烈的反感。

关于mysql - 使用 WooCommerce 的 'O.order_id' SQL 查询中的未知列 'on clause',我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/55677081/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com