表形式如下:
Year Salary
2000 1000
2001 2000
2002 3000
2003 4000
想得到如下形式的查询结果
Year Salary
2000 1000
2001 3000
2002 6000
2003 10000
sql语句怎么写?
Year Salary
2000 1000
2001 2000
2002 3000
2003 4000
想得到如下形式的查询结果
Year Salary
2000 1000
2001 3000
2002 6000
2003 10000
sql语句怎么写?
from ta a
--> 生成测试数据: #T
IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T
CREATE TABLE #T (Year INT,Salary INT)
INSERT INTO #T
SELECT 2000,1000 UNION ALL
SELECT 2001,2000 UNION ALL
SELECT 2002,3000 UNION ALL
SELECT 2003,4000--SQL查询如下:SELECT Year,
Salary=(SELECT SUM(Salary)
FROM #T
WHERE Year<=A.Year)
FROM #T AS A/*
Year Salary
----------- -----------
2000 1000
2001 3000
2002 6000
2003 10000(4 行受影响)*/
insert @t select 2000, 1000
insert @t select 2001, 2000
insert @t select 2002, 3000
insert @t select 2003, 4000
select [Year],Salary=(select sum(Salary) from @t where [year]<=t.[year] ) from @t t
/*Year Salary
----- ---------------------
2000 1000.0000
2001 3000.0000
2002 6000.0000
2003 10000.0000(所影响的行数为 4 行)
*/
declare @t table(year int,salary int)
insert into @t select 2000,1000
union all select 2001,2000
union all select 2002,3000
union all select 2003,4000
select a.year,salary=(select sum(salary)from @t where year<=a.year) from @t a
2000 1000
2001 3000
2002 6000
2003 10000