这是我的查询，我得到前15个结果，并且一切都很好，但是在前15个之下有很多行，我需要收集这些结果，将它们计算在内，并在同一查询中命名为“其他”。这是我得到和查询的结果：
SELECT `Person` , COUNT( `Person` ) AS Total
FROM taxes
WHERE `Person` IS NOT NULL
AND `Person` <> ''
AND `Date`
BETWEEN '2013-11-11'
AND '2013-11-30'
GROUP BY (
`Person`
)
ORDER BY `Total` DESC
LIMIT 5
结果
Person - Total
a ------ 66
b ------ 44
c ------ 33
d ------ 13
e ------ 14
-----------
f ------ 10
g ------ 8
h ------ 7
这就是我想要得到的：
Person | Total
a ------ 66
b ------ 44
c ------ 33
d ------ 13
e ------ 14
-----------
others --- 25
谢谢！
使用并集，限制和偏移
SQLFiddle：http ://sqlfiddle.com/#!2/69e65/1
(SELECT `Person` ,
COUNT(`Person`) AS `Total`
FROM taxes
WHERE `Person` IS NOT NULL
AND `Person` <> ''
AND `Date` BETWEEN '2013-11-11' AND '2015-11-30'
GROUP BY `Person`
ORDER BY COUNT(`Person`) DESC LIMIT 2)
UNION
(SELECT 'Other',
sum(`Total`) AS `Total`
FROM
( SELECT `Person` ,
COUNT(`Person`) AS Total
FROM taxes
WHERE `Person` IS NOT NULL
AND `Person` <> ''
AND `Date` BETWEEN '2013-11-11' AND '2015-11-30'
GROUP BY `Person`
ORDER BY COUNT(`Person`) DESC
LIMIT 2, 18446744073709551615) AS TEMP
);
使用@row变量
SQLFiddle：http ://sqlfiddle.com/#!2/0a61a/5
set @row:=1;
select if(@row>2, 'Other', `Person`) as `Person`,
sum(`Total`) as `Total`,
@row:=@row+1 as `IgnoreColumn`
from
(
SELECT `Person` ,
COUNT(`Person`) AS Total
FROM taxes
WHERE `Person` IS NOT NULL
AND `Person` <> ''
AND `Date` BETWEEN '2013-11-11' AND '2015-11-30'
GROUP BY `Person`
ORDER BY COUNT(`Person`) DESC
) as TEMP
group by if(@row>2, 'Other', `Person`)
order by @row;
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句