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

一尘不染

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

sql

我对编写SQL比较陌生。我有一个要求，我必须按原样显示前5个记录，并将其余的合并为1个单个记录，并将其追加为第6个记录。我知道前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

阅读 149

2021-05-30

共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;

2021-05-30