表结构
时间 业绩
2006/10/1 1000
2006/10/2 1000
2006/10/3 1000
2006/10/4 1000
2006/10/5 1000要建个这样的临时表
时间 业绩 总计
2006/10/1 1000 1000
2006/10/2 1000 2000(这个月加上个月)
2006/10/3 1000 3000(这个月加上2个月)
2006/10/4 1000 4000
2006/10/5 1000 5000这个存储过程要怎么写?
时间 业绩
2006/10/1 1000
2006/10/2 1000
2006/10/3 1000
2006/10/4 1000
2006/10/5 1000要建个这样的临时表
时间 业绩 总计
2006/10/1 1000 1000
2006/10/2 1000 2000(这个月加上个月)
2006/10/3 1000 3000(这个月加上2个月)
2006/10/4 1000 4000
2006/10/5 1000 5000这个存储过程要怎么写?
(
c1 char(1),
c2 int
)insert test (c1,c2)
select 'a',2 union all
select 'b',2 union all
select 'c',5 union all
select 'd',9 select * from test
select c1,(select sum(c2) from test where c1<=a.c1) as c2 from test adrop table test
insert t select '2006/10/5',1000 union all
select '2006/10/6',1000 union all
select '2006/10/7',1000 union all
select '2006/10/8',1000
create proc b
as
select a.*,(select sum(col2) from t where col1<=a.col1) from t aexec bdrop proc b
drop table t
标题:求某個數據的遞增和(多一列)
INSERT @t SELECT 'a',1
UNION ALL SELECT 'b',1
UNION ALL SELECT 'c',1
UNION ALL SELECT 'd',1
UNION ALL SELECT 'x',2
UNION ALL SELECT 'e',2
UNION ALL SELECT 'r',2SELECT *,IDENTITY(int) ID INTO #t FROM @t ORDER BY f2SELECT x.f1+ ',' +y.f1,x.f2 FROM
(SELECT f1,f2,id FROM #t a WHERE 2>(SELECT COUNT(1) FROM #t b WHERE b.f2=a.f2 AND b.id<a.id)) x
INNER JOIN
(SELECT f1,f2,id FROM #t a WHERE 2>(SELECT COUNT(1) FROM #t b WHERE b.f2=a.f2 AND b.id<a.id)) y
ON x.f2=y.f2 AND x.id<y.id
DROP TABLE #t
/*示例数据
f1 f2
a1
b1
c1
d1
x2
e2
r2*/
/*结果数据
ab1
xe2*/