组内某列的值连续出现3次标记出来
如下，eid为人员ID，对于同一个EID，date列连续出现3次以上的，FLAG列标记为1.
尽量用1条UPDATE语句！
eiddate flag
12013-3-1
12013-3-2
12013-3-41
12013-3-51
12013-3-71
12013-3-81
12013-3-91
12013-3-11
12013-3-12
12013-3-141
12013-3-151
12013-3-161
12013-3-18
22013-3-1
22013-3-2
22013-3-4
22013-3-61
22013-3-71
22013-3-81
22013-3-91
22013-3-111
22013-3-121
22013-3-131
22013-3-15
22013-3-16
22013-3-181
22013-3-191
22013-3-201
32013-3-1
32013-3-2
32013-3-41
32013-3-51
32013-3-61
32013-3-8
32013-3-9
32013-3-121
32013-3-131
32013-3-141
32013-3-151
32013-3-181
32013-3-191
32013-3-201
[解决办法]
采用了row_number函数，以下代码适合2005、2008等，不适合2000.
CREATE TABLE #t (eid INT,DATE DATETIME, flag INT )
GO
INSERT #t(eid,DATE,flag)
select 1,'2013-3-1',null union
select 1,'2013-3-2',null union
select 1,'2013-3-4',null union
select 1,'2013-3-5',null union
select 1,'2013-3-7',1 union
select 1,'2013-3-8',1 union
select 1,'2013-3-9',1 union
select 1,'2013-3-11',null union
select 1,'2013-3-12',null union
select 1,'2013-3-14',1 union
select 1,'2013-3-15',1 union
select 1,'2013-3-16',1 union
select 1,'2013-3-18',null union
select 2,'2013-3-1',null union
select 2,'2013-3-2',null union
select 2,'2013-3-4',null union
select 2,'2013-3-6',1 union
select 2,'2013-3-7',1 union
select 2,'2013-3-8',1 union
select 2,'2013-3-9',1 union
select 2,'2013-3-11',1 union
select 2,'2013-3-12',1 union
select 2,'2013-3-13',1 union
select 2,'2013-3-15',null union
select 2,'2013-3-16',null union
select 2,'2013-3-18',1 union
select 2,'2013-3-19',1 union
select 2,'2013-3-20',1 union
select 3,'2013-3-1',null union
select 3,'2013-3-2',null union
select 3,'2013-3-4',1 union
select 3,'2013-3-5',1 union
select 3,'2013-3-6',1 union
select 3,'2013-3-8',null union
select 3,'2013-3-9',null union
select 3,'2013-3-12',1 union
select 3,'2013-3-13',1 union
select 3,'2013-3-14',1 union
select 3,'2013-3-15',1 union
select 3,'2013-3-18',1 union
select 3,'2013-3-19',1 union
select 3,'2013-3-20',1
go
select eid,
date,
flag,
case when count(*) over(partition by eid,DATEadd(day,-rownum,date)) >=3
then 1
else null
end '计算出来的flag值' -- 这列和你插入到flag中的值是一样的
from
(
select *,
ROW_NUMBER() over(partition by eid order by date) rownum
from #t
)t
/*
eiddateflag计算出来的flag值
12013-03-01 00:00:00.000NULLNULL
12013-03-02 00:00:00.000NULLNULL
12013-03-04 00:00:00.000NULLNULL
12013-03-05 00:00:00.000NULLNULL
12013-03-07 00:00:00.00011
12013-03-08 00:00:00.00011
12013-03-09 00:00:00.00011
12013-03-11 00:00:00.000NULLNULL
12013-03-12 00:00:00.000NULLNULL
12013-03-14 00:00:00.00011
12013-03-15 00:00:00.00011
12013-03-16 00:00:00.00011
12013-03-18 00:00:00.000NULLNULL
22013-03-01 00:00:00.000NULLNULL
22013-03-02 00:00:00.000NULLNULL
22013-03-04 00:00:00.000NULLNULL
22013-03-06 00:00:00.00011
22013-03-07 00:00:00.00011
22013-03-08 00:00:00.00011
22013-03-09 00:00:00.00011
22013-03-11 00:00:00.00011
22013-03-12 00:00:00.00011
22013-03-13 00:00:00.00011
22013-03-15 00:00:00.000NULLNULL
22013-03-16 00:00:00.000NULLNULL
22013-03-18 00:00:00.00011
22013-03-19 00:00:00.00011
22013-03-20 00:00:00.00011
32013-03-01 00:00:00.000NULLNULL
32013-03-02 00:00:00.000NULLNULL
32013-03-04 00:00:00.00011
32013-03-05 00:00:00.00011
32013-03-06 00:00:00.00011
32013-03-08 00:00:00.000NULLNULL
32013-03-09 00:00:00.000NULLNULL
32013-03-12 00:00:00.00011
32013-03-13 00:00:00.00011
32013-03-14 00:00:00.00011
32013-03-15 00:00:00.00011
32013-03-18 00:00:00.00011
32013-03-19 00:00:00.00011
32013-03-20 00:00:00.00011
*/
