如果一个人有多个消费记录呢

如果一个人有多个消费记录，您可以按照以下步骤进行更新：

1. 计算每个客户的总消费额。

您可以使用以下 SQL 语句计算每个客户的总消费额：

SELECT customer_id, SUM(original_price) AS total_amount
FROM consumption_records_table
GROUP BY customer_id;

2. 更新卡种字段。

您可以使用以下 SQL 语句更新卡种字段：

UPDATE customer_table c
SET c.card_type = CASE 
    WHEN EXISTS (
        SELECT 1 FROM (
            SELECT customer_id, SUM(original_price) AS total_amount
            FROM consumption_records_table
            GROUP BY customer_id
        ) t 
        WHERE t.customer_id = c.customer_id AND t.total_amount >= 10000
    ) THEN '金卡'
    WHEN EXISTS (
        SELECT 1 FROM (
            SELECT customer_id, SUM(original_price) AS total_amount
            FROM consumption_records_table
            GROUP BY customer_id
        ) t 
        WHERE t.customer_id = c.customer_id AND t.total_amount >= 5000 AND t.total_amount < 10000
    ) THEN '金卡'
    ELSE '普通卡'
    END;

3. 更新折扣价字段。

您可以使用以下 SQL 语句更新折扣价字段：

UPDATE consumption_records_table r
SET r.discount_price = CASE 
    WHEN r.customer_id IN (
        SELECT customer_id FROM customer_table WHERE card_type = '金卡'
    ) THEN r.original_price * CASE 
        WHEN (
            SELECT SUM(original_price) 
            FROM consumption_records_table 
            WHERE customer_id = r.customer_id
        ) >= 10000 THEN 0.7
        WHEN (
            SELECT SUM(original_price) 
            FROM consumption_records_table 
            WHERE customer_id = r.customer_id
        ) >= 5000 THEN 0.8
        ELSE 1
    END
    ELSE r.original_price
    END;

在更新折扣价字段时，您需要使用子查询计算每个客户的总消费额，并根据总消费额计算实际消费额。