[SQL] WooCommerce Top 200 Best Customers List 買最多的客戶排序

WooTop200Customers.sql

SELECT DISTINCT ID, SUM(CONVERT(pm.meta_value, UNSIGNED INTEGER)) AS total, pm4.meta_value AS user_id, pm2.meta_value AS customer, pm3.meta_value AS email, pm5.meta_value AS mobile
FROM pm_posts AS pp 
LEFT JOIN pm_postmeta AS pm ON pp.ID = pm.post_id
LEFT JOIN pm_postmeta AS pm2 ON pp.ID = pm2.post_id
LEFT JOIN pm_postmeta AS pm3 ON pp.ID = pm3.post_id
LEFT JOIN pm_postmeta AS pm4 ON pp.ID = pm4.post_id
LEFT JOIN pm_postmeta AS pm5 ON pp.ID = pm5.post_id
WHERE pp.post_status IN ('wc-processing', 'wc-completed') AND pm.meta_key = '_order_total' AND pm2.meta_key = '_billing_name' AND pm3.meta_key = '_billing_email' AND pm5.meta_key = '_billing_phone' AND pm4.meta_key = '_customer_user' AND pm4.meta_value != 0
GROUP BY user_id 
ORDER BY total DESC
limit 200