执行限制并将所有其他条目推入“other”

pbgvytdp 于 2021-07-29 发布在 Java

关注(0)|答案(2)|浏览(223)

我想做以下工作 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

sql mysql

来源：https://stackoverflow.com/questions/63124396/doing-a-limit-and-pushing-all-other-entries-into-other

2条答案

按热度按时间

qhhrdooz1#

使用窗口函数和两级聚合：

SELECT (CASE WHEN seqnum > 10 THEN 'Other' ELSE item END) as the_item,
       SUM(cnt) as cnt
FROM (SELECT item, COUNT(*) as cnt, ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) as seqnum
      FROM table t
      GROUP BY item
     ) t
GROUP BY the_item
ORDER BY MAX(seqnum) DESC;

赞(0）回复(0）举报 2021-07-29

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;

赞(0）回复(0）举报 2021-07-29