高分求sql语句！添加状态字段！其中有垃圾数据！

[showmetoyou]

如下表
表（tb_sound）结构如下：
id userid fee paytime
5281 2211358 20.00 2004-09-29 20:39:19.403
5282 2211358 20.00 2004-09-29 20:39:19.450
5291 2211358 30.00 2004-09-29 21:39:19.403
5292 2211358 30.00 2004-09-29 21:39:19.450
5366 2211550 10.00 2004-09-30 13:20:37.340
5367 2211550 10.00 2004-09-30 13:20:37.340
5388 2211358 20.00 2004-09-31 20:39:19.403
5389 2211358 20.00 2004-09-31 20:39:19.450
5398 2211550 10.00 2004-09-30 14:20:37.340
5399 2211550 10.00 2004-09-30 14:20:37.390
条件如下：id为自增列，由于操作上的失误，相同userid插入了两条相同的数据，id紧连！
上表中id为5282，5292，5367，5389，5399为垃圾数据！
正确数据应为：
id userid fee paytime
5281 2211358 20.00 2004-09-29 20:39:19.403
5291 2211358 30.00 2004-09-29 21:39:19.403
5366 2211550 10.00 2004-09-30 13:20:37.340
5388 2211358 20.00 2004-09-31 20:39:19.403
5398 2211550 10.00 2004-09-30 14:20:37.340
然后根据正确数据，希望添加一列状态字段，按照id大小确定相同userid，哪条记录是这个用户第一次记录（id最小），（0表示第一次用户，1表示老用户）！
结果如下：
id userid fee paytime userstate
5281 2211358 20.00 2004-09-29 20:39:19.403 0
5291 2211358 30.00 2004-09-29 21:39:19.403 1
5366 2211550 10.00 2004-09-30 13:20:37.340 0
5388 2211358 20.00 2004-09-31 20:39:19.403 1
5398 2211550 10.00 2004-09-30 14:20:37.340 1
在线等！谢谢！

[humanNew]

自己写个存储过程，使用Cursor判断,说实在的你还如写个INSERT的触发器呢

[pbsql]

alter table tb_sound add userstate bit--加一列
go
update tb_sound set userstate=case when exists(select * from tb_sound t where t.userid=tb_sound.userid and t.id>tb_sound.id) then 1 else 0 end--更新

[iswear428]

--添加字段
ALTER TABLE tb_sound ADD userstate INT CONSTRAINT [CK_tb_sound_ISSTATE] CHECK ([userstate] = '0' or [userstate] = '1')
--更新字段的值
UPDATE tb_sound
SET userstate = CASE WHEN ID= (SELECT MIN(ID) FROM tb_sound AS B WHERE B.userid= userid ) THEN 0 ELSE 1 END

[pbsql]

条件写反了：
alter table tb_sound add userstate bit--加一列
go
update tb_sound set userstate=case when exists(select * from tb_sound t where t.userid=tb_sound.userid and t.id<tb_sound.id) then 1 else 0 end--更新

[子陌红尘]

--删除重复数据
delete from tb_sound
where id in
(select
min(id) as ID
from
tb_sound
group by
userid,fee,convert(varchar(16),paytime,120))


--在已经删除重复数据的表中查询数据并在查询中输出相应的userstate
select
a.*,
case when b.ID isnull then 1 else 0 end as userstate
from
tb_sound a
left join
(select min(ID) as ID from tb_sound group by userid) b
on
a.id = b.id

[子陌红尘]

alter table tb_sound add userstate bit--加一列

update tb_sound
set userstate = (case when b.ID isnull then 1 else 0 end)
from
tb_sound a
left join
(select min(ID) as ID from tb_sound group by userid) b
on
a.id = b.id