if object_id('pubs..tb') is not null drop table tb gocreate table tb(a int , b int) insert into tb(a,b) values(10,null) insert into tb(a,b) values(50,null) insert into tb(a,b) values(10,null) insert into tb(a,b) values(20,null)select id = identity(int,1,1) , * into test from tbupdate test set B=(select isnull(sum(a),0) from test TT where TT.id<=test.id)+(select isnull(b,0) from test where id=1) delete from tb insert tb select a,b from test select * from tbdrop table tb,test/* a b ----------- ----------- 10 10 50 60 10 70 20 90(所影响的行数为 4 行) */
借用楼上的列子: create table tb(a int , b int) insert into tb(a,b) values(10,null) insert into tb(a,b) values(50,null) insert into tb(a,b) values(10,null) insert into tb(a,b) values(20,null)alter table tb add id int identity(1,1) not null--新列 go update tc set b=isnull((select case when b is null then a else b end from tb where id=tc.id-1),0)+a from tb tc go alter table tb drop column id--删新列select * from tb--drop table tb a b ----------- ----------- 10 10 50 60 10 60 20 30(4 行受影响)
以上语句有点问题,如果B列没有列的情况下可以这样: 借用楼上的列子: create table tb(a int , b int) insert into tb(a,b) values(10,null) insert into tb(a,b) values(50,null) insert into tb(a,b) values(10,null) insert into tb(a,b) values(20,null)alter table tb add id int identity(1,1) not null--新列 go update tc set b=(select sum(a) from tb where id!>tc.id) from tb tc go alter table tb drop column id--删新列select * from tb--truncate table tb--drop table tb a b ----------- ----------- 10 10 50 60 10 70 20 90(4 行受影响)
SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO--------------------------------------------------------------------------- --------------作用:根据扬水站的本日开车时间\本日排水量\本日耗电数,计算累计数值---------------------------------------------------------------------------ALTER trigger ST_RWS_R_insertAndUpdate on ST_RWS_R after insert,update as Declare @nhour integer Declare @mhour integer Declare @nmin integer Declare @mmin integer Declare @vw integer Declare @ve integer ------------------计算累计排水量------------------ Select @vw=Sum(a.TDRAIN) From ST_RWS_R a Inner join inserted b On year(a.tm)=year(b.tm) And a.stcd=b.stcd ------------------计算累计耗电量----------------------- Select @ve=Sum(a.TUELE) From ST_RWS_R a Inner join inserted b On year(a.tm)=year(b.tm) And a.stcd=b.stcd -------------------读取本日开车小时---------------- Select @nhour=a.TDRH From ST_RWS_R a Inner join inserted b On a.tm=b.tm And a.stcd=b.stcd
--------------------读取本日开车分钟-------------------------------- Select @nmin=a.TDRM From ST_RWS_R a Inner join inserted b On a.tm=b.tm And a.stcd=b.stcd ---------------------读取上一天的累计分钟数--------------------------- Select @mmin=c.GDRM from ST_RWS_R c,inserted d where c.tm=(select max(a.TM) from ST_RWS_R a Inner join inserted b On year(a.tm)=year(b.tm) And a.stcd=b.stcd and a.tm<b.tm ) and c.stcd=d.stcd ---------------------读取上一天的累计小时数--------------------------- Select @mhour=c.GDRH from ST_RWS_R c,inserted d where c.tm=(select max(a.TM) from ST_RWS_R a Inner join inserted b On year(a.tm)=year(b.tm) And a.stcd=b.stcd and a.tm<b.tm ) and c.stcd=d.stcd IF @mmin is null SET @mmin=0 IF @nhour is null SET @nhour=0 IF @mhour is null Set @mhour=0 IF @nmin is null SET @nmin=0
IF @nmin+@mmin>=60 Begin UpDate ST_RWS_R Set GDRM=@nmin+@mmin-60 From ST_RWS_R a Inner join inserted b On a.tm=b.tm And a.stcd=b.stcd
Set @nhour=@nhour+1 End Else UpDate ST_RWS_R Set GDRM=@nmin+@mmin From ST_RWS_R a Inner join inserted b On a.tm=b.tm And a.stcd=b.stcd UpDate ST_RWS_R Set GDRH=@nhour+@mhour From ST_RWS_R a Inner join inserted b On a.tm=b.tm And a.stcd=b.stcd UpDate ST_RWS_R Set GDRAIN=@vw From ST_RWS_R a Inner join inserted b On a.tm=b.tm And a.stcd=b.stcd UpDate ST_RWS_R Set GUELE=@ve From ST_RWS_R a Inner join inserted b On a.tm=b.tm And a.stcd=b.stcd GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
drop table tb
gocreate table tb(a int , b int)
insert into tb(a,b) values(10,null)
insert into tb(a,b) values(50,null)
insert into tb(a,b) values(10,null)
insert into tb(a,b) values(20,null)select id = identity(int,1,1) , * into test from tbupdate test
set B=(select isnull(sum(a),0)
from test TT
where TT.id<=test.id)+(select isnull(b,0) from test where id=1) delete from tb
insert tb select a,b from test
select * from tbdrop table tb,test/*
a b
----------- -----------
10 10
50 60
10 70
20 90(所影响的行数为 4 行)
*/
create table tb(a int , b int)
insert into tb(a,b) values(10,null)
insert into tb(a,b) values(50,null)
insert into tb(a,b) values(10,null)
insert into tb(a,b) values(20,null)alter table tb add id int identity(1,1) not null--新列
go
update tc
set b=isnull((select case when b is null then a else b end from tb where id=tc.id-1),0)+a
from tb tc
go
alter table tb drop column id--删新列select * from tb--drop table tb
a b
----------- -----------
10 10
50 60
10 60
20 30(4 行受影响)
借用楼上的列子:
create table tb(a int , b int)
insert into tb(a,b) values(10,null)
insert into tb(a,b) values(50,null)
insert into tb(a,b) values(10,null)
insert into tb(a,b) values(20,null)alter table tb add id int identity(1,1) not null--新列
go
update tc
set b=(select sum(a) from tb where id!>tc.id)
from tb tc
go
alter table tb drop column id--删新列select * from tb--truncate table tb--drop table tb
a b
----------- -----------
10 10
50 60
10 70
20 90(4 行受影响)
GO
SET ANSI_NULLS ON
GO---------------------------------------------------------------------------
--------------作用:根据扬水站的本日开车时间\本日排水量\本日耗电数,计算累计数值---------------------------------------------------------------------------ALTER trigger ST_RWS_R_insertAndUpdate on ST_RWS_R after insert,update
as
Declare @nhour integer
Declare @mhour integer
Declare @nmin integer
Declare @mmin integer
Declare @vw integer
Declare @ve integer
------------------计算累计排水量------------------
Select @vw=Sum(a.TDRAIN)
From ST_RWS_R a
Inner join inserted b
On year(a.tm)=year(b.tm)
And a.stcd=b.stcd
------------------计算累计耗电量-----------------------
Select @ve=Sum(a.TUELE)
From ST_RWS_R a
Inner join inserted b
On year(a.tm)=year(b.tm)
And a.stcd=b.stcd -------------------读取本日开车小时----------------
Select @nhour=a.TDRH
From ST_RWS_R a
Inner join inserted b
On a.tm=b.tm
And a.stcd=b.stcd
--------------------读取本日开车分钟--------------------------------
Select @nmin=a.TDRM
From ST_RWS_R a
Inner join inserted b
On a.tm=b.tm
And a.stcd=b.stcd ---------------------读取上一天的累计分钟数---------------------------
Select @mmin=c.GDRM from ST_RWS_R c,inserted d
where c.tm=(select max(a.TM) from ST_RWS_R a Inner join inserted b
On year(a.tm)=year(b.tm)
And a.stcd=b.stcd
and a.tm<b.tm )
and c.stcd=d.stcd
---------------------读取上一天的累计小时数---------------------------
Select @mhour=c.GDRH from ST_RWS_R c,inserted d
where c.tm=(select max(a.TM) from ST_RWS_R a Inner join inserted b
On year(a.tm)=year(b.tm)
And a.stcd=b.stcd
and a.tm<b.tm )
and c.stcd=d.stcd IF @mmin is null SET @mmin=0
IF @nhour is null SET @nhour=0
IF @mhour is null Set @mhour=0
IF @nmin is null SET @nmin=0
IF @nmin+@mmin>=60
Begin
UpDate ST_RWS_R Set GDRM=@nmin+@mmin-60
From ST_RWS_R a
Inner join inserted b
On a.tm=b.tm
And a.stcd=b.stcd
Set @nhour=@nhour+1
End
Else
UpDate ST_RWS_R Set GDRM=@nmin+@mmin
From ST_RWS_R a
Inner join inserted b
On a.tm=b.tm
And a.stcd=b.stcd UpDate ST_RWS_R Set GDRH=@nhour+@mhour
From ST_RWS_R a
Inner join inserted b
On a.tm=b.tm
And a.stcd=b.stcd UpDate ST_RWS_R Set GDRAIN=@vw
From ST_RWS_R a
Inner join inserted b
On a.tm=b.tm
And a.stcd=b.stcd UpDate ST_RWS_R Set GUELE=@ve
From ST_RWS_R a
Inner join inserted b
On a.tm=b.tm
And a.stcd=b.stcd GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO