select aaa, bbb, ccc= bbb+ isnull((select max(bbb) from 表 a1 where a.term>a1.term),0) from 表 a
create table test1 ( aaa varchar(2), bbb int ) insert into test1(aaa,bbb) values('A',2) insert into test1(aaa,bbb) values('B',3) insert into test1(aaa,bbb) values('C',4) insert into test1(aaa,bbb) values('D',5) select aaa aaa,bbb bbb,(select sum(bbb) from test1 bb where bb.aaa<=aa.aaa) ccc from test1 aa
程序员的语句应改为: select aaa, bbb, ccc= bbb+ isnull((select max(bbb) from 表名 a1 where a.aaa>a1.aaa),0) from 表名 a
select *,(select sum(bbb) from table_name a where a.aaa <=tem.aaa) ccc from table_name tem
好象没一个对的,是不是大家误解我的意思了,我再把题目详说一次:有如下一表名table: AAA BBB f 10 h 5 a 20 t 15想求如下结果,如何写AAA BBB CCC f 10 10 h 5 15 a 20 35 t 15 50
create table test1 ( aaa varchar(2), bbb int ) insert into test1(aaa,bbb) values('A',2) insert into test1(aaa,bbb) values('B',3) insert into test1(aaa,bbb) values('C',4) insert into test1(aaa,bbb) values('D',5) select aaa aaa,bbb bbb,(select sum(bbb) from test1 bb where bb.aaa<=aa.aaa) ccc from test1 aa 你试了吗?
to dlpseeyou(豆子) : 假设我AAA列有100000行,那我是不是要写100000行呢
aaa,bbb你也想自动产生,对不对
ALTER TABLE tablename ADD id int identity(1,1) select aaa,bbb,(select sum(isnull(bbb,0)) from tablename a where a.aaa<=b.aaa) as ccc from tablename b alter table tablename DROP COLUMN id
/*假设表结构为*/ CREATE TABLE mytable ( [AAA] [varchar] (50) , [BBB] [decimal] (12,2) not null default 0 )/*插入测试数据*/ insert into mytable(AAA,BBB) values('a',10) insert into mytable(AAA,BBB) values('b',5) insert into mytable(AAA,BBB) values('c',20) insert into mytable(AAA,BBB) values('d',15)/*建立存储过程*/ CREATE PROCEDURE [dbo].[sp_sum_BBB] AS begin if exists (select * from TEMPDB.DBO.sysobjects where name = '##QUERY1') drop table ##query1 CREATE TABLE ##query1 ( [AAA] [varchar] (50) , [BBB] [decimal] (12,2) not null default 0 , [CCC] [decimal] (12,2) not null default 0 )insert into ##query1 select AAA,BBB,0 as CCC from mytable declare @val2 as decimal(12,2)select @val2= 0 UPDATE ##query1 set @val2=CCC=@val2 + BBBselect * from ##query1 if exists (select * from TEMPDB.DBO.sysobjects where name = '##QUERY1') drop table ##query1 end GO
事实上核心句子就一句话: UPDATE ##query1 set @val2=CCC=@val2 + BBB 只要知道这语句的含义,就OK了。
( aaa varchar(2),
bbb int
)
insert into test1(aaa,bbb) values('A',2)
insert into test1(aaa,bbb) values('B',3)
insert into test1(aaa,bbb) values('C',4)
insert into test1(aaa,bbb) values('D',5)
select aaa aaa,bbb bbb,(select sum(bbb) from test1 bb where bb.aaa<=aa.aaa) ccc from test1 aa
程序员的语句应改为:
select aaa, bbb, ccc= bbb+ isnull((select max(bbb) from 表名 a1 where a.aaa>a1.aaa),0) from 表名 a
AAA BBB
f 10
h 5
a 20
t 15想求如下结果,如何写AAA BBB CCC
f 10 10
h 5 15
a 20 35
t 15 50
( aaa varchar(2),
bbb int
)
insert into test1(aaa,bbb) values('A',2)
insert into test1(aaa,bbb) values('B',3)
insert into test1(aaa,bbb) values('C',4)
insert into test1(aaa,bbb) values('D',5)
select aaa aaa,bbb bbb,(select sum(bbb) from test1 bb where bb.aaa<=aa.aaa) ccc from test1 aa
你试了吗?
假设我AAA列有100000行,那我是不是要写100000行呢
select aaa,bbb,(select sum(isnull(bbb,0)) from tablename a where a.aaa<=b.aaa) as ccc from tablename b
alter table tablename DROP COLUMN id
CREATE TABLE mytable (
[AAA] [varchar] (50) ,
[BBB] [decimal] (12,2) not null default 0 )/*插入测试数据*/
insert into mytable(AAA,BBB) values('a',10)
insert into mytable(AAA,BBB) values('b',5)
insert into mytable(AAA,BBB) values('c',20)
insert into mytable(AAA,BBB) values('d',15)/*建立存储过程*/
CREATE PROCEDURE [dbo].[sp_sum_BBB]
AS
begin
if exists (select * from TEMPDB.DBO.sysobjects where name = '##QUERY1')
drop table ##query1 CREATE TABLE ##query1 (
[AAA] [varchar] (50) ,
[BBB] [decimal] (12,2) not null default 0 ,
[CCC] [decimal] (12,2) not null default 0
)insert into ##query1 select AAA,BBB,0 as CCC from mytable declare @val2 as decimal(12,2)select @val2= 0 UPDATE ##query1 set @val2=CCC=@val2 + BBBselect * from ##query1 if exists (select * from TEMPDB.DBO.sysobjects where name = '##QUERY1')
drop table ##query1
end
GO
UPDATE ##query1 set @val2=CCC=@val2 + BBB
只要知道这语句的含义,就OK了。