mysql - 如何在 SQL 中合并排序结果中的拖尾字段？

Question

如果执行以下命令，我们可以看到有 6 个结果。

> select competition.region, count(competition.score) as cnt from competition group by competition.region order by cnt desc;

## --Result--
CN      80
USA     60
SE      40
US      10
JP      10
KR      8

但我想要的是将第 4 行到末尾的字段合并到一个名为“其他”的字段中:

## --Result--
CN      80
USA     60
SE      40
OTHER   28

有什么方法可以在单个 SQL 中实现吗？谢谢!

Answer 1

使用派生表将其他表合并在一起:

select region, count(*) as cnt
(select case when competition.region in ('CN', 'USA', 'SE') then competition.region
             else 'OTHER'
        end as region 
 from competition) as dt
group by region
order by cnt desc

编辑:

分别返回 3 个最常见的区域。其余为 OTHER。

select region, count(*) as cnt
(
 select case when region in (select region from competition
                             group by region
                             order by count(*) desc
                             limit 3) then region
             else 'OTHER'
        end as region
) as dt
group by region
order by cnt desc

请注意，如果是并列第 3 名，则只会返回一个地区。