题解 | 统计各等级会员用户下订单总额

WITH total AS(
    SELECT 
        order_tb.*,
        uservip_tb.vip
    FROM order_tb
    LEFT JOIN uservip_tb
    ON order_tb.user_id=uservip_tb.user_id
),
points AS(
    SELECT vip,sum(order_price) AS order_total
    FROM total 
    GROUP BY vip
    ORDER BY sum(order_price) DESC
),
vvip AS(
    SELECT distinct vip
    FROM uservip_tb
)
SELECT 
    vvip.vip,
    coalesce(points.order_total,0) AS order_total
FROM vvip
LEFT JOIN points
ON vvip.vip=points.vip
ORDER BY coalesce(points.order_total,0) DESC;

为了提取用户完整的vip信息而构造vvip表