提问人：Zed0121 提问时间：10/17/2023 最后编辑：LoicTheAztecZed0121 更新时间：10/17/2023 访问量：36
获得一年以来没有订单且不活跃的 Woocommerce 用户
Get Woocommerce Users with no orders AND not active since a year
问：
我发现以下 SQL 查询可以获取没有名字/姓氏且没有帖子的客户：
SELECT DISTINCT `user_id`
FROM `wp_usermeta`
WHERE
`user_id` IN (SELECT DISTINCT `user_id` FROM `wp_usermeta` WHERE `meta_key` = 'wp_capabilities' AND `meta_value` = 'a:1:{s:8:"customer";b:1;}') AND
`user_id` IN (SELECT DISTINCT `user_id` FROM `wp_usermeta` WHERE `meta_key` = 'first_name' AND `meta_value` = '') AND
`user_id` IN (SELECT DISTINCT `user_id` FROM `wp_usermeta` WHERE `meta_key` = 'last_name' AND `meta_value` = '') AND
`user_id` NOT IN (SELECT DISTINCT `user_id` FROM `wp_usermeta` WHERE `meta_key` = 'billing_first_name') AND
`user_id` NOT IN (SELECT DISTINCT `user_id` FROM `wp_usermeta` WHERE `meta_key` = 'billing_last_name') AND
`user_id` NOT IN (SELECT DISTINCT `user_id` FROM `wp_usermeta` WHERE `meta_key` = '_order_count' AND meta_value > 0);
但是，我还想筛选超过 12 个月没有任何活动（例如登录）的客户。
我正在考虑使用“last_update”参数。
有人可以帮忙指导我吗？
答：
2赞
LoicTheAztec
10/17/2023
#1
使用“last_update”元键尝试以下操作：
SELECT DISTINCT `user_id`
FROM `wp_usermeta`
WHERE
`user_id` IN (SELECT DISTINCT `user_id` FROM `wp_usermeta` WHERE `meta_key` = 'wp_capabilities' AND `meta_value` LIKE '%customer%') AND
`user_id` IN (SELECT DISTINCT `user_id` FROM `wp_usermeta` WHERE `meta_key` = 'first_name' AND `meta_value` = '') AND
`user_id` IN (SELECT DISTINCT `user_id` FROM `wp_usermeta` WHERE `meta_key` = 'last_name' AND `meta_value` = '') AND
`user_id` IN (SELECT DISTINCT `user_id` FROM `wp_usermeta` WHERE `meta_key` = 'last_update' AND `meta_value` < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 YEAR))) AND
`user_id` NOT IN (SELECT DISTINCT `user_id` FROM `wp_usermeta` WHERE `meta_key` = 'billing_first_name') AND
`user_id` NOT IN (SELECT DISTINCT `user_id` FROM `wp_usermeta` WHERE `meta_key` = 'billing_last_name') AND
`user_id` NOT IN (SELECT DISTINCT `user_id` FROM `wp_usermeta` WHERE `meta_key` = '_order_count' AND meta_value > 0);
它应该起作用。
现在您可以在代码中替换（因为我没有看到更多的_order_count元键）：
`user_id` NOT IN (SELECT DISTINCT `user_id` FROM `wp_usermeta` WHERE `meta_key` = '_order_count' AND meta_value > 0)
跟：
`user_id` NOT IN (SELECT DISTINCT `user_id` FROM `wp_usermeta` WHERE `meta_key` = 'paying_customer' AND `meta_value` = '1')
评论