如何编写查询近2周未下单Woocommerce用户的SQL语句

你提供的PHP函数仅能校验单个用户是否有过已完成订单，无法批量拉取符合时间条件的用户，直接使用原生SQL查询效率更高，具体实现如下：

可用SQL查询语句 #

SELECT DISTINCT u.ID AS user_id, u.user_email, u.display_name
FROM wp_users u
LEFT JOIN (
    -- 先查询近14天有已完成订单的用户ID集合
    SELECT DISTINCT pm.meta_value AS user_id
    FROM wp_posts p
    JOIN wp_postmeta pm ON p.ID = pm.post_id
    WHERE p.post_type = 'shop_order'
      AND p.post_status = 'wc-completed'
      AND p.post_date >= DATE_SUB(NOW(), INTERVAL 14 DAY)
      AND pm.meta_key = '_customer_user'
) recent_buyers ON u.ID = recent_buyers.user_id
-- 以下两个查询条件二选一，根据业务需求调整
-- 条件1：仅查询有过历史下单记录、但近2周未下单的用户
WHERE recent_buyers.user_id IS NULL
AND EXISTS (
    SELECT 1 FROM wp_postmeta pm2
    JOIN wp_posts p2 ON pm2.post_id = p2.ID
    WHERE pm2.meta_key = '_customer_user'
      AND pm2.meta_value = u.ID
      AND p2.post_type = 'shop_order'
      AND p2.post_status = 'wc-completed'
)
-- 条件2：查询所有注册用户中近2周未下单的用户（包含从未下过单的注册用户）
-- WHERE recent_buyers.user_id IS NULL

使用说明 #

• 表前缀适配：如果你的WordPress数据库表前缀不是默认的wp_，需要把SQL中的wp_users、wp_posts、wp_postmeta的前缀替换为实际使用的前缀
• 周期调整：如果需要修改未下单的统计周期，将INTERVAL 14 DAY中的14替换为目标天数即可
• 订单状态适配：如果需要统计除已完成之外的其他订单状态，在p.post_status的条件中添加对应订单状态值即可

WordPress中调用示例 #

如果需要在WooCommerce主题/插件中使用该查询，可以直接通过$wpdb调用：

global $wpdb;
// 替换下面的SQL为你调整好的语句
$target_users = $wpdb->get_results("
    SELECT DISTINCT u.ID AS user_id, u.user_email, u.display_name
    FROM {$wpdb->prefix}users u
    LEFT JOIN (
        SELECT DISTINCT pm.meta_value AS user_id
        FROM {$wpdb->prefix}posts p
        JOIN {$wpdb->prefix}postmeta pm ON p.ID = pm.post_id
        WHERE p.post_type = 'shop_order'
          AND p.post_status = 'wc-completed'
          AND p.post_date >= DATE_SUB(NOW(), INTERVAL 14 DAY)
          AND pm.meta_key = '_customer_user'
    ) recent_buyers ON u.ID = recent_buyers.user_id
    WHERE recent_buyers.user_id IS NULL
    AND EXISTS (
        SELECT 1 FROM {$wpdb->prefix}postmeta pm2
        JOIN {$wpdb->prefix}posts p2 ON pm2.post_id = p2.ID
        WHERE pm2.meta_key = '_customer_user'
          AND pm2.meta_value = u.ID
          AND p2.post_type = 'shop_order'
          AND p2.post_status = 'wc-completed'
    )
");

// 遍历获取到的用户处理业务逻辑
foreach ($target_users as $user) {
    // 自定义业务代码
}

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