数据表Table1,字段A(数值类型),在这个表的基础上生成一个新表，
这个新表包含Table1中的字段A信息，还要在这个新表中创建一个新字段(字段B),
新字段的值为Table1中字段A的Top N-1 条字段A值的合计。
Table1
字段A
1
2
3
4
..
创建的新表如下：
Table2
字段A 字段B
1 1
2 3
3 6
4 10
.. ..
------解决方案--------------------------------------------------------
SELECT 字段A,
(SELECT SUM(字段A) AS bbb
FROM Table1
WHERE (字段A <= a.字段A)) AS 字段B
FROM Table1 AS a
------解决方案--------------------------------------------------------
select id,newCol=(select sum(id) from tb b where b.id <=a.id )
from tb a
------解决方案--------------------------------------------------------
其实这主要是看你按什么汇总了。
create table #
(
id int,
value int
)
insert into #
select 1, 100 union all
select 2, 200 union all
select 3, 300 union all
select 4, 400 union all
select 5, 500
select * from #
select b.Id, a.Id, a.value as sums
from # a, # b
where a.Id <= b.Id
order by b.Id
select b.Id, sum(a.value) as sums
from # a, # b
where a.Id <= b.Id
group by b.Id
order by b.Id
drop table #
执行结果如下：
id value
----------- -----------
1 100
2 200
3 300
4 400
5 500
Id Id sums
----------- ----------- -----------
1 1 100
2 1 100
2 2 200
3 1 100
3 2 200
3 3 300
4 1 100
4 2 200
4 3 300
4 4 400
5 1 100
5 2 200
5 3 300
5 4 400
5 5 500
Id sums
----------- -----------
1 100
2 300
3 600
4 1000
5 1500