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

# 筛选出订单表中积分消费金额大于100的订单
with t1 as(
        select user_id
            ,sum(order_price) as all_score
            from order_tb
        where order_price >100
        group by user_id),

# 筛选出会员表中有消费订单的会员
t2 as(
select user_id
      ,point
    from uservip_tb
where user_id in (select user_id
            from order_tb
        where order_price >100
        group by user_id))

# 将两个表连在一起，然后积分相加得出结果
select user_id
      ,all_score + point as point
    from(
        select t1.user_id as user_id
            ,all_score
            ,point
            from t1 join t2
                on t1.user_id=t2.user_id) as t3
order by point desc