求sql 想在一个表中查询某个字段连续N条记录是连续+1的第一条记录

表记录 参数num=3(3 条相连(no连续+1)的记录)
id  loclevel          no
1      1              1
2      1              2
3      1              4
4      1              5
5      1              6
6      2              1
7      2              2
8      2              3
9      2              4

我想得到的结果是
id  loclevel         no
3      1              4
6      2              1
7      2              2

也就是说 loclevel是1的情况下 id3到id5 NO字段是3次连续+1的(4,5,6) 所以把id3提出来 
         loclevel是2的情况下 id6到id8 NO字段是3次连续+1的(1,2,3)  id7到id9 NO字段是3次连续+1的(2,3,4)
         所以把id6,id7提出来
请问能不能通过sql实现 谢谢了 

27 个解决方案

#1

declare @t table(id int,loclevel int,no int)
insert into @t select 1,1,1 
insert into @t select 2,1,2 
insert into @t select 3,1,4 
insert into @t select 4,1,5 
insert into @t select 5,1,6 
insert into @t select 6,2,1 
insert into @t select 7,2,2 
insert into @t select 8,2,3 
insert into @t select 9,2,4 

select
    a.*
from
    @t a,@t b,@t c
where
    a.id=b.id-1 and a.no=b.no-1 and a.loclevel=b.loclevel
    and 
    a.id=c.id-2 and a.no=c.no-2 and a.loclevel=c.loclevel

/*
id          loclevel    no          
----------- ----------- ----------- 
3           1           4
6           2           1
7           2           2
*/

#2

或者：

declare @t table(id int,loclevel int,no int)
insert into @t select 1,1,1 
insert into @t select 2,1,2 
insert into @t select 3,1,4 
insert into @t select 4,1,5 
insert into @t select 5,1,6 
insert into @t select 6,2,1 
insert into @t select 7,2,2 
insert into @t select 8,2,3 
insert into @t select 9,2,4 

select
    a.*
from
    @t a
where
    exists(select 1 from @t where a.id=id-1 and a.no=no-1 and a.loclevel=loclevel)
    and 
    exists(select 1 from @t where a.id=id-2 and a.no=no-2 and a.loclevel=loclevel)

/*
id          loclevel    no          
----------- ----------- ----------- 
3           1           4
6           2           1
7           2           2
*/

#3

没搞清楚规律，顶

#4

--> 测试数据: @s
declare @s table (id int,loclevel int,no int)
insert into @s
select 1,1,1 union all
select 2,1,2 union all
select 3,1,4 union all
select 4,1,5 union all
select 5,1,6 union all
select 6,2,1 union all
select 7,2,2 union all
select 8,2,3 union all
select 9,2,4

select * from @s a 
where exists(select 1 from @s where loclevel=a.loclevel and no=a.no+1)
and exists(select 1 from @s where loclevel=a.loclevel and no=a.no+2)

--结果：
id          loclevel    no
----------- ----------- -----------
3           1           4
6           2           1
7           2           2

#5

发了2次

#6

declare @t table(id int,loclevel int,no int)
insert into @t select 1,1,1 
insert into @t select 2,1,2 
insert into @t select 3,1,4 
insert into @t select 4,1,5 
insert into @t select 5,1,6 
insert into @t select 6,2,1 
insert into @t select 7,2,2 
insert into @t select 8,2,3 
insert into @t select 9,2,4 

declare @i int
set @i=3  --当N值变化时，只需替换此处的@i值

select
    a.*
from
    @t a,
    (select t.* from @t t where not exists(select 1 from @t where id=t.id+1 and loclevel=t.loclevel and no=t.no+1)) b
where
    a.id<=b.id and a.loclevel=b.loclevel
group by
    a.id,a.loclevel,a.no
having 
    min(b.no)-a.no>=@i-1


/*
id          loclevel    no          
----------- ----------- ----------- 
3           1           4
6           2           1
7           2           2
*/

#7

请问libin_ftsafe 如果要求连续的数是不确定的呢 这次是3 下次可能是4

#8

--> 测试数据: @s
declare @s table (id int,loclevel int,no int)
insert into @s
select 1,1,1 union all
select 2,1,2 union all
select 3,1,4 union all
select 4,1,5 union all
select 5,1,6 union all
select 6,2,1 union all
select 7,2,2 union all
select 8,2,3 union all
select 9,2,4

select * from @s a 
where exists(select 1 from @s where loclevel=a.loclevel and no=a.no+1)
and exists(select 1 from @s where loclevel=a.loclevel and no=a.no+2)

--结果：
id          loclevel    no
----------- ----------- -----------
3           1           4
6           2           1
7           2           2

#9

haha 转下酒好了

#10

引用 7 楼 yxdwtt4268 的回复:

请问libin_ftsafe 如果要求连续的数是不确定的呢 这次是3 下次可能是4

见6楼的回复。

#11

引用 6 楼 libin_ftsafe 的回复:

SQL codedeclare@ttable(idint,loclevelint,noint)insertinto@tselect1,1,1insertinto@tselect2,1,2insertinto@tselect3,1,4insertinto@tselect4,1,5insertinto@tselect5,1,6insertinto@tselect6,2,1insertinto@tsel¡­

学习钻钻

#12

引用 6 楼 libin_ftsafe 的回复:

SQL codedeclare@ttable(idint,loclevelint,noint)insertinto@tselect1,1,1insertinto@tselect2,1,2insertinto@tselect3,1,4insertinto@tselect4,1,5insertinto@tselect5,1,6insertinto@tselect6,2,1insertinto@tsel¡­

太厉害了

#13

--> 测试时间:2009-07-09 16:18:21
--> 我的淘宝: http://shop36766744.taobao.com/

if object_id('[tab]') is not null drop table [tab]
create table [tab]([id] int,[loclevel] int,[no] int)
insert [tab]
select 1,1,1 union all
select 2,1,2 union all
select 3,1,4 union all
select 4,1,5 union all
select 5,1,6 union all
select 6,2,1 union all
select 7,2,2 union all
select 8,2,3 union all
select 9,2,4

select * from tab a where [no]=(select [no] from tab where a.loclevel=loclevel and ID=a.ID+2)-2

/*
id          loclevel    no          
----------- ----------- ----------- 
3           1           4
6           2           1
7           2           2

（所影响的行数为 3 行）

*/

#14

-- =========================================
-- -----------t_mac 小编-------------
   ---希望有天成为大虾---- 
-- =========================================

IF OBJECT_ID('tb') IS NOT NULL
  DROP TABLE tb
GO
CREATE TABLE tb(id int,loclevel int ,no int)
go
insert into tb select 1,1,1 
insert into tb select 2,1,2 
insert into tb select 3,1,4 
insert into tb select 4,1,5 
insert into tb select 5,1,6 
insert into tb select 6,2,1 
insert into tb select 7,2,2 
insert into tb select 8,2,3 
insert into tb select 9,2,4 
go
declare @s int
set @s=3--可以任意改
select * from tb a 
where [no]=(select [no] from tb where a.loclevel=loclevel and ID=a.ID+@s-1)-@s+1




/*------------
3	1	4
6	2	1
7	2	2
-------*/

#15

select 1 from @t where a.id=id-1 and a.no=no-1 and a.loclevel=loclevel

select 1 from @t where a.id=id-2 and a.no=no-2 and a.loclevel=loclevel

还能大概解释下这2个语句啊，我想了，想不通

#16

-------------------------------------------
--  Author : liangCK 小梁
--  Comment: 小梁 爱 兰儿
--  Date   : 2009-07-09 16:18:37
-------------------------------------------
 
--> 生成测试数据: @T
DECLARE @T TABLE (id INT,loclevel INT,no INT)
INSERT INTO @T
SELECT 1,1,1 UNION ALL
SELECT 2,1,2 UNION ALL
SELECT 3,1,4 UNION ALL
SELECT 4,1,5 UNION ALL
SELECT 5,1,6 UNION ALL
SELECT 6,2,1 UNION ALL
SELECT 7,2,2 UNION ALL
SELECT 8,2,3 UNION ALL
SELECT 9,2,4

--SQL查询如下:

DECLARE @n INT;
SET @n = 3;

;WITH Liang AS
(
    SELECT value_no=ROW_NUMBER() OVER(PARTITION BY loclevel ORDER BY id)-no,
           rowid=ROW_NUMBER() OVER(PARTITION BY loclevel ORDER BY id),*
    FROM @T
)
SELECT id,loclevel,no FROM Liang AS A
WHERE EXISTS(
         SELECT 1
         FROM Liang
         WHERE loclevel = A.loclevel
             AND value_no = A.value_no
             AND rowid BETWEEN A.rowid AND A.rowid + @n -1
         GROUP BY loclevel
         HAVING COUNT(*) >= @n
      )

/*
id          loclevel    no
----------- ----------- -----------
3           1           4
6           2           1
7           2           2

(3 row(s) affected)

*/

#17

---------------------------------
--  Author: htl258(Tony)
--  Date  : 2009-07-09 16:19:14
---------------------------------
--> 生成测试数据表:tb

If not object_id('[tb]') is null
    Drop table [tb]
Go
Create table [tb]([id] int,[loclevel] int,[no] int)
Insert tb
Select 1,1,1 union all
Select 2,1,2 union all
Select 3,1,4 union all
Select 4,1,5 union all
Select 5,1,6 union all
Select 6,2,1 union all
Select 7,2,2 union all
Select 8,2,3 union all
Select 9,2,4 union all
Select 10,2,5 union all --再加两条记录
Select 11,2,6
Go
--Select * from tb

-->SQL查询如下:
;with t as
(
    select rn=row_number() over(order by loclevel,id)-no,* 
    from tb
)
select id,loclevel,no
from t a 
where exists(
    select 1 
    from t 
    where rn=a.rn 
    group by rn 
    having count(1)>=3)
  and no not in(
    select top 2 no
    from t 
    where loclevel=a.loclevel
    order by no desc)

/*
id          loclevel    no
----------- ----------- -----------
3           1           4
6           2           1
7           2           2
8           2           3
9           2           4

(5 行受影响)
*/

#18

create table tt(id int,loclevel int,no int)
insert into tt select 1,1,1 
insert into tt select 2,1,2 
insert into tt select 3,1,4 
insert into tt select 4,1,5 
insert into tt select 5,1,6 
insert into tt select 6,2,1 
insert into tt select 7,2,2 
insert into tt select 8,2,3 
insert into tt select 9,2,4

select t1.*
from tt t1 join tt t2 
on t1.no = t2.no-1 and t1.loclevel = t2.loclevel
join tt t3 on t1.no = t3.no-2 and t1.loclevel = t3.loclevel

/*
id	loclevel	no
3	1	4
6	2	1
7	2	2
*/

#19

引用 6 楼 libin_ftsafe 的回复:

SQL codedeclare@ttable(idint,loclevelint,noint)insertinto@tselect1,1,1insertinto@tselect2,1,2insertinto@tselect3,1,4insertinto@tselect4,1,5insertinto@tselect5,1,6insertinto@tselect6,2,1insertinto@tsel¡­

有个bug 呵呵 
declare @t table(id int,loclevel int,no int)
insert into @t select 1,1,1 
insert into @t select 2,1,2 
insert into @t select 3,1,4 
insert into @t select 4,1,2 
insert into @t select 5,1,6 
insert into @t select 6,2,5 
insert into @t select 7,2,6 
insert into @t select 8,2,7
insert into @t select 9,2,2
这样的数据 7 ,2 ,6查不出来

#20

引用 19 楼 yxdwtt4268 的回复:

引用 6 楼 libin_ftsafe 的回复:
 SQL codedeclare@ttable(idint,loclevelint,noint)insertinto@tselect1,1,1insertinto@tselect2,1,2insertinto@tselect3,1,4insertinto@tselect4,1,5insertinto@tselect5,1,6insertinto@tselect6,2,1insertinto@tsel¡­

 有个bug 呵呵
 declare @t table(id int,loclevel int,no int)
 insert into @t select 1,1,1
 insert into @t select 2,1,2
 insert into @t select 3,1,4
 insert into @t select 4,1,2
 insert into @t select 5,1,6
 insert into @t select 6,2,5
 insert into @t select 7,2,6
 insert into @t select 8,2,7
 insert into @t select 9,2,2
 这样的数据 7 ,2 ,6查不出来

四楼的可以

#21

引用 19 楼 yxdwtt4268 的回复:

这样的数据 7 ,2 ,6查不出来

这条数据的后继第一条数据是满足+1条件的，但是第二条no=2，不满足在上一条基础上+1的条件。
因此不满足输出条件。

#22

引用 20 楼 you_tube 的回复:

这样的数据 7 ,2 ,6查不出来

四楼的可以

楼主的标题是：想在一个表中查询某个字段连续N条记录是连续+1的第一条记录......

insert into @t select 7,2,6    --当此条记录作为第一条
insert into @t select 8,2,7    --连续+1
insert into @t select 9,2,2    --非连续+1

明显不满足楼主的要求。难道是我理解错了？:)

#23

引用 21 楼 libin_ftsafe 的回复:

引用 19 楼 yxdwtt4268 的回复:
这样的数据 7 ,2 ,6查不出来


这条数据的后继第一条数据是满足+1条件的，但是第二条no=2，不满足在上一条基础上+1的条件。
因此不满足输出条件。

打错了 是 6,2,5  那条查不出来

#24

引用 23 楼 yxdwtt4268 的回复:

引用 21 楼 libin_ftsafe 的回复:
引用 19 楼 yxdwtt4268 的回复:
这样的数据 7 ,2 ,6查不出来


这条数据的后继第一条数据是满足+1条件的，但是第二条no=2，不满足在上一条基础上+1的条件。
因此不满足输出条件。

打错了 是 6,2,5  那条查不出来

试试14楼的。。

#25

引用 22 楼 libin_ftsafe 的回复:

引用 20 楼 you_tube 的回复:
 这样的数据 7 ,2 ,6查不出来

 四楼的可以


 楼主的标题是：想在一个表中查询某个字段连续N条记录是连续+1的第一条记录......

 insert into @t select 7,2,6    --当此条记录作为第一条
 insert into @t select 8,2,7    --连续+1
 insert into @t select 9,2,2    --非连续+1

 明显不满足楼主的要求。难道是我理解错了？:)

6,2,5 是这条出来吧
这条的确不能出来 ：)

#26

恩，不能拿no作为判断依据，用id列吧，修改如下：

declare @t table(id int,loclevel int,no int)
insert into @t select 1,1,1 
insert into @t select 2,1,2 
insert into @t select 3,1,4 
insert into @t select 4,1,2 
insert into @t select 5,1,6 
insert into @t select 6,2,5 
insert into @t select 7,2,6 
insert into @t select 8,2,7 
insert into @t select 9,2,2 

declare @i int
set @i=3  --当N值变化时，只需替换此处的@i值

select
    a.*
from
    @t a,
    (select t.* from @t t where not exists(select 1 from @t where id=t.id+1 and loclevel=t.loclevel and no=t.no+1)) b
where
    a.id<=b.id and a.loclevel=b.loclevel
group by
    a.id,a.loclevel,a.no
having 
    min(b.id)-a.id>=@i-1


/*
id          loclevel    no          
----------- ----------- ----------- 
6           2           5
*/

#27

引用 23 楼 yxdwtt4268 的回复:

引用 21 楼 libin_ftsafe 的回复:
引用 19 楼 yxdwtt4268 的回复:
这样的数据 7 ,2 ,6查不出来


这条数据的后继第一条数据是满足+1条件的，但是第二条no=2，不满足在上一条基础上+1的条件。
因此不满足输出条件。

打错了 是 6,2,5  那条查不出来

14楼
采用参数值