create table test ( 品名 varchar(10), 日期 datetime, 数量 int )insert into test values('aaa','19990101',2) insert into test values('bbb','20000101',3) insert into test values('ccc','19980101',4) insert into test values('ddd','19970101',5) insert into test values('eee','19960101',6) insert into test values('fff','19950101',1)select a.品名,a.日期,a.数量,(select sum(数量) from test b where b.日期<=a.日期) as 累计数量 from test adrop table test
--楼上忘了按品名了,借用楼上的数据测试我的语句create table test(品名 varchar(10),日期 datetime,数量 int) insert into test values('aaa','19990101',2) insert into test values('bbb','20000101',3) insert into test values('ccc','19980101',4) insert into test values('aaa','19970101',5) insert into test values('eee','19960101',6) insert into test values('bbb','19950101',1)select 品名,日期,数量,数量累计=( select sum(数量) from test where 品名=aa.品名 and 日期<=aa.日期) from test aa order by 品名,日期drop table test/*--测试结果 品名 日期 数量 数量累计 ---------- ---------------------------- ----------- --- aaa 1997-01-01 00:00:00.000 5 5 aaa 1999-01-01 00:00:00.000 2 7 bbb 1995-01-01 00:00:00.000 1 1 bbb 2000-01-01 00:00:00.000 3 4 ccc 1998-01-01 00:00:00.000 4 4 eee 1996-01-01 00:00:00.000 6 6(所影响的行数为 6 行) --*/
(
品名 varchar(10),
日期 datetime,
数量 int
)insert into test values('aaa','19990101',2)
insert into test values('bbb','20000101',3)
insert into test values('ccc','19980101',4)
insert into test values('ddd','19970101',5)
insert into test values('eee','19960101',6)
insert into test values('fff','19950101',1)select a.品名,a.日期,a.数量,(select sum(数量) from test b where b.日期<=a.日期) as 累计数量 from test adrop table test
insert into test values('aaa','19990101',2)
insert into test values('bbb','20000101',3)
insert into test values('ccc','19980101',4)
insert into test values('aaa','19970101',5)
insert into test values('eee','19960101',6)
insert into test values('bbb','19950101',1)select 品名,日期,数量,数量累计=(
select sum(数量) from test where 品名=aa.品名 and 日期<=aa.日期)
from test aa
order by 品名,日期drop table test/*--测试结果
品名 日期 数量 数量累计
---------- ---------------------------- ----------- ---
aaa 1997-01-01 00:00:00.000 5 5
aaa 1999-01-01 00:00:00.000 2 7
bbb 1995-01-01 00:00:00.000 1 1
bbb 2000-01-01 00:00:00.000 3 4
ccc 1998-01-01 00:00:00.000 4 4
eee 1996-01-01 00:00:00.000 6 6(所影响的行数为 6 行)
--*/