sql 数据累加

Question

cosnid  aa bb cc dd 
1   2   3  4  5
2   3   4  5  6
3   4   5  6  7
我要追加成
1  2  3  4  5
3  5  7  9  11
6  9  12 15  18
 
上面是我要生成的例子代码如下！
USE [DelphiDB]
GO
/****** 对象:  Table [dbo].[T_Spare_Parts]    
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[T_Spare_Parts](
[SpareID] [int] IDENTITY(1,1) NOT NULL,
[SpareNO] [decimal](10, 0) NOT NULL,
[Spare_depict] [varchar](80) COLLATE Chinese_PRC_CI_AS NULL,
[Spare_storage] [varchar](30) COLLATE Chinese_PRC_CI_AS NULL,
[Spare_buy_Scalar] [int] NULL,
[Spare_stock_Scalar] [int] NULL,
[Spare_price] [money] NULL,
[Daily_Consumption] [decimal](10, 1) NULL,
[Procurement_Leadtime] [int] NULL,
[Currentdate] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
 CONSTRAINT [PK_T_ATT] PRIMARY KEY CLUSTERED 
(
[SpareID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]GO
SET ANSI_PADDING OFFdeclare @count decimal(10,3)
DECLARE @scale decimal(10,1)
declare @add decimal(10,2)
select @count=sum(Daily_Consumption*Spare_price) from T_Spare_Parts where datepart(yy,Currentdate)=2008 
select
  备件号 = d.SpareNO,
           备件描述 = d.Spare_depict,
  所属仓库 = d.Spare_storage,
  重订数量=d.Spare_buy_Scalar,
  库存量=d.Spare_stock_Scalar,
  最新价=d.Spare_price,
  总用量=sum(d.Daily_Consumption),
  总价=cast((sum(d.Daily_Consumption)*d.Spare_price) as decimal(10,2)),
  比例=cast((sum(d.Daily_Consumption)*d.Spare_price)/@count*100 as decimal(10,2)),
--  累加比例= case when  select 
     -- @add=sum((d.Daily_Consumption)*d.Spare_price)/@count*100,

  (select sum(s.Daily_Consumption) from  dbo.T_Spare_Parts as s where datepart(MM,s.Currentdate)=1 and s.SpareNO=d.SpareNO) as '1',
  (select sum(s.Daily_Consumption) from  dbo.T_Spare_Parts as s where datepart(MM,s.Currentdate)=2 and s.SpareNO=d.SpareNO) as '2',
  (select sum(s.Daily_Consumption) from  dbo.T_Spare_Parts as s where datepart(MM,s.Currentdate)=3 and s.SpareNO=d.SpareNO) as '3',
  (select sum(s.Daily_Consumption) from  dbo.T_Spare_Parts as s where datepart(MM,s.Currentdate)=4 and s.SpareNO=d.SpareNO) as '4',
  (select sum(s.Daily_Consumption) from  dbo.T_Spare_Parts as s where datepart(MM,s.Currentdate)=5 and s.SpareNO=d.SpareNO) as '5',
  (select sum(s.Daily_Consumption) from  dbo.T_Spare_Parts as s where datepart(MM,s.Currentdate)=6 and s.SpareNO=d.SpareNO) as '6',
  (select sum(s.Daily_Consumption) from  dbo.T_Spare_Parts as s where datepart(MM,s.Currentdate)=7 and s.SpareNO=d.SpareNO) as '7',
  (select sum(s.Daily_Consumption) from  dbo.T_Spare_Parts as s where datepart(MM,s.Currentdate)=8 and s.SpareNO=d.SpareNO) as '8',
  (select sum(s.Daily_Consumption) from  dbo.T_Spare_Parts as s where datepart(MM,s.Currentdate)=9 and s.SpareNO=d.SpareNO) as '9',
  (select sum(s.Daily_Consumption) from  dbo.T_Spare_Parts as s where datepart(MM,s.Currentdate)=10 and s.SpareNO=d.SpareNO) as '10',
  (select sum(s.Daily_Consumption) from  dbo.T_Spare_Parts as s where datepart(MM,s.Currentdate)=11 and s.SpareNO=d.SpareNO) as '11',
  (select sum(s.Daily_Consumption) from  dbo.T_Spare_Parts as s where datepart(MM,s.Currentdate)=12 and s.SpareNO=d.SpareNO) as '12'    from 
T_Spare_Parts as d
-- on b.Months=datepart(MM,Currentdate)
where datepart(yy,Currentdate)=2008
    group by d.SpareNO,d.Spare_depict,d.Spare_storage,d.Spare_buy_Scalar,d.Spare_stock_Scalar,d.Spare_price
--having  sum(a.Daily_Consumption)*a.Spare_price>sum(b.Daily_Consumption)*b.Spare_price
order by 比例 descprint @add

Answer 1

declare @table table (cosnid int,aa int,bb int,cc int,dd int)
insert into @table
select 1,2,3,4,5 union all
select 2,3,4,5,6 union all
select 3,4,5,6,7select cosnid,
(select sum(aa) from @table where cosnid<=t.cosnid) as aa,
(select sum(bb) from @table where cosnid<=t.cosnid) as bb,
(select sum(cc) from @table where cosnid<=t.cosnid) as cc,
(select sum(dd) from @table where cosnid<=t.cosnid) as dd
 from @table t
/*
cosnid      aa          bb          cc          dd
----------- ----------- ----------- ----------- -----------
1           2           3           4           5
2           5           7           9           11
3           9           12          15          18
*/

Answer 2

aa bb cc dd 有4列，算哪列的比例？还是都按个数算呀？

Answer 3

declare @table table (cosnid int,aa int,bb int,cc int,dd int)
insert into @table
select 1,2,3,4,5 union all
select 2,3,4,5,6 union all
select 3,4,5,6,7select cosnid,
(select sum(aa) from @table where cosnid<=t.cosnid) as aa,
(select sum(bb) from @table where cosnid<=t.cosnid) as bb,
(select sum(cc) from @table where cosnid<=t.cosnid) as cc,
(select sum(dd) from @table where cosnid<=t.cosnid) as dd,
cast(100*cast((select count(*) from @table where cosnid<=t.cosnid) as decimal(16,4))/
(select count(*) from @table) as decimal(16,3)) as 累计比例
 from @table t
/*
cosnid      aa          bb          cc          dd          累计比例
----------- ----------- ----------- ----------- ----------- ---------------------------------------
1           2           3           4           5           33.333
2           5           7           9           11          66.667
3           9           12          15          18          100.000
*/
这样吧？

Answer 4

Currentdate  这个字段是填日期类型

Answer 5

9 12642 gsergsfdgd aaaa 39 65 2130.8800 8.0 60 2008-09-02
10 12642 gsergsfdgd aaaa 39 65 2130.8800 6.0 60 2008-10-02
11 12642 gsergsfdgd aaaa 39 65 2130.8800 8.0 60 2008-11-02
12 13772 ddddddd bbbb 1 2 58729.5000 1.0 60 2008-03-02
13 13772 ddddddd bbbb 1 2 58729.5000 1.0 60 2008-04-02
14 13772 ddddddd bbbb 1 2 58729.5000 1.0 60 2008-08-02
15 14996 wwwwww ccccc 1 3 16453.8500 2.0 60 2008-02-02
16 14996 wwwwww ccccc 1 3 16453.8500 1.0 60 2008-03-02
17 14996 wwwwww ccccc 1 3 16453.8500 1.0 60 2008-07-02
18 14996 wwwwww ccccc 1 3 16453.8500 1.0 60 2008-08-02
这是数据！
 这段代码是比例 : 比例=cast((sum(d.Daily_Consumption)*d.Spare_price)/@count*100 as decimal(10,2)),
我要在这里累加比例 -- 累加比例= 把上面的比例累加起来！

Answer 6

SQL SERVER 2000 ? SQL SERVER 2005? 2008?

Answer 7

因为你的比例不是某列，是你后组合出来的列，需要嵌套一下。
如果是SQL SERVER 2000 就用临时表
如果是SQL SERVER 2005+就是with 表达式

Answer 8

--创建表
CREATE TABLE [dbo].[T_Spare_Parts](
    [SpareID] [int] IDENTITY(1,1) NOT NULL,
    [SpareNO] [decimal](10, 0) NOT NULL,
    [Spare_depict] [varchar](80) COLLATE Chinese_PRC_CI_AS NULL,
    [Spare_storage] [varchar](30) COLLATE Chinese_PRC_CI_AS NULL,
    [Spare_buy_Scalar] [int] NULL,
    [Spare_stock_Scalar] [int] NULL,
    [Spare_price] [money] NULL,
    [Daily_Consumption] [decimal](10, 1) NULL,
    [Procurement_Leadtime] [int] NULL,
    [Currentdate] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
 CONSTRAINT [PK_T_ATT] PRIMARY KEY CLUSTERED 
(
    [SpareID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]GO
SET ANSI_PADDING OFF--插入数据
insert into [T_Spare_Parts]
select 12642,'gsergsfdgd','aaaa',39,65,2130.8800,8.0,60,'2008-09-02' union all
select 12642,'gsergsfdgd','aaaa',39,65,2130.8800,6.0,60,'2008-10-02' union all
select 12642,'gsergsfdgd','aaaa',39,65,2130.8800,8.0,60,'2008-11-02' union all
select 13772,'ddddddd','bbbb',1,2,58729.5000,1.0,60,'2008-03-02' union all
select 13772,'ddddddd','bbbb',1,2,58729.5000,1.0,60,'2008-04-02' union all
select 13772,'ddddddd','bbbb',1,2,58729.5000,1.0,60,'2008-08-02' union all
select 14996,'wwwwww','ccccc',1,3,16453.8500,2.0,60,'2008-02-02' union all
select 14996,'wwwwww','ccccc',1,3,16453.8500,1.0,60,'2008-03-02' union all
select 14996,'wwwwww','ccccc',1,3,16453.8500,1.0,60,'2008-07-02' union all
select 14996,'wwwwww','ccccc',1,3,16453.8500,1.0,60,'2008-08-02'
godeclare @count decimal(10,3)
DECLARE @scale decimal(10,1)
declare @add decimal(10,2)
select @count=sum(Daily_Consumption*Spare_price) from T_Spare_Parts where datepart(yy,Currentdate)=2008 
select
      备件号 = d.SpareNO,
           备件描述 = d.Spare_depict,
      所属仓库 = d.Spare_storage,
      重订数量=d.Spare_buy_Scalar,
      库存量=d.Spare_stock_Scalar,
      最新价=d.Spare_price,
      总用量=sum(d.Daily_Consumption),
      总价=cast((sum(d.Daily_Consumption)*d.Spare_price) as decimal(10,2)),
      比例=cast((sum(d.Daily_Consumption)*d.Spare_price)/@count*100 as decimal(10,2)),
    --  累加比例= case when  select 
     -- @add=sum((d.Daily_Consumption)*d.Spare_price)/@count*100,
        
      (select sum(s.Daily_Consumption) from  dbo.T_Spare_Parts as s where datepart(MM,s.Currentdate)=1 and s.SpareNO=d.SpareNO) as '1',
      (select sum(s.Daily_Consumption) from  dbo.T_Spare_Parts as s where datepart(MM,s.Currentdate)=2 and s.SpareNO=d.SpareNO) as '2',
      (select sum(s.Daily_Consumption) from  dbo.T_Spare_Parts as s where datepart(MM,s.Currentdate)=3 and s.SpareNO=d.SpareNO) as '3',
      (select sum(s.Daily_Consumption) from  dbo.T_Spare_Parts as s where datepart(MM,s.Currentdate)=4 and s.SpareNO=d.SpareNO) as '4',
      (select sum(s.Daily_Consumption) from  dbo.T_Spare_Parts as s where datepart(MM,s.Currentdate)=5 and s.SpareNO=d.SpareNO) as '5',
      (select sum(s.Daily_Consumption) from  dbo.T_Spare_Parts as s where datepart(MM,s.Currentdate)=6 and s.SpareNO=d.SpareNO) as '6',
      (select sum(s.Daily_Consumption) from  dbo.T_Spare_Parts as s where datepart(MM,s.Currentdate)=7 and s.SpareNO=d.SpareNO) as '7',
      (select sum(s.Daily_Consumption) from  dbo.T_Spare_Parts as s where datepart(MM,s.Currentdate)=8 and s.SpareNO=d.SpareNO) as '8',
      (select sum(s.Daily_Consumption) from  dbo.T_Spare_Parts as s where datepart(MM,s.Currentdate)=9 and s.SpareNO=d.SpareNO) as '9',
      (select sum(s.Daily_Consumption) from  dbo.T_Spare_Parts as s where datepart(MM,s.Currentdate)=10 and s.SpareNO=d.SpareNO) as '10',
      (select sum(s.Daily_Consumption) from  dbo.T_Spare_Parts as s where datepart(MM,s.Currentdate)=11 and s.SpareNO=d.SpareNO) as '11',
      (select sum(s.Daily_Consumption) from  dbo.T_Spare_Parts as s where datepart(MM,s.Currentdate)=12 and s.SpareNO=d.SpareNO) as '12'
  into #t
    from 
    T_Spare_Parts as d
--    on b.Months=datepart(MM,Currentdate)
    where datepart(yy,Currentdate)=2008
    group by d.SpareNO,d.Spare_depict,d.Spare_storage,d.Spare_buy_Scalar,d.Spare_stock_Scalar,d.Spare_price
--having  sum(a.Daily_Consumption)*a.Spare_price>sum(b.Daily_Consumption)*b.Spare_price
    order by 比例 desc
--select * from #t
select *,累加比例=(select sum(比例) from #t where 比例>=t.比例) from #t t
drop table #t /*
备件号                                     备件描述                                                                             所属仓库                           重订数量        库存量         最新价                   总用量                                     总价                                      比例                                      1                                       2                                       3                                       4                                       5                                       6                                       7                                       8                                       9                                       10                                      11                                      12                                      累加比例
--------------------------------------- -------------------------------------------------------------------------------- ------------------------------ ----------- ----------- --------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
13772                                   ddddddd                                                                          bbbb                           1           2           58729.50              3.0                                     176188.50                               57.70                                   NULL                                    NULL                                    1.0                                     1.0                                     NULL                                    NULL                                    NULL                                    1.0                                     NULL                                    NULL                                    NULL                                    NULL                                    57.70
14996                                   wwwwww                                                                           ccccc                          1           3           16453.85              5.0                                     82269.25                                26.94                                   NULL                                    2.0                                     1.0                                     NULL                                    NULL                                    NULL                                    1.0                                     1.0                                     NULL                                    NULL                                    NULL                                    NULL                                    84.64
12642                                   gsergsfdgd                                                                       aaaa                           39          65          2130.88               22.0                                    46879.36                                15.35                                   NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    8.0                                     6.0                                     8.0                                     NULL                                    99.99
*/