大神!这种统计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汇总。
...全文
249 5 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
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;
LongRui888 2015-11-30
  • 打赏
  • 举报
回复
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

22,301

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧