我有重复的商业订单,我想删除他们只保留唯一的干净簿记。
我不擅长sql,我写了这个请求,但是当有重复的时候,它会列出两个重复的。
SELECT *
FROM `wp_posts`
WHERE post_type = 'shop_order' AND post_status = 'wc-completed'
GROUP BY post_date
HAVING count(*) > 1
ORDER BY `wp_posts`.`post_date` DESC
这给了我307个结果。
如何编写正确的请求来删除重复项并只保留唯一的订单?
样本数据:
ID post_author post_date post_date_gmt post_content post_title post_excerpt post_status comment_status ping_status post_password post_name to_ping pinged post_modified post_modified_gmt post_content_filtered post_parent guid menu_order post_type post_mime_type comment_count
22282 227 2018-02-04 01:00:00 2018-02-04 00:00:00 Order – February 4, 2018 @ 01:00 AM wc-completed open closed order-4-02-18-6 2018-03-19 17:12:32 2018-03-19 16:12:32 0 https://www.hemen-biarritz.com/?post_type=shop_ord... 0 shop_order 2
22277 0 2018-01-29 01:00:00 2018-01-29 00:00:00 Order – January 29, 2018 @ 01:00 AM wc-completed open closed order-29-01-18-2 2018-03-19 17:12:33 2018-03-19 16:12:33 0 https://www.hemen-biarritz.com/?post_type=shop_ord... 0 shop_order 2
22276 0 2018-01-28 01:00:00 2018-01-28 00:00:00 Order – January 28, 2018 @ 01:00 AM wc-completed open closed order-28-01-18-2 2018-03-19 17:12:33 2018-03-19 16:12:33 0 https://www.hemen-biarritz.com/?post_type=shop_ord... 0 shop_order 2
22275 0 2018-01-25 01:00:00 2018-01-25 00:00:00 Order – January 25, 2018 @ 01:00 AM wc-completed open closed order-25-01-18-2 2018-03-19 17:12:33 2018-03-19 16:12:33 0 https://www.hemen-biarritz.com/?post_type=shop_ord... 0 shop_order 2
22232 154 2018-01-24 00:00:00 2018-01-24 00:00:00 Order – January 24, 2018 @ 12:00 AM wc-completed open closed order-24-01-18-4 2018-01-24 00:00:00 2018-01-24 00:00:00 0 https://www.hemen-biarritz.com/?post_type=shop_ord... 0 shop_order 0
22230 213 2018-01-23 00:00:00 2018-01-23 00:00:00 Order – January 23, 2018 @ 12:00 AM wc-completed open closed order-23-01-18-2 2018-01-23 00:00:00 2018-01-23 00:00:00 0 https://www.hemen-biarritz.com/?post_type=shop_ord... 0 shop_order 0
22229 0 2018-01-22 00:00:00 2018-01-22 00:00:00 Order – January 22, 2018 @ 12:00 AM wc-completed open closed order-22-01-18-2 2018-01-22 00:00:00 2018-01-22 00:00:00 0 https://www.hemen-biarritz.com/?post_type=shop_ord... 0 shop_order 0
22228 224 2018-01-20 00:00:00 2018-01-20 00:00:00 Order – January 20, 2018 @ 12:00 AM wc-completed open closed order-20-01-18-2 2018-01-20 00:00:00 2018-01-20 00:00:00 0 https://www.hemen-biarritz.com/?post_type=shop_ord... 0 shop_order 0
22227 0 2018-01-19 00:00:00 2018-01-19 00:00:00 Order – January 19, 2018 @ 12:00 AM wc-completed open closed order-19-01-18-2 2018-01-19 00:00:00 2018-01-19 00:00:00 0 https://www.hemen-biarritz.com/?post_type=shop_ord... 0 shop_order 0
22226 0 2018-01-17 00:00:00 2018-01-17 00:00:00 Order – January 17, 2018 @ 12:00 AM wc-completed open closed order-17-01-18-2 2018-01-17 00:00:00 2018-01-17 00:00:00 0 https://www.hemen-biarritz.com/?post_type=shop_ord... 0 shop_order 0
22225 0 2018-01-16 00:00:00 2018-01-16 00:00:00 Order – January 16, 2018 @ 12:00 AM wc-completed open closed order-16-01-18-2 2018-01-16 00:00:00 2018-01-16 00:00:00 0 https://www.hemen-biarritz.com/?post_type=shop_ord... 0 shop_order 0
22224 0 2018-01-15 00:00:00 2018-01-15 00:00:00 Order – January 15, 2018 @ 12:00 AM wc-completed open closed order-15-01-18-4 2018-01-15 00:00:00 2018-01-15 00:00:00 0 https://www.hemen-biarritz.com/?post_type=shop_ord... 0 shop_order 0
22222 0 2018-01-14 00:00:00 2018-01-14 00:00:00 Order – January 14, 2018 @ 12:00 AM wc-completed open closed order-14-01-18-6 2018-01-14 00:00:00 2018-01-14 00:00:00 0 https://www.hemen-biarritz.com/?post_type=shop_ord... 0 shop_order 0
编辑:
SELECT DISTINCT post_date FROM `wp_posts` WHERE post_type = 'shop_order' AND post_status = 'wc-completed'
给我614个结果,是前一个请求的两倍。
wordpress:5.0版
商业:3.5.2
2条答案
按热度按时间wztqucjr1#
首先,如果您不知道,woocommerce订单数据位于四(4)个表中:
wp_posts
wp_postmeta
wp_woocommerce_order_itemswp_woocommerce_order_itemmeta
下面的钩子函数WPDB
类和方法。它将:在一个查询中获取所有重复的订单id(最低id)(搜索通常唯一的重复订单键)
在一个查询中删除所有重复的查询订单。
但一定要在备份之前做一个数据库备份。
代码将在任何前端页面加载时一次性完成工作(使用后删除):
代码放在活动子主题(或活动主题)的function.php文件中。测试和工作。
重复的订单将在第一个前端页面加载时删除。因此,在此之后,您可以删除或注解代码。
nc1teljy2#
尝试删除连接,任意保留最大
ID
:如果您只想查看删除了逻辑副本的文章,请尝试: