子查询别名

我正在运行这个 SQL 查询:

SELECT wp_woocommerce_order_items.order_id As No_Commande
FROM  wp_woocommerce_order_items
LEFT JOIN
(
SELECT meta_value As Prenom
FROM wp_postmeta
WHERE meta_key = '_shipping_first_name'
) AS a
ON wp_woocommerce_order_items.order_id = a.post_id
WHERE  wp_woocommerce_order_items.order_id =2198

我得到了这个错误:

# 1054-on 子句中未知列‘ a.post _ id’。

我认为我的代码非常简单，但我不能使它正确。我做错了什么？

435912 次浏览

小开

最佳答案

You didn't select post_id in the subquery. You have to select it in the subquery like this:

SELECT wp_woocommerce_order_items.order_id As No_Commande
FROM  wp_woocommerce_order_items
LEFT JOIN
(
SELECT meta_value As Prenom, post_id  -- <----- this
FROM wp_postmeta
WHERE meta_key = '_shipping_first_name'
) AS a
ON wp_woocommerce_order_items.order_id = a.post_id
WHERE  wp_woocommerce_order_items.order_id =2198

小开

我认识到这个答案是有效的，并且已经被接受，但是有一个更干净的方法来写这个查询。在 mysql 和 postgres 上测试。

SELECT wpoi.order_id As No_Commande
FROM  wp_woocommerce_order_items AS wpoi
LEFT JOIN wp_postmeta AS wpp ON wpoi.order_id = wpp.post_id
AND wpp.meta_key = '_shipping_first_name'
WHERE  wpoi.order_id =2198