Group by Count of (前5个)和Count (所有其他)
Group by Count of (前5个)和Count (所有其他)
提问于 2013-03-21 02:34:11
我有一个查询需要编辑。目前，它返回2列数据，一个案例标签和从昨天开始的前7天内处理的计数(或总案例数)。我需要更改此输出，以便输出中只有6个标签(即始终为6行数据)。这些行需要是前5个标签，其余标签的总和是第6个标签(称为“其他”)。这是因为此输出被提供给PHP脚本，该脚本在基于web的平台上呈现数据。
最后，这里是我需要的输出表以及下面的查询。
+-----------+---------------+
| 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回答 2
Stack Overflow用户
发布于 2013-03-21 03:03:58
在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不支持该函数。
Stack Overflow用户
发布于 2013-03-21 02:42:25
一种可能的选择是使用一个排名变量。
伪连接将初始化秩，并且if将计数到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替换为"Other“。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/15531942
复制相关文章
相似问题
腾讯云开发者
