我在mysql中有以下问题:
来自这样的查询:
select Category, count(*) as C
from categories_likes
group by Category
order by C desc
limit 3
我需要得到这样的结果:
Category | C
Love | 10
Sad | 5
Angry | 3
Other | 50
其中other是"other"行,是结果中不需要的其他类别中的计数的总和,但需要将所有计数分组到最后一行中。
希望有人能帮我。感谢
假设您希望将"爱"、"悲伤"one_answers"愤怒"之外的所有类别分组到一个名为"其他"的独特组中。
如果是,您可以使用case
表达式:
select
case
when category in ('Love', 'Sad', 'Angry') then category
else 'Other'
end new_category,
count(*) cnt
from categories_like
group by new_category
order by (new_category = 'Other'), cnt desc
请注意,查询的order by
子句将类别"Other"放在最后。
如果你想要一个动态的前3个类别,然后是所有其他类别的总数,那么它就有点不同了。假设MySQL 8.0,您可以使用排名功能:
select case when rn <= 3 then category else 'Other' end new_category, sum(cnt) cnt
from (
select category, count(*) cnt, rank() over(order by count(*) desc) rn
from categories_like
group by category
) t
group by new_category
order by (new_category = 'Other'), cnt desc
在早期版本中,我建议union all
:
(
select category new_category, count(*) cnt
from categories_like
group by category
order by cnt desc
limit 3
)
union all (
select 'Other', sum(cnt)
from (
select category, count(*) cnt
from categories_like
group by category
order by cnt desc
limit 4, 1000
) t
)
order by (new_category = 'Other'), cnt desc
这是假设类别不超过1000个;您可以根据需要增加子查询的偏移量。