组内某字段的值出现2次以上相同的，字段更新为另一个值
如以下数据，同一groupid，f列有2次以上"休息"的，第3次起，更新为“缺勤”
尽量用1条SQL实现。
groupid date f
32013-08-05 00:00:00休息
32013-08-06 00:00:00休息
32013-08-07 00:00:00上班1
32013-08-08 00:00:00休息
32013-08-09 00:00:00上班2
32013-08-10 00:00:00休息
32013-08-11 00:00:00休息
42013-08-07 00:00:00上班1
42013-08-08 00:00:00休息
42013-08-09 00:00:00上班2
42013-08-10 00:00:00休息
42013-08-11 00:00:00上班1
42013-08-12 00:00:00休息
更新后，应该得到以下数据：
groupid date f
32013-08-05 00:00:00休息
32013-08-06 00:00:00休息
32013-08-07 00:00:00上班1
32013-08-08 00:00:00缺勤
32013-08-09 00:00:00上班2
32013-08-10 00:00:00缺勤
32013-08-11 00:00:00缺勤
42013-08-07 00:00:00上班1
42013-08-08 00:00:00休息
42013-08-09 00:00:00上班2
42013-08-10 00:00:00休息
42013-08-11 00:00:00上班1
42013-08-12 00:00:00缺勤
[解决办法]
--drop table tb
--go
create table tb(groupid int, date datetime, f varchar(10));
insert into tb
select 3,'2013-08-05 00:00:00','休息'
union all select 3,'2013-08-06 00:00:00','休息'
union all select 3,'2013-08-07 00:00:00','上班1'
union all select 3,'2013-08-08 00:00:00','休息'
union all select 3,'2013-08-09 00:00:00','上班2'
union all select 3,'2013-08-10 00:00:00','休息'
union all select 3,'2013-08-11 00:00:00','休息'
union all select 4,'2013-08-07 00:00:00','上班1'
union all select 4,'2013-08-08 00:00:00','休息'
union all select 4,'2013-08-09 00:00:00','上班2'
union all select 4,'2013-08-10 00:00:00','休息'
union all select 4,'2013-08-11 00:00:00','上班1'
union all select 4,'2013-08-12 00:00:00','休息'
;with t
as
(
select *,
ROW_NUMBER() over(partition by groupid order by date) as rownum
from tb
where f = '休息'
)
update t
set f = '缺勤'
where rownum >= 3
select *
from tb
/*
groupiddate f
32013-08-05 00:00:00.000休息
32013-08-06 00:00:00.000休息
32013-08-07 00:00:00.000上班1
32013-08-08 00:00:00.000缺勤
32013-08-09 00:00:00.000上班2
32013-08-10 00:00:00.000缺勤
32013-08-11 00:00:00.000缺勤
42013-08-07 00:00:00.000上班1
42013-08-08 00:00:00.000休息
42013-08-09 00:00:00.000上班2
42013-08-10 00:00:00.000休息
42013-08-11 00:00:00.000上班1
42013-08-12 00:00:00.000缺勤
*/
[解决办法]
create table #tb
(groupid int, date datetime, f varchar(10)); insert into #tb
select 3 ,'2013-08-05 00:00:00', '休息'union all
select 3 ,'2013-08-06 00:00:00', '休息'union all
select 3 ,'2013-08-07 00:00:00', '上班1'union all
select 3 ,'2013-08-08 00:00:00', '休息'union all
select 3 ,'2013-08-09 00:00:00', '上班2'union all
select 3 ,'2013-08-10 00:00:00', '休息'union all
select 3 ,'2013-08-11 00:00:00', '休息'union all
select 4 ,'2013-08-07 00:00:00', '上班1'union all
select 4 ,'2013-08-08 00:00:00', '休息'union all
select 4 ,'2013-08-09 00:00:00', '上班2'union all
select 4 ,'2013-08-10 00:00:00', '休息'union all
select 4 ,'2013-08-11 00:00:00', '上班1'union all
select 4 ,'2013-08-12 00:00:00', '休息'
select * from #tb
--select * from (select rn= ROW_NUMBER () over(PARTITION by groupid order by date),* from #tb where f='休息' ) a
update #tb
set f='缺勤'
from #tb b join (select rn= ROW_NUMBER () over(PARTITION by groupid order by date),* from #tb where f='休息' ) a on a.groupid =b.groupid and a.date =b.date
where a.rn >=3
select * from #tb
drop table #tb