题解 | SQL 191. 某乎问答高质量的回答中用户属于各级别的数量

WITH t1 AS (
    SELECT *
    FROM answer_tb
    WHERE char_len >= 100
),
t2 AS (
    SELECT t1.author_id,
           a.author_level
    FROM t1
    LEFT JOIN author_tb a
    ON t1.author_id = a.author_id
),
t3 AS (
    SELECT author_level,
           count(author_id) AS num
    FROM t2
    GROUP BY author_level
),
t4 AS (
    SELECT '5-6级' AS level_cut,
           SUM(IF(author_level in (5,6),num, 0)) AS num
    FROM t3
    UNION ALL
    SELECT '3-4级' AS level_cut,
           SUM(IF(author_level in (3,4),num, 0)) AS num
    FROM t3
    UNION ALL
    SELECT '1-2级' AS level_cut,
           SUM(IF(author_level in (1,2),num, 0)) AS num
    FROM t3

)
SELECT * FROM t4
WHERE num != 0
ORDER BY num DESC,level_cut ASC;

# 细节在于排序是结果降序，序号升序！