mysql - 在多个条件下连接表和更新列

Question

我有两个表 customers 和 orders，其中重要的列是:

/* Customers: */
id        | login     | membershipid
100       | email1    | 0
101       | email2    | 0

/* Orders: */
userid    | total
100       | 150
101       | 120
101       | 450

我想根据每个客户的总购买量更新 membershipid。假设总和小于 500 则为零，如果总和为 500-1000 则为一，如果总和超过 1000 则为二。

我这样得到总和:

SELECT customers.login, sum( orders.total ) AS total_purchased 
FROM xcart_customers AS customers
LEFT JOIN xcart_orders AS orders ON customers.id = orders.userid
GROUP BY customers.login

但我不确定如何更新客户表。像这样的东西:

UPDATE xcart_customers set (membershipid) = (
    SELECT sum( orders.total ) AS total_purchased 
    FROM xcart_customers AS customers
    LEFT JOIN xcart_orders AS orders ON customers.id = orders.userid
    GROUP BY customers.login
)

但在哪里以及如何使用条件(GROUP BY 是否有效？):

CASE 
    WHEN 
    ..
    ELSE 
END

Answer 1

UPDATE xcart_customers 
join
(
    SELECT customers.login, sum( orders.total ) AS total_purchased 
    FROM xcart_customers AS customers
    LEFT JOIN xcart_orders AS orders ON customers.id = orders.userid
    GROUP BY customers.login
) tmp on tmp.login = xcart_customers.login
set xcart_customers.membershipid = case when total_purchased is null or total_purchased < 500 then 0
                                        when total_purchased between 500 and 1000 then 1
                                        else 2
                                   end