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

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

...全文
187 1 收藏 5

5 条回复

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

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

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;

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

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

2.1w+

MS-SQL Server 疑难问题