题解 | 三个union的暴力解法

with temp as (
    select
    '5-6级' as level_cut,
    count(*) as num
from
    answer_tb a left join author_tb u on a.author_id = u.author_id
where
    char_len >= 100
    and author_level in (5,6)
union
select
    '3-4级' as level_cut,
    count(*) as num
from
    answer_tb a left join author_tb u on a.author_id = u.author_id
where
    char_len >= 100
    and author_level in (3,4)
union
select
    '1-2级' as level_cut,
    count(*) as num
from
    answer_tb a left join author_tb u on a.author_id = u.author_id
where
    char_len >= 100
    and author_level in (1,2)
)

select
    *
from
    temp
where
    num != 0
order by
    num desc,level_cut asc