开始表中的原始数据是这样的:emp_Id happen_date sale sale_month_sum
---------- ----------- ----------- --------------
E01 2012-01-01 100 0
E01 2012-01-02 200 0
E01 2012-01-03 500 0
E01 2012-01-04 700 0
E01 2012-01-05 150 0
E01 2012-01-06 0 0
E02 2012-01-02 600 0
E02 2012-01-03 0 0
E02 2012-01-04 300 0
E02 2012-01-05 800 0
E02 2012-01-06 0 0
通过一个sql达到以下效果:
emp_Id happen_date sale sale_month_sum
---------- ----------- ----------- --------------
E01 2012-01-01 100 100
E01 2012-01-02 200 300
E01 2012-01-03 500 800
E01 2012-01-04 700 1500
E01 2012-01-05 150 1650
E01 2012-01-06 0 1650
E02 2012-01-02 600 600
E02 2012-01-03 0 600
E02 2012-01-04 300 900
E02 2012-01-05 800 1700
E02 2012-01-06 0 1700
---------- ----------- ----------- --------------
E01 2012-01-01 100 0
E01 2012-01-02 200 0
E01 2012-01-03 500 0
E01 2012-01-04 700 0
E01 2012-01-05 150 0
E01 2012-01-06 0 0
E02 2012-01-02 600 0
E02 2012-01-03 0 0
E02 2012-01-04 300 0
E02 2012-01-05 800 0
E02 2012-01-06 0 0
通过一个sql达到以下效果:
emp_Id happen_date sale sale_month_sum
---------- ----------- ----------- --------------
E01 2012-01-01 100 100
E01 2012-01-02 200 300
E01 2012-01-03 500 800
E01 2012-01-04 700 1500
E01 2012-01-05 150 1650
E01 2012-01-06 0 1650
E02 2012-01-02 600 600
E02 2012-01-03 0 600
E02 2012-01-04 300 900
E02 2012-01-05 800 1700
E02 2012-01-06 0 1700
解决方案 »
- SQL Server 2005的P2p拓扑结构的事务复制
- 初学者的问题
- myeclipse链接sqlsever2005数据库
- 求TSQL语句: 给定一个字符串,里面含有逗号分割的本号列表,要求查寻出符合本号的行
- SQLServer2005,update中的“.write()”怎么用?
- ChildCount = conn.Execute("select count(*) from xuq_tree where id1="&id)(0)
- update触发器为什么滞后反应?
- 如何在报表中不同列显示同一个表中同一个字段
- 大家整理一下tsql命令可以实现,但是企业管理器图形界面不能实现的操作!谢谢!
- 一个SQL问题。
- sql效率
- sum求值
create table tb(emp_Id varchar(10),happen_date datetime,sale int,sale_month_sum int)
insert into tb values('E01', '2012-01-01', 100 , 0)
insert into tb values('E01', '2012-01-02', 200 , 0)
insert into tb values('E01', '2012-01-03', 500 , 0)
insert into tb values('E01', '2012-01-04', 700 , 0)
insert into tb values('E01', '2012-01-05', 150 , 0)
insert into tb values('E01', '2012-01-06', 0 , 0)
insert into tb values('E02', '2012-01-02', 600 , 0)
insert into tb values('E02', '2012-01-03', 0 , 0)
insert into tb values('E02', '2012-01-04', 300 , 0)
insert into tb values('E02', '2012-01-05', 800 , 0)
insert into tb values('E02', '2012-01-06', 0 , 0)
goselect t.emp_Id,t.happen_date,t.sale , sale + (select sum(sale) from tb where emp_Id = t.emp_Id and happen_date <= t.happen_date) sale_month_sum from tb tdrop table tb/*
emp_Id happen_date sale sale_month_sum
---------- ------------------------------------------------------ ----------- --------------
E01 2012-01-01 00:00:00.000 100 200
E01 2012-01-02 00:00:00.000 200 500
E01 2012-01-03 00:00:00.000 500 1300
E01 2012-01-04 00:00:00.000 700 2200
E01 2012-01-05 00:00:00.000 150 1800
E01 2012-01-06 00:00:00.000 0 1650
E02 2012-01-02 00:00:00.000 600 1200
E02 2012-01-03 00:00:00.000 0 600
E02 2012-01-04 00:00:00.000 300 1200
E02 2012-01-05 00:00:00.000 800 2500
E02 2012-01-06 00:00:00.000 0 1700(所影响的行数为 11 行)*/
create table tb(emp_Id varchar(10),happen_date datetime,sale int,sale_month_sum int)
insert into tb values('E01', '2012-01-01', 100 , 0)
insert into tb values('E01', '2012-01-02', 200 , 0)
insert into tb values('E01', '2012-01-03', 500 , 0)
insert into tb values('E01', '2012-01-04', 700 , 0)
insert into tb values('E01', '2012-01-05', 150 , 0)
insert into tb values('E01', '2012-01-06', 0 , 0)
insert into tb values('E02', '2012-01-02', 600 , 0)
insert into tb values('E02', '2012-01-03', 0 , 0)
insert into tb values('E02', '2012-01-04', 300 , 0)
insert into tb values('E02', '2012-01-05', 800 , 0)
insert into tb values('E02', '2012-01-06', 0 , 0)
goselect t.emp_Id,t.happen_date,t.sale , (select sum(sale) from tb where emp_Id = t.emp_Id and happen_date <= t.happen_date) sale_month_sum from tb tdrop table tb/*
emp_Id happen_date sale sale_month_sum
---------- ------------------------------------------------------ ----------- --------------
E01 2012-01-01 00:00:00.000 100 100
E01 2012-01-02 00:00:00.000 200 300
E01 2012-01-03 00:00:00.000 500 800
E01 2012-01-04 00:00:00.000 700 1500
E01 2012-01-05 00:00:00.000 150 1650
E01 2012-01-06 00:00:00.000 0 1650
E02 2012-01-02 00:00:00.000 600 600
E02 2012-01-03 00:00:00.000 0 600
E02 2012-01-04 00:00:00.000 300 900
E02 2012-01-05 00:00:00.000 800 1700
E02 2012-01-06 00:00:00.000 0 1700(所影响的行数为 11 行)*/
(select SUM(day_sale)from monthScore where happen_date<m.happen_date and emp_Id=m.emp_Id) is null then 0 else
day_sale+(select SUM(day_sale)from monthScore where happen_date<m.happen_date and emp_Id=m.emp_Id)
end
from monthScore(nolock) m