问题描述:
查找出下列数据中那几行符合下面条件的记录
同一客户在3天内连续进行了3次或以上操作,同一天发生的不同操作数(operno)的操作分别计数,
同一天发生同一操作(operno)数的只算一次
在下面的数据中,则我们可以发现客户13520在20091014进行了多次操作>=3及 客户13523在20090921到20090922两天间进行了3次操作,故这几行都被查询出来
而客户13520在20090807进行的3次操作则不是多次操作
operdate operno custid
1 20070731 3417 13513
2 20090708 5960 13513
3 20090806 7407 13513
4 20090807 3901 13513
5 20091204 5970 13513
6 20090320 66 13520
7 20090807 5104 13520
8 20090807 5104 13520
9 20090807 5104 13520
10 20090928 7832 13520
11 20090928 5990 13520
12 20091014 4670 13520
13 20091014 4673 13520
14 20091014 6179 13520
15 20091014 5779 13520
16 20070529 4673 13523
17 20070618 368 13523
18 20070730 2989 13523
19 20070829 3081 13523
20 20070829 3081 13523
21 20070829 3081 13523
22 20070829 3117 13523
23 20070829 3131 13523
24 20070829 3162 13523
25 20090715 4443 13523
26 20090921 8950 13523
27 20090921 9008 13523
28 20090922 5961 13523
29 20091216 5843 13523
30 20091218 4751 13523
谢谢
查找出下列数据中那几行符合下面条件的记录
同一客户在3天内连续进行了3次或以上操作,同一天发生的不同操作数(operno)的操作分别计数,
同一天发生同一操作(operno)数的只算一次
在下面的数据中,则我们可以发现客户13520在20091014进行了多次操作>=3及 客户13523在20090921到20090922两天间进行了3次操作,故这几行都被查询出来
而客户13520在20090807进行的3次操作则不是多次操作
operdate operno custid
1 20070731 3417 13513
2 20090708 5960 13513
3 20090806 7407 13513
4 20090807 3901 13513
5 20091204 5970 13513
6 20090320 66 13520
7 20090807 5104 13520
8 20090807 5104 13520
9 20090807 5104 13520
10 20090928 7832 13520
11 20090928 5990 13520
12 20091014 4670 13520
13 20091014 4673 13520
14 20091014 6179 13520
15 20091014 5779 13520
16 20070529 4673 13523
17 20070618 368 13523
18 20070730 2989 13523
19 20070829 3081 13523
20 20070829 3081 13523
21 20070829 3081 13523
22 20070829 3117 13523
23 20070829 3131 13523
24 20070829 3162 13523
25 20090715 4443 13523
26 20090921 8950 13523
27 20090921 9008 13523
28 20090922 5961 13523
29 20091216 5843 13523
30 20091218 4751 13523
谢谢
create table #test
(id int identity(1,1),operdate char(8), operno int, custid int)insert #test(operdate,operno,custid) select 20070731, 3417 ,13513
insert #test(operdate,operno,custid) select 20090708, 5960, 13513
insert #test(operdate,operno,custid) select 20090806, 7407, 13513
insert #test(operdate,operno,custid) select 20090807, 3901, 13513
insert #test(operdate,operno,custid) select 20091204, 5970, 13513
insert #test(operdate,operno,custid) select 20090320, 66, 13520
insert #test(operdate,operno,custid) select 20090807, 5104, 13520
insert #test(operdate,operno,custid) select 20090807, 5104, 13520
insert #test(operdate,operno,custid) select 20090807, 5104, 13520
insert #test(operdate,operno,custid) select 20090928, 7832, 13520
insert #test(operdate,operno,custid) select 20090928, 5990, 13520
insert #test(operdate,operno,custid) select 20091014, 4670, 13520
insert #test(operdate,operno,custid) select 20091014, 4673, 13520
insert #test(operdate,operno,custid) select 20091014, 6179, 13520
insert #test(operdate,operno,custid) select 20091014, 5779, 13520
insert #test(operdate,operno,custid) select 20070529, 4673, 13523
insert #test(operdate,operno,custid) select 20070618, 368, 13523
insert #test(operdate,operno,custid) select 20070730, 2989, 13523
insert #test(operdate,operno,custid) select 20070829, 3081, 13523
insert #test(operdate,operno,custid) select 20070829, 3081, 13523
insert #test(operdate,operno,custid) select 20070829, 3081, 13523
insert #test(operdate,operno,custid) select 20070829, 3117, 13523
insert #test(operdate,operno,custid) select 20070829, 3131, 13523
insert #test(operdate,operno,custid) select 20070829, 3162, 13523
insert #test(operdate,operno,custid) select 20090715, 4443, 13523
insert #test(operdate,operno,custid) select 20090921, 8950, 13523
insert #test(operdate,operno,custid) select 20090921, 9008, 13523
insert #test(operdate,operno,custid) select 20090922, 5961, 13523
insert #test(operdate,operno,custid) select 20091216, 5843, 13523
insert #test(operdate,operno,custid) select 20091218, 4751, 13523select distinct operdate,operno,custid from #test a
where exists(select 1 from #test where a.custid=custid and a.operdate=operdate group by custid having count(distinct operno)>=3)operdate operno custid
-------- ----------- -----------
20070829 3081 13523
20070829 3117 13523
20070829 3131 13523
20070829 3162 13523
20091014 4670 13520
20091014 4673 13520
20091014 5779 13520
20091014 6179 13520(8 行受影响)
这样
select *
,case when exists(select 1 from (select distinct operdate,operno,custid from #test ) b
where a.custid=custid and abs(datediff(day,operdate,a.operdate))<=1 group by custid having count(*) >=3)
then 1 else 0 end as stat
from (select distinct operdate,operno,custid from #test ) a
) a where stat=1operdate operno custid stat
-------- ----------- ----------- -----------
20070829 3081 13523 1
20070829 3117 13523 1
20070829 3131 13523 1
20070829 3162 13523 1
20090921 8950 13523 1
20090921 9008 13523 1
20090922 5961 13523 1
20091014 4670 13520 1
20091014 4673 13520 1
20091014 5779 13520 1
20091014 6179 13520 1(11 行受影响)估计这样可以了。。