Summary:我有按计数分组、计数和排序的数据.我只想保留前X行,并将其他行累积为名为"Other"的行
下面的数据是available at DB Fiddle
CREATE TABLE cities (name TEXT);
INSERT INTO cities(name) VALUES ('paris'), ('paris'), ( 'london'), ( 'london'), ( 'london'), ( 'london'), ( 'rome'), ( 'madrid');
SELECT name, COUNT(name) AS count FROM cities
GROUP BY name
ORDER BY count DESC
LIMIT 2
上面的代码生成前两个最常用的城市(条目数量最多的城市):
| name | count |
| ------ | ----- |
| london | 4 |
| paris | 2 |
我想要实现的是
| name | count |
| ------ | ----- |
| london | 4 |
| paris | 2 |
| other | 2 |
其中所有剩余的行(在我的例子中是'rome'
和'madrid'
)一起分组到other
下.
我对SQL不是很了解,但在伪代码中,我考虑的内容如下
SELECT name, COUNT(name) AS count FROM cities
GROUP BY name
ORDER BY count DESC
LIMIT 2 AS top_2
LIMIT 2+ AS other
这可能在一次通过中实现吗?作为一种变通办法,我将有相同的查询,但排序方式相反,并且限制为"行数-前2个"(所以我认为总共有3个查询).
(I am surprised that this has not been asked before, but could not find a match)个