大神!这种统计SQL ,怎么写啊。

shuzhan789 2015-11-27 09:54:58
现在的表结果:
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汇总。
...全文
187 1 收藏 5
写回复
5 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
shadowpj 2015-12-04
2楼正解 select id, isnull((select sum(num) from a where a.orderindex<=tab.orderindex),0) as num from a tab order by orderindex
回复
许晨旭 2015-12-03
;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
回复
湖中仙人 2015-12-02
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
回复
等不到来世 2015-11-28
select Id,(select SUM(Num) from tb where OrderIndex<=t.OrderIndex) AS Num
from tb as t
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2015-11-27 09:54
社区公告
暂无公告