mysql - 如果查询不是特定值，则不显示整行

Question

如果我显示的“payment_method”不是值“x”，我不想显示整行。我如何更新我的代码来执行此操作？

SELECT
    p.ID AS order_id,
    max( CASE WHEN pm.meta_key = '_customer_user' AND p.ID = pm.post_id THEN pm.meta_value END ) AS user_ID,
    max( CASE WHEN pm.meta_key = '_order_total' AND p.ID = pm.post_id THEN pm.meta_value END ) AS order_total,
    max( CASE WHEN pm.meta_key = '_payment_method' AND p.ID = pm.post_id THEN pm.meta_value END ) AS payment_method

FROM wp_posts p
    LEFT JOIN wp_postmeta pm ON p.ID = pm.post_id
    LEFT JOIN wp_woocommerce_order_items oi ON p.ID = oi.order_id
WHERE post_type = 'shop_order' and post_status = 'wc-completed'
GROUP BY p.ID; 

Answer 1

您使用了HAVING 子句。与在聚合之前(通常在加入之前)过滤记录集的 WHERE 子句非常相似，HAVING 子句在聚合后过滤结果集。这是在 SQL 中运行的最后步骤之一:

SELECT
    p.ID AS order_id,
    max( CASE WHEN pm.meta_key = '_customer_user' AND p.ID = pm.post_id THEN pm.meta_value END ) AS user_ID,
    max( CASE WHEN pm.meta_key = '_order_total' AND p.ID = pm.post_id THEN pm.meta_value END ) AS order_total,
    max( CASE WHEN pm.meta_key = '_payment_method' AND p.ID = pm.post_id THEN pm.meta_value END ) AS payment_method

FROM wp_posts p
    LEFT JOIN wp_postmeta pm ON p.ID = pm.post_id
    LEFT JOIN wp_woocommerce_order_items oi ON p.ID = oi.order_id
WHERE post_type = 'shop_order' and post_status = 'wc-completed'    
GROUP BY p.ID
HAVING payment_method <> 'X'