大神！这种统计SQL ，怎么写啊。

[shuzhan789]

现在的表结果：
Id Num OrderIndex
A1 3 1
A2 2 2
A3 3 3
B1 1 4
B2 4 5

想实现的结果：
Id Num
A1 3
A2 5
A3 8
B1 9
B2 13

统计后的结果：
根据OrderIndex排序，
每一行的Num的值为 小于等于当前行的OrderIndex的所有Num汇总。

[等不到来世]

select Id,(select SUM(Num) from tb where OrderIndex<=t.OrderIndex) AS Num
from tb as t

• 打赏
• 举报

回复 赞

[阳泉酒家小当家]

select Id,SUM(Num) as  Num
from tb t1
inner join tb t2
on t2.OrderIndex<=t1.OrderIndex
group by id

• 打赏
• 举报

回复 赞

[湖中仙人]

create table #tb (ID nvarchar(5), Num int, OrderIndex int ) insert into #tb select 'A1',3,1 union all select 'A2',2,2 union all select 'A3',3,3 union all select 'B1',1,4 union all select 'B2',4,5 --select * from #tb; with cs1 as ( select ID,Num,OrderIndex from #tb where OrderIndex=1 union all select tt.ID,(case when tt.OrderIndex=1 then tt.Num else tt.Num+cs1.Num end) as Num,tt.OrderIndex+1 as OrderIndex from #tb tt inner join cs1 on tt.OrderIndex=cs1.OrderIndex ) select ID,Num from cs1 where OrderIndex<>1;

• 打赏
• 举报

回复 赞

[许晨旭]

;WITH CTE AS(
	SELECT 'A1' AS Id,3 as Num,1 AS OrderIndex UNION ALL
	SELECT 'A2' AS Id,2 as Num,2 AS OrderIndex UNION ALL
	SELECT 'A3' AS Id,3 as Num,3 AS OrderIndex UNION ALL
	SELECT 'B1' AS Id,1 as Num,4 AS OrderIndex UNION ALL
	SELECT 'B2' AS Id,4 as Num,5 AS OrderIndex
)
SELECT A.Id,SUM(B.Num) AS NUM
FROM CTE a
JOIN CTE b ON b.Id<=a.Id
GROUP BY A.Id

• 打赏
• 举报

回复 赞

[shadowpj]

2楼正解 select id, isnull((select sum(num) from a where a.orderindex<=tab.orderindex),0) as num from a tab order by orderindex

• 打赏
• 举报

回复 赞