优化查询 - 从同一个子查询中选择 COUNT 和 SUM

I have a query which takes to long to execute. I am sure there are simple ways to improve it. For example, I use the same sub-query twice for displaying two different columns (sum and count) but encountered several errors while trying to solve it on my own.

SELECT u.ID,
u.user_email AS mail, 
u.user_login AS userName, 
u.user_registered  AS signUpDate, 
(select meta_value from wp_usermeta where user_id = u.ID and meta_key = 'first_name' limit 1) as firstName, 
(select meta_value from wp_usermeta where user_id = u.ID and meta_key = 'last_name' limit 1) as lastName, 
(select meta_value from wp_usermeta where user_id = u.ID and meta_key = 'billing_phone' limit 1) as billingPhone, 
(select meta_value from wp_usermeta where user_id = u.ID and meta_key = 'shipping_phone' limit 1) as shippingPhone, 
(SELECT COUNT(meta_value) from wp_postmeta WHERE meta_key = '_order_total' and post_id IN (select post_id from wp_postmeta where meta_value = u.ID and meta_key = '_customer_user')) as orderCount,
(SELECT SUM(meta_value) from wp_postmeta WHERE meta_key = '_order_total' and post_id IN (select post_id from wp_postmeta where meta_value = u.ID and meta_key = '_customer_user')) as moneySpent 
FROM wp_users u;

推荐答案

@Timur，根据您在第一个答案的评论中提出的问题；你必须做这样的事情.您仍然需要连接，但 wp_usermeta 表不需要那么多连接.您仍然需要加入 wp_postmeta 两次，因为您无法在 '_customer_user' meta_key 上进行加入并同时检索 '_order_total' meta_key 在单个连接中.

SELECT    u.ID, 
          u.user_email AS mail,
          u.user_login AS userName,
          u.user_registered AS signUpDate,
          MAX(CASE WHEN um.meta_key = 'first_name'     THEN um.meta_value END) AS firstName, 
          MAX(CASE WHEN um.meta_key = 'last_name'      THEN um.meta_value END) AS lastName, 
          MAX(CASE WHEN um.meta_key = 'billing_phone'  THEN um.meta_value END) AS billingPhone, 
          MAX(CASE WHEN um.meta_key = 'shipping_phone' THEN um.meta_value END) AS shippingPhone,
          COUNT(pval.meta_value) AS orderCount,
          SUM(pval.meta_value) AS moneySpent
FROM      wp_users AS u
LEFT JOIN wp_usermeta AS um   ON u.ID = um.user_id
LEFT JOIN wp_postmeta AS pm   ON u.ID = pm.meta_value      AND pm.meta_key = '_customer_user'
LEFT JOIN wp_postmeta AS pval ON pm.post_id = pval.post_id AND pval.meta_key = '_order_total'
WHERE     (um.meta_key IN ('first_name', 'last_name', 'billing_phone', 'shipping_phone') 
           OR um.meta_key IS NULL)
GROUP BY  u.ID, 
          u.user_email,
          u.user_login,
          u.user_registered;