WordPress中Subquery返回多行错误：如何正确获取商品常规价格？

这个报错的根源很明确：你用来取商品常规价格的子查询没有和主表中的商品ID做关联，它现在会把wp_postmeta表里所有meta_key为_regular_price的记录都查出来——这肯定是多行结果，但主查询里每个商品行只能对应一个价格，数据库自然就报错了。

修正后的完整SQL语句 #

我们需要给价格子查询加上post_id = p1.ID的关联条件，确保每个商品只获取自己的常规价格。另外我还优化了图片的查询方式（用JOIN替代子查询，性能更优），同时去掉了原语句里冗余的wp_posts p3关联（因为你没用到这个表的数据）：

SELECT 
    p1.ID,
    p1.post_title,
    p1.post_excerpt,
    p2.meta_value AS thumbnail_id,
    p1.guid AS product_guid,
    p_attachment.guid AS img,
    (SELECT meta_value FROM wp_postmeta WHERE meta_key = '_regular_price' AND post_id = p1.ID) AS price
FROM wp_posts p1
JOIN wp_postmeta p2 
    ON p1.ID = p2.post_id 
    AND p1.post_type = 'product' 
    AND p1.post_status = 'publish' 
    AND p2.meta_key = '_thumbnail_id'
LEFT JOIN wp_posts p_attachment
    ON p2.meta_value = p_attachment.ID 
    AND p_attachment.post_type = 'attachment'

额外优化建议（可选） #

如果你的商品存在促销价（_sale_price字段），可以用COALESCE函数优先显示促销价，没有促销价时再显示常规价格，这样更贴合电商场景：

SELECT 
    p1.ID,
    p1.post_title,
    p1.post_excerpt,
    p2.meta_value AS thumbnail_id,
    p1.guid AS product_guid,
    p_attachment.guid AS img,
    COALESCE(
        (SELECT meta_value FROM wp_postmeta WHERE meta_key = '_sale_price' AND post_id = p1.ID),
        (SELECT meta_value FROM wp_postmeta WHERE meta_key = '_regular_price' AND post_id = p1.ID)
    ) AS price
FROM wp_posts p1
JOIN wp_postmeta p2 
    ON p1.ID = p2.post_id 
    AND p1.post_type = 'product' 
    AND p1.post_status = 'publish' 
    AND p2.meta_key = '_thumbnail_id'
LEFT JOIN wp_posts p_attachment
    ON p2.meta_value = p_attachment.ID 
    AND p_attachment.post_type = 'attachment'

这样修改后，每个商品行都会对应唯一的价格，就不会再出现「Subquery returns more than one row」的错误了。

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