表结构
serial_no mil
1 320
2 320
3 320
4 100
5 100
6 320
7 320
8 200
9 302
10 400
11 500
12 400
serila_no是一个自增列
判断连续性举例:查询1前面的5条记录中MIL〉300 并且是连续的行,(只能为连续大于300的)并且行记录总和是3,
像Serial_no第三行时,前面的5条记录,就不成立,因为没有连续大于300
像上面的数据,得出来的结果应该是
1 320请问这条语句怎么写
serial_no mil
1 320
2 320
3 320
4 100
5 100
6 320
7 320
8 200
9 302
10 400
11 500
12 400
serila_no是一个自增列
判断连续性举例:查询1前面的5条记录中MIL〉300 并且是连续的行,(只能为连续大于300的)并且行记录总和是3,
像Serial_no第三行时,前面的5条记录,就不成立,因为没有连续大于300
像上面的数据,得出来的结果应该是
1 320请问这条语句怎么写
serial_no mil
1 320
8 320
12 320
15 100
16 100
20 320
22 320
26 200
28 302
30 400
32 500
33 400
serila_no是一个自增列
判断连续性举例:查询1前面的5条记录中MIL〉300 并且是连续的行,(只能为连续大于300的)并且行记录总和是3,
像Serial_no第三行时,前面的5条记录,就不成立,因为没有连续大于300
像上面的数据,得出来的结果应该是
1 320请问这条语句怎么写
忘了说一下,哪个serila_No并不是连接的+1,更正表结构
serial_no mil
1 320
8 320
12 320
15 100
16 100
20 320
22 320
26 200
28 302
30 400
32 500
33 400
serila_no是一个自增列
判断连续性举例:
比如当我要查询SERIAL_NO=1时 前面的5条记录中MIL〉300 并且是连续的大于300的行,(只能为连续大于300的)并且行记录总和是3,像Serial_no第三行时,前面的5条记录,就不成立,因为没有连续大于300
像上面的数据,得出来的结果应该是
1 320
insert tb
select 1, 320 union all
select 2, 320 union all
select 3, 320 union all
select 4, 100 union all
select 5, 100 union all
select 6, 320 union all
select 7, 320 union all
select 8, 200 union all
select 9, 302 union all
select 10, 400 union all
select 11, 500 union all
select 12, 400 select * from tb a
where mil>300
and exists
(select 1 from tb b where b.serial_no=a.serial_no+1 and b.mil>300)
and exists
(select 1 from tb b where b.serial_no=a.serial_no+2 and b.mil>300)
and exists
(select 1 from tb b where b.serial_no=a.serial_no+3 and b.mil<=300)/*
serial_no mil
----------- -----------
1 320(所影响的行数为 1 行)
*/
create table tb(serial_no int,mil int)
insert tb
select 1, 320 union all
select 2, 320 union all
select 3, 320 union all
select 4, 100 union all
select 5, 100 union all
select 6, 320 union all
select 7, 320 union all
select 8, 200 union all
select 9, 302 union all
select 10, 400 union all
select 11, 500 union all
select 12, 400 select * from tb a
where
not exists
(select 1 from tb b where b.serial_no between a.serial_no and a.serial_no+2 and b.mil<=300)
and exists
(select 1 from tb b where b.serial_no=a.serial_no+3 and b.mil<=300)/*
serial_no mil
----------- -----------
1 320(所影响的行数为 1 行)
*/
(select *,id as row_number() over (order by serial_no) from tb)
select * from cte
where
not exists
(select 1 from cte b where b.id between a.id and a.id+2 and b.mil<=300)
and exists
(select 1 from cte b where b.id=a.id+3 and b.mil<=300)
if object_id('[t1]') is not null drop table [t1]
go
-- milcreate table [t1](serial_no int identity(1,1), mil int)
insert [t1](mil)
select '320' union all
select '320' union all
select '320' union all
select '100' union all
select '100' union all
select '320' union all
select '320' union all
select '200' union all
select '302' union all
select '400' union all
select '500' union all
select '400'
select distinct a.mil from t1 a
where exists(select b.* from t1 b where b.serial_no>a.serial_no and b.serial_no-a.serial_no =1 and a.mil =b.mil and b.mil>300)
-----------------------------查询结果:
---mil
---320