table
id mid ishow utime
1 1 0 20110411
2 1 0 20110412
3 1 0 20110413
4 1 0 20110414
5 2 0 20110412
6 2 0 20110415
....id int
mid int
ishow bit
utime int如何把同一mid,utime值最大的那个的ishow值更新为1?
求教,谢谢。
id mid ishow utime
1 1 0 20110411
2 1 0 20110412
3 1 0 20110413
4 1 0 20110414
5 2 0 20110412
6 2 0 20110415
....id int
mid int
ishow bit
utime int如何把同一mid,utime值最大的那个的ishow值更新为1?
求教,谢谢。
from tb a where utime=(select max(utime) from tb where mid=a.mid)
declare @table table (id int,mid int,ishow int,utime datetime)
insert into @table
select 1,1,0,'20110411' union all
select 2,1,0,'20110412' union all
select 3,1,0,'20110413' union all
select 4,1,0,'20110414' union all
select 5,2,0,'20110412' union all
select 6,2,0,'20110415'update @table
set ishow=1
from @table a inner join (
select mid,max(utime) as utime from @table group by mid
) b on a.mid=b.mid and a.utime=b.utimeselect * from @table
/*
id mid ishow utime
----------- ----------- ----------- -----------------------
1 1 0 2011-04-11 00:00:00.000
2 1 0 2011-04-12 00:00:00.000
3 1 0 2011-04-13 00:00:00.000
4 1 1 2011-04-14 00:00:00.000
5 2 0 2011-04-12 00:00:00.000
6 2 1 2011-04-15 00:00:00.000
*/
set ishow = 1
from tb T
where utime = (select max(utime) from tb where mid=T.mid)
update table set ishow=1
from table a inner join (select mid,max(utime) from table group by mid) B on A.mid=B.mid and A.utime=B.utime
from (select id from tb where not exists (select 1 from tb a where a.mid=tb.mid and a.utime>tb.utime)) b
where tb.id=b.id