由（前五名）计数及计数集团（所有其他）(Group by Count of (Top 5) & Co

我有一个查询，我需要修改。 目前，它返回数据的2列的情况下，标签和前7天期限从昨日起在处理计数（或案例的总数）。 我需要这样只有6标签的输出改变这个输出（即 - 永远6行数据）。 这些行必须是前5名的标签，其余标签为公司第六届标签的总和（称为“其他”）。 这是因为该输出被送到呈现一个基于网络的平台上的数据的PHP脚本。

最后，在这里说明的是输出我需要以及下面的查询表。

+-----------+---------------+  
| CaseLabel | CasesResolved |  
+-----------+---------------+  
| Label1    |            20 |  
| Label2    |            18 |
| Label3    |            10 |
| Label4    |             9 |
| Label5    |             7 |
| Other     |            12 |
+-----------+---------------+

在此先感谢您的帮助！ :-)

运行MySQL 5.096

MySQL的代码：

SELECT
    deskcases.Labels,
    COUNT(deskcases.Labels)AS CaseCount
FROM
    deskcases
WHERE
    deskcases.Labels NOT LIKE ''
AND deskcases.Labels NOT LIKE '%SPAM%'
AND deskcases.Labels NOT LIKE '%Online Orders%'
AND deskcases.Labels NOT LIKE '%Internal SPAM%'
AND deskcases.`Case Status` LIKE 'Resolved'
AND deskcases.`Resolved At` > CURDATE()- INTERVAL 7 DAY
GROUP BY
    deskcases.Labels
ORDER BY
    CaseCount DESC

Answer 1:

在MySQL中，可能表达最简单的方法是使用临时表：

create temporary table temp as (
    id int not null auto_increment,
    CaseLabel varchar(255),
    CasesResolved int
);

insert into temp(CaseLabel, CasesResolved)
    SELECT deskcases.Labels, COUNT(deskcases.Labels)AS CaseCount
    FROM deskcases
    WHERE deskcases.Labels NOT LIKE ''
          AND deskcases.Labels NOT LIKE '%SPAM%'
          AND deskcases.Labels NOT LIKE '%Online Orders%'
          AND deskcases.Labels NOT LIKE '%Internal SPAM%'
          AND deskcases.`Case Status` LIKE 'Resolved'
          AND deskcases.`Resolved At` > CURDATE()- INTERVAL 7 DAY
    GROUP BY deskcases.Labels
    ORDER BY CaseCount DESC;

select (case when id <= 5 then caselabel else 'Other' end),
       SUM(casesResolved) as CasesResolved
from temp
group by (case when id <= 5 then caselabel else 'Other' end)
order by MAX(id) desc

该id在临时表中的列添加行号到每一行。 在任何其他真正的数据库，你可以使用row_number()函数，但MySQL不支持这一点。

Answer 2:

一个可能的选择是使用一个等级的变量。

伪加盟将初始化等级和是否会数到6。
所有这些东西将首先得到内部查询解决，生产这样的东西

| Label1    |            20 |  
| Label2    |            18 |
| Label3    |            10 |
| Label4    |             9 |
| Label5    |             7 |
| Label6    |            12 |
| Label7    |               |
| ......

然后，其他查询会崩溃到所需的输出这一点。

select if(rank=6,"Other",sub.Labels) as Label, SUM(sub.CaseCount) from (
    SELECT
        if(@Rank < 6,@Rank:= @Rank + 1, @Rank) as Rank
        ,deskcases.Labels
        ,COUNT(deskcases.Labels) AS CaseCount
    FROM
        deskcases
    JOIN (@rank:= 0)
    WHERE
        deskcases.Labels NOT LIKE ''
    AND deskcases.Labels NOT LIKE '%SPAM%'
    AND deskcases.Labels NOT LIKE '%Online Orders%'
    AND deskcases.Labels NOT LIKE '%Internal SPAM%'
    AND deskcases.`Case Status` LIKE 'Resolved'
    AND deskcases.`Resolved At` > CURDATE()- INTERVAL 7 DAY
    GROUP BY
        deskcases.Labels
    ORDER BY
        CaseCount DESC
) sub
group by sub.rank ASC

更换labelx为“其他”的最后一行。

文章来源: Group by Count of (Top 5) & Count of (All Other)