phpmyadmin 获得Woocommerce用户没有订单，并且自一年以来不活跃

ajsxfq5m 于 2023-10-20 发布在 PHP

关注(0)|答案(1)|浏览(130)

我发现下面的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”参数。
有人能帮我指路吗？

phpmyadmin

来源：https://stackoverflow.com/questions/77304810/get-woocommerce-users-with-no-orders-and-not-active-since-a-year

1条答案

按热度按时间

yyyllmsg1#

使用“last_update”Meta密钥尝试以下操作：

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 Meta密钥）：

`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')

赞(0）回复(0）举报 2023-10-20

我来回答

phpmyadmin 获得Woocommerce用户没有订单，并且自一年以来不活跃

1条答案

相关问题

热门标签

最新问答