更新数据问题。。请大虾帮忙看看

[hlq8210]

更新数据问题
1：
id tmpdate tmptime colid
1 2005-05-31 00:00:00 2005-05-31 08:03:13.000 1
1 2005-05-31 00:00:00 2005-05-31 12:45:28.000 1
1 2005-05-31 00:00:00 2005-05-31 18:49:55.000 1
1 2005-07-01 00:00:00 2005-07-01 08:49:55.000 1
1 2005-07-01 00:00:00 2005-07-01 17:15:11.000 1

2 2005-05-31 00:00:00 2005-05-31 08:03:13.000 1
2 2005-05-31 00:00:00 2005-05-31 12:45:28.000 1
2 2005-07-01 00:00:00 2005-07-01 08:49:55.000 1
2 2005-07-01 00:00:00 2005-07-01 17:15:11.000 1

要结果：

1 2005-05-31 00:00:00 2005-05-31 08:03:13.000 1
1 2005-05-31 00:00:00 2005-05-31 12:45:28.000 2
1 2005-05-31 00:00:00 2005-05-31 18:49:55.000 3
1 2005-07-01 00:00:00 2005-07-01 08:49:55.000 1
1 2005-07-01 00:00:00 2005-07-01 17:15:11.000 2

2 2005-05-31 00:00:00 2005-05-31 08:03:13.000 1
2 2005-05-31 00:00:00 2005-05-31 12:45:28.000 2
2 2005-07-01 00:00:00 2005-07-01 08:49:55.000 1
2 2005-07-01 00:00:00 2005-07-01 17:15:11.000 2



Employeeid ,tmpdate 相同的数据 按tmptime 的升序 递增修改colid 值

[子陌红尘]

declare @id int,@tmpdate datetime,@colid int

update t
set
@colid = case when id=@id and tmpdate=@tmpdate then @colid+1 else 1 end,
colid = @colid,
@id = id,
@tmpdate = tmpdate

[子陌红尘]

update
a
set
a.colid = b.cnt
from
表 a,
(select
id,
tmpdate,
tmptime,
cnt = (select
count(*)
from
表
where
id=c.id
and
tmpdate=c.tmpdate
and
tmptime<=c.tmptime)
from
表 c) b
where
a.id = b.id
and
a.tmpdate = b.tmpdate
and
a.tmptime = b.tmptime

[hlq8210]

to libin_ftsafe(子陌红尘) 谢谢你
马上结帖

[zlp321002]

--测试环境
declare @t table (id int ,tmpdate datetime,tmptime datetime,colid int)
insert into @t select 1,'2005-05-31 00:00:00','2005-05-31 08:03:13.000',1
union all select 1,'2005-05-31 00:00:00','2005-05-31 12:45:28.000',1
union all select 1,'2005-05-31 00:00:00','2005-05-31 18:49:55.000',1
union all select 1,'2005-07-01 00:00:00','2005-07-01 08:49:55.000',1
union all select 1,'2005-07-01 00:00:00','2005-07-01 17:15:11.000',1
union all select 2,'2005-05-31 00:00:00','2005-05-31 08:03:13.000',1
union all select 2,'2005-05-31 00:00:00','2005-05-31 12:45:28.000',1
union all select 2,'2005-07-01 00:00:00','2005-07-01 08:49:55.000',1
union all select 2,'2005-07-01 00:00:00','2005-07-01 17:15:11.000',1
--更新语句
update @t
set colid=T.序号
from @t S,
(
select *,序号=(select sum(1) from @t where id=a.id and tmpdate=a.tmpdate and tmptime<=a.tmptime)
from @t a
)
T
where S.id=T.id and S.tmpdate=T.tmpdate and S.tmptime=T.tmptime
--查看结果
select * from @t
id tmpdate tmptime colid
----------- ------------------------------------------------------ ------------------------------------------------------ -----------
1 2005-05-31 00:00:00.000 2005-05-31 08:03:13.000 1
1 2005-05-31 00:00:00.000 2005-05-31 12:45:28.000 2
1 2005-05-31 00:00:00.000 2005-05-31 18:49:55.000 3
1 2005-07-01 00:00:00.000 2005-07-01 08:49:55.000 1
1 2005-07-01 00:00:00.000 2005-07-01 17:15:11.000 2
2 2005-05-31 00:00:00.000 2005-05-31 08:03:13.000 1
2 2005-05-31 00:00:00.000 2005-05-31 12:45:28.000 2
2 2005-07-01 00:00:00.000 2005-07-01 08:49:55.000 1
2 2005-07-01 00:00:00.000 2005-07-01 17:15:11.000 2

[hlq8210]

明天结帖
现在结不了

[zhangyang555]

生成测试数据:
create table test(id int ,tmpdate datetime,tmptime datetime,colid int)

insert into test (id,tmpdate,tmptime,colid) values(1,'2005-05-31 00:00:00','2005-05-31 08:03:13.000',1)
insert into test (id,tmpdate,tmptime,colid) values(1,'2005-05-31 00:00:00','2005-05-31 12:45:28.000',1)
insert into test (id,tmpdate,tmptime,colid) values(1,'2005-05-31 00:00:00','2005-05-31 18:49:55.000',1)
insert into test (id,tmpdate,tmptime,colid) values(1,'2005-07-01 00:00:00','2005-07-01 08:49:55.000',1)
insert into test (id,tmpdate,tmptime,colid) values(1,'2005-07-01 00:00:00','2005-07-01 17:15:11.000',1)

insert into test (id,tmpdate,tmptime,colid) values(2,'2005-05-31 00:00:00','2005-05-31 08:03:13.000',1)
insert into test (id,tmpdate,tmptime,colid) values(2,'2005-05-31 00:00:00','2005-05-31 12:45:28.000',1)
insert into test (id,tmpdate,tmptime,colid) values(2,'2005-07-01 00:00:00','2005-07-01 08:49:55.000',1)
insert into test (id,tmpdate,tmptime,colid) values(2,'2005-07-01 00:00:00','2005-07-01 17:15:11.000',1)


查询语句:
select t.id,t.tmpdate,t.tmptime,(select count(distinct tmptime) from test where id=t.id and tmpdate=t.tmpdate and tmptime<=t.tmptime) as 'colid' from test t group by id,tmpdate,tmptime order by t.id,t.tmpdate,t.tmptime

[zhangyang555]

哦,原来是更新哦,呵呵,没看清楚

update a set a.colid = b.cnt
from test a,(select id,tmpdate,tmptime,cnt = (select count(*) from test where id=c.id and
tmpdate=c.tmpdate and tmptime<=c.tmptime) from test c) b where a.id = b.id and a.tmpdate = b.tmpdate and a.tmptime = b.tmptime