题解 | 更新用户积分信息？

/*用SUM CASE WHEN*/

WITH
t1 AS (     -- 计算出近日的新增积分
SELECT
    user_id,
    SUM(CASE WHEN order_price > 100 THEN order_price ELSE 0 END) AS point       -- 只计算100以上金额订单
FROM order_tb AS a
GROUP BY user_id
)

SELECT      -- 主查询，计算往期+当期积分
    t1.user_id,
    t1.point + b.point AS point
FROM t1
INNER JOIN uservip_tb AS b
    ON (t1.user_id = b.user_id)
WHERE t1.point != 0     -- 去除近日有消费过，但是因为金额小于100而没有获得积分的用户信息
ORDER BY point DESC;