显示前 n 条记录并合并其余行 [英] Display top n records and consolidate the rest of the rows

问题描述

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

I am relatively new to writing SQL. I have a requirement where I have to display the top 5 records as it is and consolidate the rest as 1 single record and append it as the 6th record. I know top 5 selects the top 5 records, but I am finding it difficult to put together a logic to consolidate the rest of the records and append it at the bottom of the result set.

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

<小时>

我希望它显示为

---

I want this to be displayed as

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

推荐答案

除非 weekof 跨年，否则会以正确的顺序和获取您想要的数据:

Except when weekof spans years, this will get the data you want and in the correct order:

;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(并在表示层忽略它):

When the rows being returned span a year, you may have to return the rn as well (and just ignore it at the presentation layer):

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

这篇关于显示前 n 条记录并合并其余行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！