原文:在论坛中出现的比较难的sql问题：29(row_number函数 组内某列的值连续出现3次标记出来)
在论坛中，遇到了不少比较难的sql问题，虽然自己都能解决，但发现过几天后，就记不起来了，也忘记解决的方法了。
所以，觉得有必要记录下来，这样以后再次碰到这类问题，也能从中获取解答的思路。
组内某列的值连续出现3次标记出来
如下，eid为人员ID，对于同一个EID，date列连续出现3次以上的，FLAG列标记为1.
尽量用1条UPDATE语句！
eid date flag
1 2013-3-1
1 2013-3-2
1 2013-3-4
1 2013-3-5
1 2013-3-7
1
1 2013-3-8
1
1 2013-3-9
1
1 2013-3-11
1 2013-3-12
1 2013-3-14
1
1 2013-3-15
1
1 2013-3-16
1
1 2013-3-18
2 2013-3-1
2 2013-3-2
2 2013-3-4
2 2013-3-6
1
2 2013-3-7
1
2 2013-3-8
1
2 2013-3-9
1
2 2013-3-11
1
2 2013-3-12
1
2 2013-3-13
1
2 2013-3-15
2 2013-3-16
2 2013-3-18
1
2 2013-3-19
1
2 2013-3-20
1
3 2013-3-1
3 2013-3-2
3 2013-3-4
1
3 2013-3-5
1
3 2013-3-6
1
3 2013-3-8
3 2013-3-9
3 2013-3-12
1
3 2013-3-13
1
3 2013-3-14
1
3 2013-3-15
1
3 2013-3-18
1
3 2013-3-19
1
3 2013-3-20
1
我的方法：
-
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
-
/*
-
eid date flag 计算出来的flag值
-
1 2013-03-01 00:00:00.000 NULL NULL
-
1 2013-03-02 00:00:00.000 NULL NULL
-
1 2013-03-04 00:00:00.000 NULL NULL
-
1 2013-03-05 00:00:00.000 NULL NULL
-
1 2013-03-07 00:00:00.000 1 1
-
1 2013-03-08 00:00:00.000 1 1
-
1 2013-03-09 00:00:00.000 1 1
-
1 2013-03-11 00:00:00.000 NULL NULL
-
1 2013-03-12 00:00:00.000 NULL NULL
-
1 2013-03-14 00:00:00.000 1 1
-
1 2013-03-15 00:00:00.000 1 1
-
1 2013-03-16 00:00:00.000 1 1
-
1 2013-03-18 00:00:00.000 NULL NULL
-
2 2013-03-01 00:00:00.000 NULL NULL
-
2 2013-03-02 00:00:00.000 NULL NULL
-
2 2013-03-04 00:00:00.000 NULL NULL
-
2 2013-03-06 00:00:00.000 1 1
-
2 2013-03-07 00:00:00.000 1 1
-
2 2013-03-08 00:00:00.000 1 1
-
2 2013-03-09 00:00:00.000 1 1
-
2 2013-03-11 00:00:00.000 1 1
-
2 2013-03-12 00:00:00.000 1 1
-
2 2013-03-13 00:00:00.000 1 1
-
2 2013-03-15 00:00:00.000 NULL NULL
-
2 2013-03-16 00:00:00.000 NULL NULL
-
2 2013-03-18 00:00:00.000 1 1
-
2 2013-03-19 00:00:00.000 1 1
-
2 2013-03-20 00:00:00.000 1 1
-
3 2013-03-01 00:00:00.000 NULL NULL
-
3 2013-03-02 00:00:00.000 NULL NULL
-
3 2013-03-04 00:00:00.000 1 1
-
3 2013-03-05 00:00:00.000 1 1
-
3 2013-03-06 00:00:00.000 1 1
-
3 2013-03-08 00:00:00.000 NULL NULL
-
3 2013-03-09 00:00:00.000 NULL NULL
-
3 2013-03-12 00:00:00.000 1 1
-
3 2013-03-13 00:00:00.000 1 1
-
3 2013-03-14 00:00:00.000 1 1
-
3 2013-03-15 00:00:00.000 1 1
-
3 2013-03-18 00:00:00.000 1 1
-
3 2013-03-19 00:00:00.000 1 1
-
3 2013-03-20 00:00:00.000 1 1
-
*/
