组内某字段的值出现2次以上相同的，字段更新为另一个值

Question

如以下数据，同一groupid，f列有2次以上"休息"的，第3次起，更新为“缺勤”
尽量用1条SQL实现。
groupid    date                   f
3 2013-08-05 00:00:00 休息
3 2013-08-06 00:00:00 休息
3 2013-08-07 00:00:00 上班1
3 2013-08-08 00:00:00 休息
3 2013-08-09 00:00:00 上班2
3 2013-08-10 00:00:00 休息
3 2013-08-11 00:00:00 休息
4 2013-08-07 00:00:00 上班1
4 2013-08-08 00:00:00 休息
4 2013-08-09 00:00:00 上班2
4 2013-08-10 00:00:00 休息
4 2013-08-11 00:00:00 上班1
4 2013-08-12 00:00:00 休息更新后，应该得到以下数据：
groupid    date                   f
3 2013-08-05 00:00:00 休息
3 2013-08-06 00:00:00 休息
3 2013-08-07 00:00:00 上班1
3 2013-08-08 00:00:00 缺勤
3 2013-08-09 00:00:00 上班2
3 2013-08-10 00:00:00 缺勤
3 2013-08-11 00:00:00 缺勤
4 2013-08-07 00:00:00 上班1
4 2013-08-08 00:00:00 休息
4 2013-08-09 00:00:00 上班2
4 2013-08-10 00:00:00 休息
4 2013-08-11 00:00:00 上班1
4 2013-08-12 00:00:00 缺勤

Answer 1

--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 >= 3select *
from tb
/*
groupid date             f
3 2013-08-05 00:00:00.000 休息
3 2013-08-06 00:00:00.000 休息
3 2013-08-07 00:00:00.000 上班1
3 2013-08-08 00:00:00.000 缺勤
3 2013-08-09 00:00:00.000 上班2
3 2013-08-10 00:00:00.000 缺勤
3 2013-08-11 00:00:00.000 缺勤
4 2013-08-07 00:00:00.000 上班1
4 2013-08-08 00:00:00.000 休息
4 2013-08-09 00:00:00.000 上班2
4 2013-08-10 00:00:00.000 休息
4 2013-08-11 00:00:00.000 上班1
4 2013-08-12 00:00:00.000 缺勤
*/

Answer 2

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