求解：在SQL中实现累加的过程

Question

序号 货品编码    货品信息                              规格                      计划采购总长度(mm)
1 100104000322 热轧圆钢                       Φ65/42CrMo                    39420 1852.5 4260.75
1.1 1501010000171 G4260-1  内接头                Φ65/42CrMo                    28051 1852.5 4260.75
1.2 1501010000261 G4260-5  外接头                Φ65/42CrMo                    11369
2 100104000308 热轧圆钢                       Φ35/42CrMo                    8494
2.1 1501010000181 G4260-2  套                    Φ35/42CrMo                    8494
3 100104000309 热轧圆钢                       Φ36/42CrMo                    1231
3.1 1501010000191 G4260-2A  套                   Φ36/42CrMo                    5621
4 100114010427 无缝钢管                       Φ28×4.5/20                   1234
4.1 1501010000201 G4260-3  接管                  Φ28×4.5/20                   7894
5 100114030005 无缝钢管                       Φ60.3×7.5/R780               114071
5.1 1501010000241 G4260-4  外管                  Φ60.3×7.5/R780               114071
6 100104000303 热轧圆钢                       Φ30/42CrMo                    2114
6.1 1501010000271 G4260-6  接头2                 Φ30/42CrMo                    1567
6.2 1501010000281 G4260-7  接头1                 Φ30/42CrMo                    1253
--------------------------------------------------------------------------------------------------------------
这是我的数据表我想把相同规格的计划采购总长度进行累加，如何实现？谢谢解答

Answer 1

--序号 货品编码 货品信息 规格 计划采购总长度(mm) select *,累加值=(select sum([计划采购总长度(mm)]) from tb where 规格=t.规格 and 序号<=t.序号) from tb t?

Answer 2

http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html

Answer 3

 规格         计划采购总长度(mm) 
Φ65/42CrMo      39420 
Φ65/42CrMo      28051 
Φ65/42CrMo      11369 
Φ35/42CrMo       8494 
Φ35/42CrMo       8494 
Φ36/42CrMo       1231 
Φ36/42CrMo       5621 
Φ28×4.5/20       1234 
Φ28×4.5/20       7894 
刚刚描述的不够清晰，就拿这个简单的表吧我想让规格相同的计划采购总长度累加，结果如下：
规格         计划采购总长度(mm) 
Φ65/42CrMo      78840
Φ35/42CrMo      16988
Φ36/42CrMo       6852
Φ28×4.5/20      9128

Answer 4

/*------------------------------------------------------------------
--  Author : htl258(Tony)
--  Date   : 2010-04-18 20:18:46
--  Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
Jul  9 2008 14:43:34 
Copyright (c) 1988-2008 Microsoft Corporation
Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)------------------------------------------------------------------*/
--> 生成测试数据表:tbIF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([规格] NVARCHAR(10),[计划采购总长度(mm)] INT)
INSERT [tb]
SELECT N'Φ65/42CrMo',39420 UNION ALL
SELECT N'Φ65/42CrMo',28051 UNION ALL
SELECT N'Φ65/42CrMo',11369 UNION ALL
SELECT N'Φ35/42CrMo',8494 UNION ALL
SELECT N'Φ35/42CrMo',8494 UNION ALL
SELECT N'Φ36/42CrMo',1231 UNION ALL
SELECT N'Φ36/42CrMo',5621 UNION ALL
SELECT N'Φ28×4.5/20',1234 UNION ALL
SELECT N'Φ28×4.5/20',7894
GO
--SELECT * FROM [tb]-->SQL查询如下:
select *,累加值=(select SUM([计划采购总长度(mm)]) from tb where 规格=t.规格) from tb t 
/*
规格         计划采购总长度(mm) 累加值
---------- ----------- -----------
Φ65/42CrMo 39420       78840
Φ65/42CrMo 28051       78840
Φ65/42CrMo 11369       78840
Φ35/42CrMo 8494        16988
Φ35/42CrMo 8494        16988
Φ36/42CrMo 1231        6852
Φ36/42CrMo 5621        6852
Φ28×4.5/20 1234        9128
Φ28×4.5/20 7894        9128(9 行受影响)
*/
select 规格,SUM([计划采购总长度(mm)]) 累加值 from tb group by 规格
/*
规格         累加值
---------- -----------
Φ28×4.5/20 9128
Φ35/42CrMo 16988
Φ36/42CrMo 6852
Φ65/42CrMo 78840(4 行受影响)*/

Answer 5

SELECT 
  规格,
  SUM([计划采购总长度(mm)]) AS [计划采购总长度(mm)]  
FROM TB
GROUP BY 规格

Answer 6

select 规格,sum([计划采购总长度(mm)]) as  计划采购总长度 from tb group by 规格