已经知道原表
year salary
------------------ ---------------------
2000 1000
2001 2000
2002 3000
2003 4000
显示查询结果
year salary
------------------ ---------------------
2000 1000
2001 3000
2002 6000
2003 10000即salary为以前年的工资的和;
请大家越简单越好,容易理解的就行
year salary
------------------ ---------------------
2000 1000
2001 2000
2002 3000
2003 4000
显示查询结果
year salary
------------------ ---------------------
2000 1000
2001 3000
2002 6000
2003 10000即salary为以前年的工资的和;
请大家越简单越好,容易理解的就行
[YEAR],
salary=(select sum(salary) from tb where t.year>=year)
from tb t
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb( year int, salary int )
go
insert tb SELECT
2000, 1000 UNION ALL SELECT
2001, 2000 UNION ALL SELECT
2002 ,3000 UNION ALL SELECT
2003 ,4000
go
select
[YEAR],
salary=(select sum(salary) from tb where t.year>=year)
from tb tgoYEAR salary
----------- -----------
2000 1000
2001 3000
2002 6000
2003 10000
DROP TABLE TEST
CREATE TABLE TEST(YEAR INT,SALARY INT)
INSERT TEST
SELECT 2000,1000 UNION ALL
SELECT 2001,2000 UNION ALL
SELECT 2002,3000 UNION ALL
SELECT 2003,4000
GO
SELECT * FROM TESTselect
[YEAR],
SALARY=(select sum(salary) from TEST where t.year>=year)
from TEST tYEAR SALARY
----------- -----------
2000 1000
2001 3000
2002 6000
2003 10000(所影响的行数为 4 行)
(SELECT SUM(salary) FROM TBTEST WHERE [YEAR]<=T.[YEAR]) AS SALARY
FROM TBTEST T
INSERT TBTEST
SELECT 2000,1000 UNION ALL
SELECT 2001,2000 UNION ALL
SELECT 2002,3000 UNION ALL
SELECT 2003,4000
GOSELECT [YEAR],
(SELECT SUM(salary) FROM TBTEST WHERE [YEAR]<=T.[YEAR]) AS SALARY
FROM TBTEST T
(所影响的行数为 4 行)YEAR SALARY
----------- -----------
2000 1000
2001 3000
2002 6000
2003 10000(所影响的行数为 4 行)
if object_id('card') is not null drop table Cardgo
create table card(year int,salary int)
insert into Card select
2000 ,1000 union all select
2001 ,2000 union all select
2002 ,3000 union all select
2003 ,4000
select year,[salary]=(select sum(salary) from card where year<=t.year) from card tyear salary
----------- -----------
2000 1000
2001 3000
2002 6000
2003 10000(4 行受影响)
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-08-08 22:44:21
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([year] int,[salary] int)
insert [tb]
select 2000,1000 union all
select 2001,2000 union all
select 2002,3000 union all
select 2003,4000
--------------开始查询--------------------------
select [year],(select sum(salary) from tb where [year]<=t.[year]) as [salary] from tb t
----------------结果----------------------------
/*
year salary
----------- -----------
2000 1000
2001 3000
2002 6000
2003 10000(所影响的行数为 4 行)*/