sql - 显示前 n 条记录并合并其余行

Question

我对编写 SQL 比较陌生。我有一个要求，我必须按原样显示前 5 条记录，并将其余记录合并为 1 条记录并将其附加为第 6 条记录。我知道 top 5 选择前 5 条记录，但我发现很难将逻辑放在一起以合并其余记录并将其附加到结果集的底部。

weekof          sales    year    weekno
-------------------------------------------------------------
07/01 - 07/07   2   2012    26  
07/08 - 07/14   2   2012    27  
07/29 - 08/04   1   2012    30  
08/05 - 08/11   1   2012    31  
08/12 - 08/18   32  2012    32  
08/26 - 09/01   2   2012    34  
09/02 - 09/08   8   2012    35  
09/09 - 09/15   46  2012    36   
09/16 - 09/22   26  2012    37

我希望这显示为

weekof          sales
----------------------
09/16 - 09/22   26  
09/09 - 09/15   46  
09/02 - 09/08   8   
08/26 - 09/01   2   
08/12 - 08/18   32  
07/01 - 08/11   6

Answer 1

除非 weekof跨越多年，这将获得您想要的数据并以正确的顺序:

;WITH x AS 
(
  SELECT weekof, sales, 
    rn = ROW_NUMBER() OVER (ORDER BY [year] DESC, weekno DESC) 
  FROM dbo.table_name
)
SELECT weekof, sales FROM x WHERE rn <= 5
UNION ALL
SELECT MIN(LEFT(weekof, 5)) + ' - ' + MAX(RIGHT(weekof, 5)), SUM(sales)
FROM x WHERE rn > 5    
ORDER BY weekof DESC;

当返回的行跨越一年时，您可能还必须返回 rn (并在表示层忽略它):

;WITH x AS 
(
  SELECT weekof, sales, 
    rn = ROW_NUMBER() OVER (ORDER BY [year] DESC, weekno DESC) 
  FROM dbo.table_name
)
SELECT weekof, sales, rn FROM x WHERE rn <= 5
UNION ALL
SELECT MIN(LEFT(weekof, 5)) + ' - ' + MAX(RIGHT(weekof, 5)), SUM(sales), rn = 6 
FROM x WHERE rn > 5
ORDER BY rn;