题解 | SQL 168. 计算商城中2021年每月的GMV

WITH t1 AS (
    SELECT
        SUBSTR(event_time,1,7) AS month,
        total_amount
    FROM tb_order_overall
    WHERE status != 2 AND substr(event_time,1,4) = 2021
)
SELECT month,
       ROUND(sum(total_amount),0) AS GMV
       FROM t1
GROUP BY month
HAVING GMV > 100000
ORDER BY GMV asc;

# 这道题关键在于substr，或者用date_format(dt,%Y%m)，date函数精确到日。