我想做以下工作 LIMIT 10
查询:
SELECT item, count(*)
FROM table
GROUP BY item
ORDER BY count(*) DESC
LIMIT 10
然后将所有的值都压入“other”行(在上面的例子中是第11行)。有没有可能在一个查询中不使用连接或其他方法来实现这一点?我现在得到的是:
SELECT IFNULL(t2.item, 'Other'), COUNT(*)
FROM table
LEFT OUTER JOIN (SELECT item
FROM table
GROUP BY item
ORDER BY count(*) DESC
LIMIT 10) t2 ON (item)
GROUP BY t2.item
2条答案
按热度按时间qhhrdooz1#
使用窗口函数和两级聚合:
qvtsj1bj2#
我怀疑这会比戈登的回答更有效,但这里有一个黑客的方式使用
CTE
```with a as
(select item, count(*) as cnt
from t
group by item
order by cnt desc limit 0,10), --top ten rows
b as
(select item, count(*) as cnt
from t
group by item
order by cnt desc limit 10,18446744073709551615) --all but top 10 rows
select item, cnt
from a
union all
select 'other', sum(cnt)
from b;