这样的SQL怎么写？执行效率越高越好。

[好记忆不如烂笔头abc]

表test,有如下字段
[id]数值型自增1主键
[feilei] 字符型,
[heji] 数值型
记录如下：
1,a,10
2,a,10
3,a,20
5,b,10
6,b,10
7,b,10
8,c,5
9,c,10
10,d,5
10,e,50
11,f,100
12,f,10
13,g,20
14,g,60
15,h,200
15,h,100
.....可能有更多记录

希望得到的结果是，取出TOP3的按feilei字段的sum(heji),其它的归为other类别。
要求得到如下结果：
h,300
f,110
g,80
other,140

要求一条SQL语句得出结果，执行效率越高越好，请高手出招，十分感谢！

[Vidor]

--> 测试数据：#test
if object_id('tempdb.dbo.#test') is not null drop table #test
create table #test(id int, feilei varchar(8), heji int)
insert into #test
select 1, 'a', 10 union all
select 2, 'a', 10 union all
select 3, 'a', 20 union all
select 5, 'b', 10 union all
select 6, 'b', 10 union all
select 7, 'b', 10 union all
select 8, 'c', 5 union all
select 9, 'c', 10 union all
select 10, 'd', 5 union all
select 10, 'e', 50 union all
select 11, 'f', 100 union all
select 12, 'f', 10 union all
select 13, 'g', 20 union all
select 14, 'g', 60 union all
select 15, 'h', 200 union all
select 15, 'h', 100

;with cte as
(
	select topid=row_number()over(order by sum(heji) desc), feilei, sum(heji)heji from #test group by feilei
)
select
	case when topid<=3 then feilei else 'other' end feilei,
	sum(heji)heji
from cte group by
	case when topid<=3 then feilei else 'other' end

/*
feilei   heji
-------- -----------
f        110
g        80
h        300
other    140

(4 行受影响)

*/

[好记忆不如烂笔头abc]

前面还得要;with cte as这样？

[好记忆不如烂笔头abc]

我的SQLSERVER2000里执行，报错如下

服务器: 消息 156，级别 15，状态 1，行 21
在关键字 'with' 附近有语法错误。
服务器: 消息 195，级别 15，状态 1，行 23
'row_number' 不是可以识别的 函数名。

[htl258_Tony]

--> 生成测试数据表: [tb]
IF OBJECT_ID('tb') IS NOT NULL
    DROP TABLE tb
GO
CREATE TABLE tb
(
	id      INT, 
	feilei  VARCHAR(8), 
	heji    INT
)
INSERT INTO tb
SELECT 1, 'a', 10 UNION ALL
SELECT 2, 'a', 10 UNION ALL
SELECT 3, 'a', 20 UNION ALL
SELECT 5, 'b', 10 UNION ALL
SELECT 6, 'b', 10 UNION ALL
SELECT 7, 'b', 10 UNION ALL
SELECT 8, 'c', 5 UNION ALL
SELECT 9, 'c', 10 UNION ALL
SELECT 10, 'd', 5 UNION ALL
SELECT 10, 'e', 50 UNION ALL
SELECT 11, 'f', 100 UNION ALL
SELECT 12, 'f', 10 UNION ALL
SELECT 13, 'g', 20 UNION ALL
SELECT 14, 'g', 60 UNION ALL
SELECT 15, 'h', 200 UNION ALL
SELECT 15, 'h', 100

--SELECT * FROM [tb]

-->SQL查询如下:
SELECT rn=IDENTITY(INT),feilei,SUM(heji) heji INTO # FROM tb GROUP BY feilei ORDER BY heji DESC 
SELECT CASE WHEN rn<=3 THEN feilei ELSE 'other' END AS fenlei,SUM(heji) heji
FROM # 
GROUP BY CASE WHEN rn<=3 THEN feilei ELSE 'other' END
DROP TABLE #
/*
fenlei   heji
-------- -----------
f        110
g        80
h        300
other    140

(4 行受影响)
*/

量大的话临时表效率不会低

[snake99love]

嗯，学习了~~

[htl258_Tony]

--> 生成测试数据表: [tb]
IF OBJECT_ID('tb') IS NOT NULL
    DROP TABLE tb
GO
CREATE TABLE tb
(
	id      INT, 
	feilei  VARCHAR(8), 
	heji    INT
)
INSERT INTO tb
SELECT 1, 'a', 10 UNION ALL
SELECT 2, 'a', 10 UNION ALL
SELECT 3, 'a', 20 UNION ALL
SELECT 5, 'b', 10 UNION ALL
SELECT 6, 'b', 10 UNION ALL
SELECT 7, 'b', 10 UNION ALL
SELECT 8, 'c', 5 UNION ALL
SELECT 9, 'c', 10 UNION ALL
SELECT 10, 'd', 5 UNION ALL
SELECT 10, 'e', 50 UNION ALL
SELECT 11, 'f', 100 UNION ALL
SELECT 12, 'f', 10 UNION ALL
SELECT 13, 'g', 20 UNION ALL
SELECT 14, 'g', 60 UNION ALL
SELECT 15, 'h', 200 UNION ALL
SELECT 15, 'h', 100

--SELECT * FROM [tb]

-->SQL查询如下:
SELECT rn=IDENTITY(INT),feilei,SUM(heji) heji INTO # FROM tb GROUP BY feilei ORDER BY heji DESC 
SELECT CASE WHEN rn<=3 THEN feilei ELSE 'other' END AS fenlei,SUM(heji) heji
FROM # 
GROUP BY CASE WHEN rn<=3 THEN feilei ELSE 'other' END
ORDER BY CASE CASE WHEN rn<=3 THEN feilei ELSE 'other' END WHEN 'other' THEN 1 ELSE 0 END,heji DESC 
DROP TABLE #
/*
fenlei   heji
-------- -----------
h        300
f        110
g        80
other    140

(4 行受影响)

*/

[Vidor]

--> 测试数据：#test
if object_id('tempdb.dbo.#test') is not null drop table #test
create table #test(id int, feilei varchar(8), heji int)
insert into #test
select 1, 'a', 10 union all
select 2, 'a', 10 union all
select 3, 'a', 20 union all
select 5, 'b', 10 union all
select 6, 'b', 10 union all
select 7, 'b', 10 union all
select 8, 'c', 5 union all
select 9, 'c', 10 union all
select 10, 'd', 5 union all
select 10, 'e', 50 union all
select 11, 'f', 100 union all
select 12, 'f', 10 union all
select 13, 'g', 20 union all
select 14, 'g', 60 union all
select 15, 'h', 200 union all
select 15, 'h', 100

-- 临时表的话这样效率更好

select top 4 feilei, sum(heji)heji into # from #test group by feilei with rollup order by 2 desc

-- 只有4行，效率怎么也不会差:
select
	feilei = case when feilei is null then 'other' else feilei end,
	heji = case when feilei is null then heji - (select sum(heji) from # where feilei is not null) else heji end
from # order by 1

/*
feilei   heji
-------- -----------
f        110
g        80
h        300
other    140

(4 行受影响)
*/

[「已注销」]

SELECT * FROM 
(
	SELECT TOP 3 [feilei],SUM([heji])sumHeJi
	FROM #tb t
	GROUP BY [feilei]
	ORDER BY sumHeJi DESC
)M
UNION ALL
SELECT 'Other' AS [feilei],SUM(T.[heji])sumHeJi
FROM #tb t
WHERE T.feilei NOT IN (SELECT [feilei] FROM 
(
	SELECT TOP 3 [feilei],SUM([heji])sumHeJi
	FROM #tb t
	GROUP BY [feilei]
	ORDER BY sumHeJi DESC
)M
)

feilei     sumHeJi
---------- -----------
h          300
f          110
g          80
Other      140

(4 row(s) affected)

[好记忆不如烂笔头abc]

感谢各位，临时表的作用还真不小！