如何判断连续满足情况

Question

create table t
( 
  id VARCHAR(10),  
  name VARCHAR(10),    
  je int,            
  idate DATEtime   
)
 
insert into t
select '10004','小明','0','2008-01-01'
union select '10004','小明','0','2008-01-01'
union select '10004','小明','0','2008-02-01'
union select '10004','小明','0','2008-03-01'
union select '10004','小明','8','2008-04-01'
union select '10004','小明','0','2008-05-01'
union select '10004','小明','8','2008-06-01'
union select '10004','小明','0','2008-07-01'
union select '10004','小明','0','2008-08-01'
union select '10004','小明','8','2008-09-01'
union select '10005','张三','0','2008-01-01'
union select '10005','张三','0','2008-02-01'
union select '10005','张三','0','2008-03-01'
union select '10005','张三','0','2008-04-01'
union select '10005','张三','0','2008-05-01'
union select '10005','张三','0','2008-06-01'
union select '10005','张三','8','2008-07-01'
union select '10005','张三','8','2008-08-01'
union select '10005','张三','8','2008-09-01'
union select '10005','张三','8','2008-10-01'
union select '10005','张三','8','2008-11-01'--drop table t
需求查询出连续六个月没有费用的用户
结果：
'10005','张三','0','2008-01-01'
'10005','张三','0','2008-02-01'
'10005','张三','0','2008-03-01'
'10005','张三','0','2008-04-01'
'10005','张三','0','2008-05-01'
'10005','张三','0','2008-06-01'
如果查询出连续三个月没有费用的用户
结果：
'10004','小明','0','2008-01-01'
'10004','小明','0','2008-02-01'
'10004','小明','0','2008-03-01'
'10005','张三','0','2008-01-01'
'10005','张三','0','2008-02-01'
'10005','张三','0','2008-03-01'
'10005','张三','0','2008-04-01'
'10005','张三','0','2008-05-01'
'10005','张三','0','2008-06-01'

Answer 1

create table t 
( 
  id VARCHAR(10),  
  name VARCHAR(10),    
  je int,            
  idate DATEtime  
) insert into t 
select '10004','小明','0','2008-01-01' 
union select '10004','小明','0','2008-01-01' 
union select '10004','小明','0','2008-02-01' 
union select '10004','小明','0','2008-03-01' 
union select '10004','小明','8','2008-04-01' 
union select '10004','小明','0','2008-05-01' 
union select '10004','小明','8','2008-06-01' 
union select '10004','小明','0','2008-07-01' 
union select '10004','小明','0','2008-08-01' 
union select '10004','小明','8','2008-09-01' 
union select '10005','张三','0','2008-01-01' 
union select '10005','张三','0','2008-02-01' 
union select '10005','张三','0','2008-03-01' 
union select '10005','张三','0','2008-04-01' 
union select '10005','张三','0','2008-05-01' 
union select '10005','张三','0','2008-06-01' 
union select '10005','张三','8','2008-07-01' 
union select '10005','张三','8','2008-08-01' 
union select '10005','张三','8','2008-09-01' 
union select '10005','张三','8','2008-10-01' 
union select '10005','张三','8','2008-11-01' --DROP TABLE T,#T
SELECT IDD=IDENTITY(INT,1,1),* INTO #T FROM T ORDER BY ID ,IDATESELECT *,(SELECT COUNT(1) FROM #T WHERE JE=T.JE AND IDD<=T.IDD
AND IDD>(SELECT ISNULL(MAX(IDD),0) FROM #T WHERE IDD<T.IDD AND JE<>T.JE))
FROM #T TIDD         id         name       je          idate                                                              
----------- ---------- ---------- ----------- ------------------------------------------------------ ----------- 
1           10004      小明         0           2008-01-01 00:00:00.000                                1
2           10004      小明         0           2008-02-01 00:00:00.000                                2
3           10004      小明         0           2008-03-01 00:00:00.000                                3
4           10004      小明         8           2008-04-01 00:00:00.000                                1
5           10004      小明         0           2008-05-01 00:00:00.000                                1
6           10004      小明         8           2008-06-01 00:00:00.000                                1
7           10004      小明         0           2008-07-01 00:00:00.000                                1
8           10004      小明         0           2008-08-01 00:00:00.000                                2
9           10004      小明         8           2008-09-01 00:00:00.000                                1
10          10005      张三         0           2008-01-01 00:00:00.000                                1
11          10005      张三         0           2008-02-01 00:00:00.000                                2
12          10005      张三         0           2008-03-01 00:00:00.000                                3
13          10005      张三         0           2008-04-01 00:00:00.000                                4
14          10005      张三         0           2008-05-01 00:00:00.000                                5
15          10005      张三         0           2008-06-01 00:00:00.000                                6
16          10005      张三         8           2008-07-01 00:00:00.000                                1
17          10005      张三         8           2008-08-01 00:00:00.000                                2
18          10005      张三         8           2008-09-01 00:00:00.000                                3
19          10005      张三         8           2008-10-01 00:00:00.000                                4
20          10005      张三         8           2008-11-01 00:00:00.000                                5（所影响的行数为 20 行）

Answer 2

if object_id('[tb]') is not null drop table [tb] 
go 
create table [tb]([value1] int,[value2] int) 
insert [tb] 
select 1,12 union all 
select 1,13 union all 
select 1,23 union all 
select 0,14 union all 
select 0,15 union all 
select 1,16 union all 
select 0,23 union all 
select 0,22 union all 
select 1,21 union all 
select 1,12 select id=identity(int,1,1),* into # from tb
select 
value1,value2,col3=(select count(1) from # where [value1]=a.[value1] and id<=a.id 
and id>=(select isnull(max(id),0) from # where id<a.id and value1!=a.value1)) 
from # a--结果：
value1      value2      col3
----------- ----------- -----------
1           12          1
1           13          2
1           23          3
0           14          1
0           15          2
1           16          1
0           23          1
0           22          2
1           21          1
1           12          2

Answer 3

类似2楼的代码  当id>=3时候就满足条件了  需要写的话说一下

Answer 4

declare @days int
set @days=6;with cte as
(
select *,row_number() over (order by id, idate) as seq from t
)
,cte2 as
(
select *,0 as grp from cte where seq=1
union all
select cte.*, case when cte2.id=cte.id and cte.je=cte2.je and cte.je=0 then grp else grp+1 end as grp  
from cte, cte2 
where cte.seq=cte2.seq+1
)
select cte2.id,cte2.name,cte2.je,cte2.idate
from cte2 join (select id,grp from cte2 group by id,grp having count(*)>=@days) t
  on cte2.id=t.id and cte2.grp=t.grp

Answer 5

二楼的运行结果不对啊
需求是分六个月和三个月，
六个月就是说连续6条je都是0的搜索出来
三个月就是连续3条je都是0的搜索出来

Answer 6

create table t 
( 
  id VARCHAR(10),  
  name VARCHAR(10),    
  je int,            
  idate DATEtime  
) insert into t 
select '10004','小明','0','2008-01-01' 
union select '10004','小明','0','2008-01-01' 
union select '10004','小明','0','2008-02-01' 
union select '10004','小明','0','2008-03-01' 
union select '10004','小明','8','2008-04-01' 
union select '10004','小明','0','2008-05-01' 
union select '10004','小明','8','2008-06-01' 
union select '10004','小明','0','2008-07-01' 
union select '10004','小明','0','2008-08-01' 
union select '10004','小明','8','2008-09-01' 
union select '10005','张三','0','2008-01-01' 
union select '10005','张三','0','2008-02-01' 
union select '10005','张三','0','2008-03-01' 
union select '10005','张三','0','2008-04-01' 
union select '10005','张三','0','2008-05-01' 
union select '10005','张三','0','2008-06-01' 
union select '10005','张三','8','2008-07-01' 
union select '10005','张三','8','2008-08-01' 
union select '10005','张三','8','2008-09-01' 
union select '10005','张三','8','2008-10-01' 
union select '10005','张三','8','2008-11-01' --DROP TABLE T,#T
SELECT IDD=IDENTITY(INT,1,1),* INTO #T FROM T ORDER BY ID ,IDATEselect name from 
(
SELECT *,(SELECT COUNT(1) FROM #T WHERE JE=T.JE AND IDD<=T.IDD
AND IDD>(SELECT ISNULL(MAX(IDD),0) FROM #T WHERE IDD<T.IDD AND JE<>T.JE))d
FROM #T T
)as t group by name having max(d)>=3name       
---------- 
小明
张三（所影响的行数为 2 行）