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

WITH t1 AS (
    SELECT b.vip,
           a.order_price
    FROM order_tb a
    LEFT JOIN uservip_tb b
    ON a.user_id = b.user_id
),
t2 AS (
    SELECT vip
    FROM uservip_tb
    GROUP BY vip
),
t3 AS (
    SELECT vip,
           SUM(order_price) AS order_total
    FROM t1
    GROUP BY vip
),
t4 AS (
    SELECT t2.vip,
           IFNULL(t3.order_total,0) AS order_total
    FROM t2
    LEFT JOIN t3
    ON t2.vip = t3.vip
    ORDER BY order_total DESC
)SELECT * FROM t4;

# 这道题的关键在于需要先建立一个维度表！