如何使n行合并为1行，并显示其他行不变

I have this table

我有这个表

Col1        Col2
--------------------
1           100
2           50
3           60
4           70
5           20

I am trying to add multiple rows into one row with total For example if input is 2, result is a row that has sum of row1 to row2,and row3 to end row.

我尝试在一行中添加多个行，例如，如果输入是2，结果是一行有row1和row2的和，以及row3到end行。

Here is what I want as results for my mock data:

下面是我想要的模拟数据结果:

Col1        Col2
--------------------
1           150(sum(row1 and row2)
2            60
3            70
4            20

2 个解决方案

#1

you can GROUP BY with CASE and then add the row numbers with RANK or ROW_NUMBER

您可以使用CASE分组，然后添加具有RANK或ROW_NUMBER的行号

SELECT 
  ROW_NUMBER() OVER (ORDER BY col1), 
  col2 
FROM 
  (
    SELECT 
      CASE WHEN col1 <= 2 THEN 0 ELSE col1 END col1, 
      SUM([Col2]) col2 
    FROM 
      some_table 
    GROUP BY 
      CASE WHEN col1 <= 2 THEN 0 ELSE col1 END
  ) a

#2

select min(col1), sum(col2) from table
where col1 is <= 2
UNION
select col1, col2 from table
where col1 is > 2

Note here though that col1 is not changed for rows 2 onwards.

请注意，尽管在第2行之前col1没有更改。

#1