CREATE PROCEDURE dbo.UpdateMark
as
declare
@Col1 varchar(100),
@Col2 varchar(100),
@Col3 varchar(100),
@Col4 varchar(100),
@EarlyTime datetime
Begin
declare C_Mark cursor local
for SELECT DISTINCT Col1,Col2,Col3,Col4 FROM TABLE1
OPRN C_Mark
FETCH NEXT FROM C_Mark INTO @Col1,@Col2,@Col3,@Col4
while(@@fetch_status=0)
begin
UPDATE TABLE1 SET MARK=1 WHERE Date=(SELECT MIN(DATE) FROM TABLE1 WHERE Col1=@Col1 AND Col2=@Col2 AND Col3=@Col3 AND Col4=@Col4)
UPDATE TABLE1 SET MARK=-1 WHERE Col1=@Col1 AND Col2=@Col2 AND Col3=@Col3 AND Col4=@Col4) AND Mark=0
FETCH NEXT FROM C_Mark INTO @Col1,@Col2,@Col3,@Col4
end
close C_MARK
CEALLOCATE C_MARK
End
as
declare
@Col1 varchar(100),
@Col2 varchar(100),
@Col3 varchar(100),
@Col4 varchar(100),
@EarlyTime datetime
Begin
declare C_Mark cursor local
for SELECT DISTINCT Col1,Col2,Col3,Col4 FROM TABLE1
OPRN C_Mark
FETCH NEXT FROM C_Mark INTO @Col1,@Col2,@Col3,@Col4
while(@@fetch_status=0)
begin
UPDATE TABLE1 SET MARK=1 WHERE Date=(SELECT MIN(DATE) FROM TABLE1 WHERE Col1=@Col1 AND Col2=@Col2 AND Col3=@Col3 AND Col4=@Col4)
UPDATE TABLE1 SET MARK=-1 WHERE Col1=@Col1 AND Col2=@Col2 AND Col3=@Col3 AND Col4=@Col4) AND Mark=0
FETCH NEXT FROM C_Mark INTO @Col1,@Col2,@Col3,@Col4
end
close C_MARK
CEALLOCATE C_MARK
End
update table set =1 where id in(select id1 from(select max(date) as date,id1 from table group by col1,col2,col3,col4) DERIVEDTBL)
update a set MARK = (case when a.Date = b.Date then 1 else -1 end)
from yourTable a
join (Select Col1,Col,Col3,Col4,min(Date) as Date from yourTable ) b
on a.Col1 = b.Col1 and a.col2 = b.col2 and a.col3 = b.col3 and a.col4 = b.col4
update a set MARK = (case when a.Date = b.Date then 1 else -1 end)
from yourTable a
join (Select Col1,Col,Col3,Col4,min(Date) as Date from yourTable
group by Col1,Col,Col3,Col4) b
on a.Col1 = b.Col1 and a.col2 = b.col2 and a.col3 = b.col3 and a.col4 = b.col4
jiutiao兄的代码不成功。
试过txlicenhe兄的方法,待我检查无误后结分。