SQL Server 2005-选择前N个加上“其他”

未进行测试,但请尝试以下操作:

select * from (
    select top 5 
        columnB, columnA 
    from 
        someTable t
    order by
        columnA desc
    union all
    select 
        null, sum(columnA) 
    from 
        someTable t
    where primaryKey not in   (
        select top 5 
            primaryKey
        from 
            someTable t
        order by
            columnA desc
    )  
) a

select top 5 columnB, columnA
from someTable 
order by columnA desc

select SUM(columnA) as Total
from someTable

在客户端执行减法操作。

100%未经测试,我简直无法想象,但你可以试试这样的东西。如果我今晚有机会测试,我会更新帖子,但晚餐有一瓶葡萄酒,而且是周五晚上。.. :)

WITH CTE AS
     (
     SELECT
          ColumnB,
          ColumnA,
          ROW_NUMBER() OVER (ORDER BY ColumnB) AS RowNumber
     FROM
          dbo.SomeTable
     )
 SELECT
      CASE WHEN RowNumber <= 5 THEN ColumnB ELSE 'Other' END AS ColumnB,
      SUM(ColumnA) AS ColumnA
 FROM
      CTE
 GROUP BY
      CASE WHEN RowNumber <= 5 THEN ColumnB ELSE 'Other' END
 ORDER BY
      MIN(RowNumber)

编辑:看起来这在几个愚蠢的语法错误之后奏效了。我已经纠正了这些问题,所以现在它应该像上面列出的那样工作。虽然我不能谈论大数据集的性能,但值得一试。

这超出了我的想象,我会保证非常高效:

SELECT TOP 5 columnB, columnA
FROM comTable t
ORDER BY columnA desc

UNION

SELECT 'Other', (A.Sum - B.Sum) AS Summary
FROM (SELECT SUM(columnA) FROM someTable) A
JOIN (SELECT SUM(One) FROM 
    (SELECT TOP 5 columnA FROM someTable ORDER BY columnA DESC)) B

我最近经常使用EXCEPT语句:(未测试,但我尝试了一下)

select top 5 
    columnB, columnA 
from 
    someTable t
order by
    columnA desc
UNION ALL
SELECT 'OTHER' ColumnB, SUM(ColumnA)
FROM
(SELECT ColumnB, ColumnA 
FROM someTable t
EXCEPT
select top 5 
    columnB, columnA 
from 
    someTable t
order by
    columnA desc
) others